Help us understand the problem. What is going on with this article?

Oracleで実行計画を取得する(DBMS_XPLAN.DISPLAY_CURSOR)

More than 1 year has passed since last update.

はじめに

Oracleで実行計画を取得する方法は、以下のように多く存在します。(他にもありますが)

  • DBMS_XPLAN.DISPLAY_CURSOR
  • SQLトレース
  • EXPLAIN PLAN
  • AUTOTRACE(SQL*Plus)

が、DBMS_XPLAN.DISPLAY_CURSORが使えれば十分なケースが多いと思います。
DBMS_XPLAN.DISPLAY_CURSORは、v$sql_planから実行計画を取得することができるファンクションです。ただし、共有プールから実行計画が削除されている場合は取得することができません。

以降、Oracle 12c R2にてDBMS_XPLAN.DISPLAY_CURSORを使用してみます。

DBMS_XPLAN.DISPLAY_CURSORの実行

直前に実行したSQLの実行計画を取得

直前に実行したSQLの実行計画を取得する方法です。

SQL> set serveroutput off
 → 正しく取得できないので、「set serveroutput off」は必ず実行。
SQL> select emp.first_name, emp.last_name, j.job_title from employees emp, jobs j where emp.job_id = j.job_id and emp.salary = 8300.00;
 → 実行計画を取得するSQLを実行。
SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR());
 → 実行計画取得。DBMS_XPLAN.DISPLAYを引数なしで呼ぶ。引数なしだと直前に実行したSQLの実行計画を取得できる。

取得した実行計画は以下のとおり。

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1536616604

--------------------------------------------------------------------------------
----------

| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)|
Time     |

--------------------------------------------------------------------------------
----------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    55 |     4   (0)|
00:00:01 |

|   1 |  NESTED LOOPS                |           |     1 |    55 |     4   (0)|
00:00:01 |

|   2 |   NESTED LOOPS               |           |     1 |    55 |     4   (0)|
00:00:01 |

|*  3 |    TABLE ACCESS FULL         | EMPLOYEES |     1 |    28 |     3   (0)|
00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|*  4 |    INDEX UNIQUE SCAN         | JOB_ID_PK |     1 |       |     0   (0)|
00:00:01 |

|   5 |   TABLE ACCESS BY INDEX ROWID| JOBS      |     1 |    27 |     1   (0)|
00:00:01 |

--------------------------------------------------------------------------------
----------



PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("EMP"."SALARY"=8300.00)
   4 - access("EMP"."JOB_ID"="J"."JOB_ID")

Note
-----
   - this is an adaptive plan

22行が選択されました。

SQL IDを指定して実行計画を取得

SQL IDを指定して実行計画を取得します。通常はこちらのやり方を利用することが多いです。

select sql_id, sql_text from v$sql where sql_text like 'select emp.first_name%';
 → v$sqlから実行計画を取得したSQLを探して、SQL_IDを取得します。

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定'));

取得した実行計画は以下のとおり。

PLAN_TABLE_OUTPUT
SQL_ID  cp5qum1t9gdun, child number 0
-------------------------------------
select emp.first_name, emp.last_name, j.job_title, j.min_salary from 
employees emp, jobs j where emp.job_id = j.job_id and emp.salary = 
8300.00

Plan hash value: 1536616604

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS                |           |     1 |    59 |     4   (0)| 00:00:01 |
|   2 |   NESTED LOOPS               |           |     1 |    59 |     4   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL         | EMPLOYEES |     1 |    28 |     3   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | JOB_ID_PK |     1 |       |     0   (0)|          |
|   5 |   TABLE ACCESS BY INDEX ROWID| JOBS      |     1 |    31 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("EMP"."SALARY"=8300)
   4 - access("EMP"."JOB_ID"="J"."JOB_ID")

Note
-----
   - this is an adaptive plan

下のように表示されている行ですが、「child number 0」と表示されています。

SQL_ID  cp5qum1t9gdun, child number 0

今回は同じSQL(SQL_ID)に対して作成された実行計画が一つだったため、「child number 0」になっています。
実行計画が2つあった場合は、「child number 1」と連番を持ちます。
実行計画が異なるので子カーソルが複数あると言ったりするらしいです。

実行計画が複数ある場合に実行計画を取得するとどうなるかというと、
取得に失敗します。

どうすれば取得できるかというと、「child number 1」であれば、DBMS_XPLAN.DISPLAY_CURSORプロシージャの引数に"1"を指定するだけです。
引数を指定しないと全部表示してくれれば良いのですが、そうはなっていないようです。

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定', 1));

child numberの取得方法は色々ありますが、v$sqlでSQL_IDを調べたのであれば、CHILD_NUMBERカラムの値を見るのが一番簡単です。

format指定によって取得できる情報の違い

DBMS_XPLAN.DISPLAY_CURSORの引数formatにより、出力する情報を設定することができます。
formatに指定できる情報はたくさんあるのですが、よく利用する指定方法だけ以降に記載します。

