Amazon RDS Oracle DB 인스턴스에서 대해 Statspack에서 캡처한 실행 계획을 보려면 어떻게 해야 합니까?

​해결 방법

1.    SQL 실행 계획을 캡처하려면 스냅 레벨이 6 이상인 Statspack 스냅샷**(i_snap_level => 6)**을 생성합니다. 자세한 내용은 Oracle을 실행하는 Amazon RDS DB 인스턴스의 성능 통계를 확인하려면 어떻게 해야 합니까?를 참조하십시오.

2.    Statspack 보고서를 사용하여 쿼리에 대한 Begin Snap ID, End Snap ID 및 이전 해시 값을 확인합니다. 다음 예제에서 Begin Snap ID는 22이고 End Snap ID는 23입니다.

STATSPACK report for
Database    DB Id    Instance     Inst Num  Startup Time   Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          1234567890 silent              1 03-Jan-20 00:45 12.2.0.1.0  NO
Host Name             Platform                CPUs Cores Sockets   Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
     ip-172-31-22-176 Linux x86 64-bit           2     2       1          3.7
Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- ------------------
Begin Snap:         22 03-Jan-20 01:30:36       41        .8
  End Snap:         23 03-Jan-20 01:39:12       40        .8
   Elapsed:       8.60 (mins) Av Act Sess:       0.0
   DB time:       0.11 (mins)      DB CPU:       0.11 (mins)
...

3.    이전 해시 값을 사용하여 실행 계획을 보려면 쿼리를 찾습니다. Statspack 보고서는 다양한 "SQL order by" 섹션을 포함합니다. 예를 들어, "SQL ordered by CPU DB/Inst" 섹션은 CPU 집약적 쿼리를 나열합니다. 다음 예제에서는 이전 해시 값 73250552를 사용합니다. 이는 해당 시간에 최상위 CPU 집약적 쿼리의 해시 값입니다.

...
SQL ordered by CPU  DB/Inst: SILENT/silent  Snaps: 22-23
-> Total DB CPU (s):               7
-> Captured SQL accounts for   80.3% of Total DB CPU
-> SQL reported below exceeded  1.0% of Total DB CPU
    CPU                  CPU per             Elapsd                     Old
  Time (s)   Executions  Exec (s)  %Total   Time (s)    Buffer Gets  Hash Value
---------- ------------ ---------- ------ ---------- --------------- ----------
      4.03            3       1.34   60.8       4.08         528,477   73250552
Module: SQL*Plus
SELECT COUNT(*) FROM HOGE_TBL H1 INNER JOIN HOGE_TBL H2 USING(OB
JECT_NAME)
      0.75            1       0.75   11.3       0.77          18,994 2912400228
Module: sqlplus@ip-172-31-22-176 (TNS V1-V3)
BEGIN statspack.snap; END;
      0.14          107       0.00    2.1       0.15             732 3879834072
select TIME_WAITED_MICRO from V$SYSTEM_EVENT  where event = 'Sha
red IO Pool Memory'
...

3.    SQL*Plus와 같은 Oracle 클라이언트를 사용하여 DB 인스턴스에 연결합니다.

4.    다음과 유사한 쿼리를 호출하여 실행 계획을 검색합니다.

SELECT lpad(' ', 1 * ( depth - 1 ))
     || operation AS operation,
       object_name,
       cardinality,
       bytes,
       cost
FROM   stats$sql_plan
WHERE  plan_hash_value IN(SELECT plan_hash_value
                          FROM   stats$sql_plan_usage
                          WHERE  old_hash_value = OLD_HASH_VALUE
                                 AND snap_id BETWEEN BEGIN_SNAP_ID AND END_SNAP_ID
                                 AND plan_hash_value > 0)
ORDER  BY plan_hash_value, id;

참고: OLD_HASH_VALUE, BEGIN_SNAP_ID 및 END_SNAP_ID를 사용자의 값으로 바꿉니다.

다음 예제는 SQL*Plus에서 검색한 쿼리의 실행 계획입니다.

SQL> col operation format a20
col object_name format a20
SQL>SELECT lpad(' ', 1 * ( depth - 1 ))
     || operation AS operation,
       object_name,
       cardinality,
       bytes,
       cost
FROM   stats$sql_plan
WHERE  plan_hash_value IN(SELECT plan_hash_value
                          FROM   stats$sql_plan_usage
                          WHERE  old_hash_value = 73250552
                                 AND snap_id BETWEEN 22 AND 23
                                 AND plan_hash_value > 0)
OPERATION            OBJECT_NAME          CARDINALITY      BYTES       COST
-------------------- -------------------- ----------- ---------- ----------
SELECT STATEMENT                                                       1119
SORT                                                1         70
 HASH JOIN                                      87756    6142920       1119
  TABLE ACCESS       HOGE_TBL                   72992    2554720        397
  TABLE ACCESS       HOGE_TBL                   72992    2554720        397

---

관련 정보

Oracle을 실행하는 Amazon RDS DB 인스턴스의 성능 통계를 확인하려면 어떻게 해야 합니까?

Oracle Statspack

Statspack 사용에 대한 Oracle 설명서