Amazon RDS Oracle DB インスタンスの Statspack が取得した実行プランを表示する方法を教えてください。

最終更新日: 2020 年 3 月 13 日

Statspack を使用して Amazon Relational Database Service (Amazon RDS) Oracle DB インスタンスのパフォーマンス統計を取得しました。しかし、Statspack レポートに実行プランに関する情報がありません。Statspack が取得したクエリの実行プランを表示するにはどうすればよいですか?

解決方法

1.    スナップレベルが 6 以上 (i_snap_level=>6) の Statspack スナップショットを取得し、SQL 実行プランを取得します。詳細については、「How do I check the performance statistics on an Amazon RDS DB instance that is running Oracle?」をご参照ください。

2.    Statspack レポートを使用して、クエリの開始スナップ ID、終了スナップ ID、および古いハッシュ値を確認します。次の例では、 開始スナップ ID22 で、終了スナップ ID23 です。

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 の並べ替え」セクションが含まれます。たとえば、「CPU DB/Inst で SQL を並べ替え」セクションには、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_VALUEBEGIN_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