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 が実行されない。