Go to Qiita Advent Calendar 2024 Top
LoginSignup
4
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

@nishina247

SQLの実行計画をEXPLAIN PLANとAUTOTRACE(SQL*Plus)で確認する際SQLは実行されるか

Last updated at Posted at 2018-03-30

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

4
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
4
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?