(Oracle)EXPLAIN PLANによるSQL実行計画の取得

最近Oracleのパフォーマンスチューニングについて勉強しました。
Oracleのパフォーマンスの鍵となるのが、SQL実行計画

SQLを発行した時にどのような実行計画が立てられているのかを確認するには「EXPLAIN PLAN」と「AUTOTRACE」の2種類の方法があります。
今回はEXPLAIN PLANによる実行計画の取得方法をまとめます。

サンプルテーブル

お馴染みのemp表、dept表を使います。

SQL> select * from emp;

EMPNO ENAME      JOB          MGR  SALARY DEPTNO
----- ---------- ---------- ------ ------ ------- 
7369  SMITH      CLERK        7902    800 20
7499  ALLEN      SALES        7698   1600 30
7521  WARD       SALES        7698   1250 30
7566  JONES      MANAG        7839   2970 20
7654  MARTIN     SAL          7698   1250 30
7698  BLAKE      MANAG        7839   2850 30
7782  CLARK      MANAG        7839   2450 10
7839  KING       PRESIDENT           5000 10
7844  TURNER     SAL          7698   1500 30
7900  JAMES      CLERK        7698    950 30
7902  FORD       ANALY        7566   3000 20
7934  MILLER     CLE          7782   1300 10

12 rows selected.

SQL> select * from dept;

DEPTNO DNAME      LOC
------ ---------- ---------- 
10     ACCOUNTING NEW YORK
20     RESEARCH   DALLAS
30     SALE       CHICAGO
40     OPERATIONS BOSTON

emp表のdeptnoにはdept表のdeptnoが入ります。

EXPLAIN PLANによる実行計画の確認

手順は2つ。

  1. EXPLAIN PLANによる実行計画の生成、格納。
  2. PLAN_TABLEからの実行計画取得。

1.EXPLAIN PLANによる実行計画の生成

EXPLAIN PLANの基本形は次のとおり。

explain plan for <SQL文>;

簡単です。このSQLを発行することでSYSスキーマPLAN_TABLE表に、実行計画が格納されます。

SQL> explain plan for select e.ename from emp e,dept d where e.deptno=d.deptno

Explained.

2.PLAN_TABLEからの実行計画取得

EXPLAIN PLANで生成した実行計画を取得するには、select文を発行します。

ただし、単にPLAN_TABLEから取得しても情報がバラバラで見辛いため、DBMS_XPLAN.DISPLAYファンクションを利用します。 DBMS_XPLAN.DISPLAYファンクションは、PLAN_TABLEの情報を集めユーザに見やすい形で実行計画を表示してくれます。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 615168685

---------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time   |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    12 |   156 |     6   (0)| 00:00:01 |
|*  1 |  HASH JOIN          |       |    12 |   156 |     6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | DEPT  |     4 |    12 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | EMP   |    12 |   120 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


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

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   
   15 rows selected.)

DBMS_XPALN.DISPLAYに引数を何も渡さなければ、最後にPLAN TABLEに格納されたSQLの実行計画が抽出されます。

実行計画から分かること

出力された表によって「OracleがどのようにSQLを解釈しているか」「SQLの実行コスト」といったことが分かります。

上記の実行計画からは例えばこんなことが分かります。

  • dept表をフルスキャンして12行が得られる
  • emp表をフルスキャンして12行が得られる
  • empとdeptをハッシュ結合した結果、12行が抽出される。
  • 合計コストは18


では次に、empとdeptにインデックスを付与して実行計画がどう変わるかを確認してみます。

SQL> create index emp_idx on emp(empno)
 
Index created.

SQL> create index dept_idx on dept(deptno);

Index created.


ここでEXPLAN PLANとPLAN TABLEの取得方法を少し変えてみます。
EXPLAIN PLANでSTATEMENT_IDを設定することで、任意の実行計画を取得できます。

explain plan set statement_id='<任意のID>' for <SQL文>;

select * from table(dbms_xplan.display('plan_table','<任意のID>'));


これに従い、インデックスを付与した後の同じSQLの実行計画を取得します。

SQL> explain plan set statement_id='emp_dept_join' for select e.ename from emp e,dept d where e.deptno=d.deptno

Explained.

SQL> select * from table(dbms_xplan.display('plan_table','emp_dept_join'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3691029676

-------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |    12 |   156 |     3 (0)  | 00:00:01 |
|   1 |  NESTED LOOPS       |          |    12 |   156 |     3 (0)  | 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMP      |    12 |   120 |     3 (0)  | 00:00:01 |
|*  3 |   INDEX RANGE SCAN  | DEPT_IDX |     1 |     3 |     0 (0)  | 00:00:01 |
-------------------------------------------------------------------------------


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

   3 - access("E"."DEPTNO"="D"."DEPTNO")

   15 rows selected.

実行計画の表が変わっていることが分かります。

  • dept表の検索がインデックススキャンに変わっている。これによりdept表からは指定の1件だけ抽出される。
  • emp表とdept表の結合がネステッドループ結合へと変わっている。
  • 全体のコスト値が9に減少。

これはdept表でのインデックススキャンが可能になったことでOracleオプティマイザが「ハッシュよりネステッドループの方が効率が良い」と判断したことになります。
(サンプルのSQL、あまりいい例ではなかったかな……)

EXPLAIN PLANの注意点

EXPLAIN PLANで取得できる実行計画は「想定される実行計画」です。
そのため、実際にSQLを流した際は異なる実行計画が利用される可能性があります。


以上、EXPLAIN PLANの簡単な使い方でした。
なおPLAN_TABLE表やDBMS_XPLANパッケージの詳細な使い方は公式ガイドを参照しましょう。

PLAN_TABLE
DBMS_XPLAN

Oracleの現場を効率化する100の技

Oracleの現場を効率化する100の技

新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意 (DB Selection)

新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意 (DB Selection)