[OracleDB] 実行計画の確認方法

2018年7月5日2022年6月4日

Oracle Databaseを運用する上で「実行計画」の管理は必須です。

性能問題が発生した場合にSQLの「実行計画」を確認し、どこをチューニングするのか検討します。

今回は「実行計画」の確認方法を紹介したいと思います。

SQL*Plusで確認

最も一般的なのが「*SQLPlus**」の「autotrace」で確認する方法です。

ただし事前に準備が必要になるので以下の設定を必ず行いましょう。

$ sqlplus / as sysdba
SQL> @?/sqlplus/admin/plustrce.sql
SQL> grant PLUSTRACE to <autotraceを利用したいユーザ>

上記の設定が終わったら、以下の方法で「autotrace」を使用しましょう。

set autotrace <on | off | traceonly>

なお「autotrace」時には「実行結果」、「実行計画」、「実行時統計」を表示有無を指定することができます。

以下が「autotrace」での表示設定になります。

オプション	動作
set autotrace on explain	実行結果と実行計画を表示。
set autotrace on statistics	実行結果と実行時統計情報を表示。
set autotrace on	実行結果、実行計画、実行時統計情報を表示。
set autotrace traceonly	実行計画と実行時統計情報を表示。実行結果は表示しない。ただしFetchは内部的に行っている
set autotrace off	autotraceを無効化。

下の例が実行計画と実行時統計情報を表示した例です。

SQL> set line 1000
SQL> set autotrace traceonly
SQL> select ORDER_ID, ORDER_DATE from ORDERS;

実行計画

----------------------------------------------------------
Plan hash value: 2719558606

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |   105 |  1575 |     2   (0)| 00:00:01 |
|   1 |  VIEW                  | index$_join$_001  |   105 |  1575 |     2   (0)| 00:00:01 |
|*  2 |   HASH JOIN            |                   |       |       |            |          |
|   3 |    INDEX FAST FULL SCAN| ORDER_PK          |   105 |  1575 |     1   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| ORD_ORDER_DATE_IX |   105 |  1575 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access(ROWID=ROWID)

統計

----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
       3687  bytes sent via SQL*Net to client
        674  bytes received via SQL*Net from client
          8  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        105  rows processed

「autotrace」では結果セットをFetchした後に各種情報が表示されます。

そのため重いSQLの場合返ってこない時があるので注意しましょう。

explain plan文で確認

「explain plan」文でも「実行計画」を確認することができます。

「統計情報」の確認はできないので注意してください。

「autotrace」と違い、実行結果をFetchしないので重いSQLの場合は「explain plan」を利用しましょう。

下が「explain plan」文の使用方法です。

explain plan for <実行計画を確認したいSQL>;

さらに解析したSQLを表示させるために、「DBMS_XPLAN」プロシージャを利用します。

select * from table(DBMS_XPLAN.DISPLAY());

下が実行時のサンプルになります。

SQL> explain plan for select ORDER_ID, ORDER_DATE from ORDERS;

解析されました。

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------
Plan hash value: 2719558606

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |   105 |  1575 |     2   (0)| 00:00:01 |
|   1 |  VIEW                  | index$_join$_001  |   105 |  1575 |     2   (0)| 00:00:01 |
|*  2 |   HASH JOIN            |                   |       |       |            |          |
|   3 |    INDEX FAST FULL SCAN| ORDER_PK          |   105 |  1575 |     1   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| ORD_ORDER_DATE_IX |   105 |  1575 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------------------------------------------------
   2 - access(ROWID=ROWID)

なお、SQLIDが分かれば「explain plan」文で解析しなくても、

「DBMS_XPLAN」プロシージャで実行計画を取得出来ます。

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(\'<SQLID>\'));

下が「DBMS_XPLAN.DISPLAY_CURSOR」を使用した例です。

SQL> select SQL_TEXT, SQL_ID, HASH_VALUE, OLD_HASH_VALUE from V$SQL where SQL_TEXT = 'select ORDER_ID, ORDER_DATE from ORDERS';

SQL_TEXT                                SQL_ID                                  HASH_VALUE OLD_HASH_VALUE
--------------------------------------- --------------------------------------- ---------- --------------
select ORDER_ID, ORDER_DATE from ORDERS 7jvry69gppf0u                           1599780890     2175230393

SQL> select * from table(DBMS_XPLAN.DISPLAY_CURSOR('7jvry69gppf0u'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  7jvry69gppf0u, child number 0
-------------------------------------
select ORDER_ID, ORDER_DATE from ORDERS

Plan hash value: 2719558606

--------------------------------------------------------------------------------------------
| Id  | Operation              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |                   |       |       |     2 (100)|          |
|   1 |  VIEW                  | index$_join$_001  |   105 |  1575 |     2   (0)| 00:00:01 |
|*  2 |   HASH JOIN            |                   |       |       |            |          |
|   3 |    INDEX FAST FULL SCAN| ORDER_PK          |   105 |  1575 |     1   (0)| 00:00:01 |
|   4 |    INDEX FAST FULL SCAN| ORD_ORDER_DATE_IX |   105 |  1575 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access(ROWID=ROWID)