まずは、"ALL"指定。これで実行計画+実行統計を取得します。

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定', format=>'ALL'));

format指定なしと比較して、以下の情報が追加で出力されます。

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$1
   3 - SEL$1 / EMP@SEL$1
   4 - SEL$1 / J@SEL$1
   5 - SEL$1 / J@SEL$1

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25], 
       "EMP"."JOB_ID"[VARCHAR2,10], "J"."JOB_TITLE"[VARCHAR2,35], 
       "J"."MIN_SALARY"[NUMBER,22]
   2 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25], 
       "EMP"."JOB_ID"[VARCHAR2,10], "J".ROWID[ROWID,10]
   3 - "EMP"."FIRST_NAME"[VARCHAR2,20], "EMP"."LAST_NAME"[VARCHAR2,25], 
       "EMP"."JOB_ID"[VARCHAR2,10]
   4 - "J".ROWID[ROWID,10]
   5 - "J"."JOB_TITLE"[VARCHAR2,35], "J"."MIN_SALARY"[NUMBER,22]

次に、"ALL ALLSTATS LAST"を指定した場合です。
statistics_levelがALLでないと取得できません。そのため、sessionレベルでstatistics_levelを以下のように設定します。
statistics_levelをシステム全体で変更することもできますが、オーバーヘッドが大きいので、通常指定することはありません。

alter session set statistics_level=all;
show parameter statistics_level;
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定', format=>'ALL ALLSTATS LAST'));

※LASTを指定しない場合、平均値や合計値(Buffers)が表示されてしまう。

"ALL"を指定した場合と比較して、以下の情報が追加で出力されます。

E-Rows、E-Bytes、E-Time、A-Rows、A-Time、Buffers が追加されています。
"E-"はOracleによって推定されたデータで、"A-"は実際の実行結果になります。BuffersはバッファからGetしたデータブロック数です。

------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |      1 |        |       |     4 (100)|          |      1 |00:00:00.01 |       9 |
|   1 |  NESTED LOOPS                |           |      1 |      1 |    59 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       9 |
|   2 |   NESTED LOOPS               |           |      1 |      1 |    59 |     4   (0)| 00:00:01 |      1 |00:00:00.01 |       8 |
|*  3 |    TABLE ACCESS FULL         | EMPLOYEES |      1 |      1 |    28 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       7 |
|*  4 |    INDEX UNIQUE SCAN         | JOB_ID_PK |      1 |      1 |       |     0   (0)|          |      1 |00:00:00.01 |       1 |
|   5 |   TABLE ACCESS BY INDEX ROWID| JOBS      |      1 |      1 |    31 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       1 |
------------------------------------------------------------------------------------------------------------------------------------

終わったら、statistics_levelは元に戻しておきましょう。

alter session set statistics_level=typical;

次に、「format=>'ADVANCED'」を試してみます。

select plan_table_output from table(DBMS_XPLAN.DISPLAY_CURSOR('SQL_IDを指定', format=>'ADVANCED'));

format=>'ALL'よりもOutline Dataが増える。
付与したヒント句が本当に反映されたかどうかを確認できます。

Outline Data
-------------

  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
      DB_VERSION('12.2.0.1')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "EMP"@"SEL$1")
      INDEX(@"SEL$1" "J"@"SEL$1" ("JOBS"."JOB_ID"))
      LEADING(@"SEL$1" "EMP"@"SEL$1" "J"@"SEL$1")
      USE_NL(@"SEL$1" "J"@"SEL$1")
      NLJ_BATCHING(@"SEL$1" "J"@"SEL$1")
      END_OUTLINE_DATA
  */

開発環境(性能試験以外)なら、statistics_levelをALLにして、formatを"ALLSTATS LAST ADVANCED"しておけば良いかと思います。
ヒント句を使わないなら、"ALL ALLSTATS LAST"

おまけ:statistics_levelをALLにできない場合は、gather_plan_statisticsヒントをつけておけば同じ情報を得られます。

select /*+ gather_plan_statistics */ emp.first_name, emp.last_name, j.job_title, j.min_salary from employees emp, jobs j where emp.job_id = j.job_id and emp.salary = 8300.00;

参考

Oracle公式 DBMS_XPLAN
https://docs.oracle.com/cd/E57425_01/121/ARPLS/d_xplan.htm
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_XPLAN.html#GUID-BAD480AA-351A-48FE-A8E7-F0D8EF643EBF

カタカタブログ Oracle SQL実行計画でヒントの一覧を表示するためのDBMS_XPLAN.display(format=>'ADVANCED')オプションが便利
http://totech.hateblo.jp/entry/2014/03/11/155832

株式会社コーソル Oracleの実行計画を読んでみよう! #dbts2017
https://www.slideshare.net/ryotawatabe/20170907-dbts2017-tokyo-cosol-how-to-read-oracle-execution-plan

Why do not you register as a user and use Qiita more conveniently?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away