OracleDB

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

2018年7月5日

こんにちは!土門大貴(daikidomon)です。

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

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

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

SQL*Plusで確認

最も一般的なのが「SQL*Plus」の「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 offautotraceを無効化。

下の例が実行計画と実行時統計情報を表示した例です。
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;

解析されました。

SQL> select PLAN_TABLE_OUTPUT from TABLE(DBMS_XPLAN.DISPLAY());

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)

 

関連記事

-OracleDB

Copyright© スタートアップIT企業社長のブログ , 2020 All Rights Reserved.