Oracleで実行計画を取得する（AUTOTRACE）

Oracleで実行計画を取得する方法は色々ありますが、今回はAUTOTRACEについて書いてみます。
普段はDBMS_XPLAN.DISPLAY_CURSORを使用していますが、ヒント句で試行錯誤するときはAUTOTRACEを使っています。

DBMS_XPLAN.DISPLAY_CURSORについては、以下の記事を参考に。

Oracleで実行計画を取得する（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文自体は実行されます。

参考