最近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つ。

EXPLAIN PLANによる実行計画の生成、格納。
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.