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、および古いハッシュ値を確認します。次の例では、 開始スナップ ID は 22 で、終了スナップ 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 の並べ替え」セクションが含まれます。たとえば、「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_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