Oracleで実行計画を取得する（AUTOTRACE）
Oracleで実行計画を取得する方法は色々ありますが、今回はAUTOTRACEについて書いてみます。
普段はDBMS_XPLAN.DISPLAY_CURSORを使用していますが、ヒント句で試行錯誤するときはAUTOTRACEを使っています。
DBMS_XPLAN.DISPLAY_CURSORについては、以下の記事を参考に。
AUTOTRACEはSQL*Plusだけで実行できるので容易に実行計画を取得できます。
ただし、実際にSQLを実行して実行計画を取得するため、SQLによっては時間がかかってしまいます。また、取得される実行計画も
Estimate（見積）の値であり、実際の実行計画と異なる可能性があります。
デメリットはありますが、容易に実行計画を取得できることから試行錯誤しながらチューニングする際には利用しやすいかと思います。
EXPLAIN PLANと比較すると、ソート回数・読み込みブロックサイズなどの実行統計が出力されることに違いがあります。
なお、AUTOTRACEは内部的にはExplain Plainを使用しているらしいです。
AUTOTRACEの実行方法
AUTOTRACEはSQL*Plusで「SET AUTOTRACE ON」を実行するだけです。
オプションを含めると以下の種類がありますが、「SET AUTOTRACE ON」とたまに「SET AUTOTRACE TRACEONLY」だけ覚えていればよいと思います。
| 自動トレースの設定 | 実行統計 | 結果 |
|---|---|---|
| SET AUTOTRACE OFF | ― | AUTOTRACEレポートは生成されない。デフォルトです。 |
| SET AUTOTRACE ON EXPLAIN | × | 実行計画だけが表示されます。 |
| SET AUTOTRACE ON STATISTICS | 〇 | 実行統計だけが表示されます。 |
| SET AUTOTRACE ON | 〇 | 実行計画と実行統計が表示されます。 |
| SET AUTOTRACE TRACEONLY | 〇 | 「SET AUTOTRACE ON」と同様に実行計画と実行統計が表示されます。 |
準備
実行統計の表示には「PLUSTACE」ロールが必要です。
以下のように「PLUSTACE」ロール作成するスクリプトを作成し、ユーザにロールを付与します。
ここではOracleサンプルスキーマで作成されたhrユーザにロールを付与しています。
connect / as sysdba
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> grant plustrace to hr;
実行例
set linesize 1000
set pagesize 0
-- AUTOTRACEを実行する
set autotrace on
-- 対象のSQLを実行する。Oracleのサンプルスキーマを利用しています。
select
d.*
, e.FIRST_NAME
, e.LAST_NAME
from
hr.DEPARTMENTS d
inner join hr.EMPLOYEES e
on d.MANAGER_ID = e.EMPLOYEE_ID
order by d.DEPARTMENT_ID;
-- SELECT文なので実行結果が表示されます。
10 Administration 200 1700 Jennifer Whalen
20 Marketing 201 1800 Michael Hartstein
30 Purchasing 114 1700 Den Raphaely
40 Human Resources 203 2400 Susan Mavris
50 Shipping 121 1500 Adam Fripp
60 IT 103 1400 Alexander Hunold
70 Public Relations 204 2700 Hermann Baer
80 Sales 145 2500 John Russell
90 Executive 100 1700 Steven King
100 Finance 108 1700 Nancy Greenberg
110 Accounting 205 1700 Shelley Higgins
11 rows selected.
-- 実行計画と実行統計が表示されます。
Execution Plan
----------------------------------------------------------
Plan hash value: 3992612024
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 440 | 6 (17)| 00:00:01 |
| 1 | SORT ORDER BY | | 11 | 440 | 6 (17)| 00:00:01 |
|* 2 | HASH JOIN | | 11 | 440 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | DEPARTMENTS | 11 | 231 | 3 (0)| 00:00:01 |
| 4 | VIEW | index$_join$_002 | 107 | 2033 | 2 (0)| 00:00:01 |
|* 5 | HASH JOIN | | | | | |
| 6 | INDEX FAST FULL SCAN| EMP_NAME_IX | 107 | 2033 | 1 (0)| 00:00:01 |
| 7 | INDEX FAST FULL SCAN| EMP_EMP_ID_PK | 107 | 2033 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("D"."MANAGER_ID"="E"."EMPLOYEE_ID")
3 - filter("D"."MANAGER_ID" IS NOT NULL)
5 - access(ROWID=ROWID)
Statistics
----------------------------------------------------------
4 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
1430 bytes sent via SQL*Net to client
510 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
11 rows processed
上の実行方法では、SELECTの実行結果も表示されました。実行結果が不要な場合は、「set autotrace traceonly」を実行します。
実行結果が表示されないだけで、SELECT文自体は実行されます。