Oracle でこれから実行する SQL の実行計画を確認する

Oracle では以下の 2 種類を利用して SQL の実行計画を確認することができる。

EXPLAIN PLAN
AUTOTRACE（SQL*Plus）
- ON／TRACEONLY／TRACEONLY EXPLAIN／TRACEONLY STATISTICS

これらの確認方法で、実際に SQL の実行が行われているかを確認したところ、EXPLAIN PLAN は実行されず、AUTOTRACE 機能では一部を除き実行されていた。

EXPLAIN PLAN

SQL のハード・パースのみが行われる。実行はされない。

EXPLAIN PLAN FOR
<SQL Statement>;
select * from table(dbms_xplan.display);

V$SQL であらかじめ SQL_ID を特定しておいた SQL の実行状況を確認しながら EXPLAIN PLAN してみる。

SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           32          4      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.01
SQL> 
SQL> explain plan for 
  2  select * from t01;

解析されました。

経過: 00:00:00.01
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  1100 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T01  |   100 |  1100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

8行が選択されました。

経過: 00:00:00.02
SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           32          4      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.00

EXECUTIONS がカウントアップされていないことから、SQL の実行が行われていないことがわかる。
SELECT だけでなく DML 文も実行せずに実行計画を参照することができる。

AUTOTRACE（SQL*Plus）

SQL*Plus の AUTOTRACE 機能。
オプションによって SQL が実行されたりされなかったりする。

SET AUTOT[RACE] { ON | OFF | TRACE[ONLY] } [ EXP[LAIN] ] [ STAT[ISTICS] ]

AUTOTRACE ON


SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           32          4      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.01
SQL> set autot on
SQL> select * from t01;

        C1 C2
---------- --------
         1 18-03-28
         2 17-10-28
         3 17-10-28
           :
           :
       100 17-10-28

100行が選択されました。

経過: 00:00:00.00

実行計画
----------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  1100 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T01  |   100 |  1100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
       3369  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL> set autot off
SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           40          5      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.00

EXECUTIONS がカウントアップされており、SQL が実行されていることがわかる。

AUTOTRACE TRACEONLY


SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           40          5      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.00
SQL> set autot trace
SQL> select * from t01;

100行が選択されました。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  1100 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T01  |   100 |  1100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
       3369  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL> set autot off
SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           48          6      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.00

TRACEONLY でも EXECUTIONS がカウントアップされており、SQL が実行されている。

AUTOTRACE TRACEONLY EXPLAIN


SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           48          6      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.00
SQL> set autot trace exp
SQL> select * from t01;
経過: 00:00:00.00

実行計画
----------------------------------------------------------
Plan hash value: 3295674804

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  1100 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T01  |   100 |  1100 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

SQL> set autot off
SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           48          6      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.00

EXECUTIONS がカウントアップされていない。
TRACEONLY EXPLAIN だと実行されない模様。

AUTOTRACE TRACEONLY STATISTICS

SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           48          6      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.00
SQL> set autot trace stat
SQL> select * from t01;

100行が選択されました。

経過: 00:00:00.00

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         13  consistent gets
          0  physical reads
          0  redo size
       3369  bytes sent via SQL*Net to client
        589  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        100  rows processed

SQL> set autot off
SQL> select SQL_ID,FETCHES,EXECUTIONS,PLAN_HASH_VALUE from v$sql where sql_id = '5ts7a9dr622qv';

SQL_ID             FETCHES EXECUTIONS PLAN_HASH_VALUE
--------------- ---------- ---------- ---------------
5ts7a9dr622qv           56          7      3295674804  <<<<<<<<<<<<<<<<

経過: 00:00:00.00

EXECUTIONS がカウントアップされており、SQL が実行されている。

まとめ

EXPLAIN PLAN → SQL の実行は行われない
AUTOTRACE（SQL*Plus）
- ON → SQL の実行は行われる
- TRACEONLY → SQL の実行は行われる
- TRACEONLY EXPLAIN → SQL の実行は行われない
- TRACEONLY STATISTICS → SQL の実行は行われる

AUTOTRACE の場合、TRACEONLY でも実行統計を表示するために実行されていると思われる。
そのため、実行統計を表示しない TRACEONLY EXPLAIN のみ SQL が実行されない。