Oracleデータベースで実行計画を確認する方法

IT技術
2022.09.18
スポンサーリンク
急げ！新ドメインは早いもの勝ち！

概要

実行計画とは

実行計画とは、階層構造を持った「オペレーション」と呼ばれる処理ステップで構成された、「SQLを実行する手順書」である。

適切な実行計画を立案するには、以下の点に留意する必要がある。

  • 最新のオプティマイザ統計の収集
  • 適切な初期化パラメータの設定
  • 適切な索引や制約の作成・設定

実行計画の読む順序

  1. 下位の階層 の （右側にインデントされている）オペレーションから実行される。
  2. 同じ階層の（インデントが同じ）オペレーションが複数存在する場合は、上部から実行される。
  3. 上位の階層のオペレーションは、下位の階層のオペレーションの処理結果を受ける。

システム環境

Oracle Database 19c Enterprise Edition Release 19.16.0.1.0

実行計画の確認方法

方法1. SQLを実行して確認する(AUTOTRACE機能)

実行計画を確認したいSQL文が実行可能な場合は、AUTOTRACE機能を使用するのが最も簡単に実行計画を確認できる。

SQL> SET AUTOTRACE ON または TRACEONLY
SQL> <実行計画を確認したいSQL文>
  • SET AUTOTRACE ON：SQL文の実行結果に加えて、実行計画と統計情報を表示する。
  • SET AUTOTRACE TRACEONLY：実行計画と統計情報を表示する。(SQL文の実行結果は表示しない。)

以下、実行例を記載する。
Id:1が「TABLE ACCESS STORAGE FULL| TEST_TABLE」となっており、TEST_TABLEテーブルに対してフルスキャンしていることが確認できる。

SQL> SET AUTOTRACE TRACEONLY
SQL> select * from test_table;
Execution Plan
----------------------------------------------------------
Plan hash value: 3979868219
--------------------------------------------------------------------------------
--------
| Id  | Operation                 | Name       | Rows  | Bytes | Cost (%CPU)| Ti
me     |
--------------------------------------------------------------------------------
--------
|   0 | SELECT STATEMENT          |            |     3 |    18 |     3   (0)| 00
:00:01 |
|   1 |  TABLE ACCESS STORAGE FULL| TEST_TABLE |     3 |    18 |     3   (0)| 00
:00:01 |
--------------------------------------------------------------------------------
--------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive
parallel operation
Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        699  bytes sent via SQL*Net to client
         52  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          3  rows processed
SQL>

方法2. 過去のSQL実行履歴から確認する(DBMS_XPLAN.DISPLAY_CURSORプロシージャ)

方法1はSQL文を実際に実行して確認する方法であるが、確認対象のSQL文がupdate文などで実行することが難しい場合、過去のSQL実行履歴から確認する方法を用いる。

※過去のSQL実行履歴を確認するために、V$SQLを参照している。
V$SQLは共有プールのライブラリ・キャッシュ(=メモリ領域)に格納されている情報のため、再起動や共有プールのフラッシュ後、またあまりに過去の履歴は消えてしまって確認できない場合がある。

V$SQLで過去実行したSQL文の「SQL_ID」と「子カーソル番号」を確認する。

SQL> SELECT sql_text,sql_id,child_number
FROM V$SQL WHERE sql_text LIKE '%<実行計画を確認したいSQL文>';

DBMS_XPLAN.DISPLAY_CURSORプロシージャを使い、実行計画を確認する。

SQL> SELECT * FROM table(
 DBMS_XPLAN.DISPLAY_CURSOR(
  SQL_ID => '<SQL_ID>',CURSOR_CHILD_NO => <子カーソル番号>
 )
);

以下、実行例を記載する。
Id:2が「INDEX RANGE SCAN | TEST_INDEX」となっており、TEST_INDEXインデックスを使って索引検索していることが確認できる。

SQL> SELECT sql_text,sql_id,child_number
  2  FROM V$SQL WHERE sql_text LIKE 'select * from test_table where col1=1';
SQL_TEXT                                 SQL_ID        CHILD_NUMBER
---------------------------------------- ------------- ------------
select * from test_table where col1=1    0j9kxfn5srfj5            0
SQL> 
SQL> SELECT * FROM table(
  2  DBMS_XPLAN.DISPLAY_CURSOR(
  3  SQL_ID => '0j9kxfn5srfj5',CURSOR_CHILD_NO => 0
  4  )
  5  );
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  0j9kxfn5srfj5, child number 0
-------------------------------------
select * from test_table where col1=1
Plan hash value: 3631346743
--------------------------------------------------------------------------------
------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost
(%CPU)| Time     |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
------------------
|   0 | SELECT STATEMENT                    |            |       |       |     2
 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TEST_TABLE |     1 |    26 |     2
   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TEST_INDEX |     1 |       |     1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   (0)| 00:00:01 |
--------------------------------------------------------------------------------
------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("COL1"=1)
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of no expensive
parallel operation
24 rows selected.
SQL>
タイトルとURLをコピーしました