Explain Plan の使用と確認方法

今日はちょっとライト目にExplain Planの使用方法でも書きます。
まぁ、週末だしね、多少簡単げなとこで。

EXPLAIN PLAN ってなに？

Oracle Database パフォーマンス・チューニング・ガイド
10g リリース2（10.2）
B19207-01
19 EXPLAIN PLANの使用方法
http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/server.102/B19207-01/ex_plan.html

EXPLAIN PLAN文は、SELECT、UPDATE、INSERTおよびDELETE文について
Oracle オプティマイザが選択した実行計画を表示します。
文の実行計画とは、Oracleがその文を実行するために行う一連の処理です。

どんなときにつかう？

SQLチューニングに使います。
上にもあるように、EXPLAIN PLAN を実行することで、
SQL文の実行計画が確認できます。

実行計画とは？

実行計画とは、Oracleが発行されたSQLを実行するために、
どのようなパス(方法)でデータにアクセスするかを示すものになります。
実行計画はOracle内部の Optimizer によって決定されます。
ちなみに Optimizerには Rule Base のものと Cost Base のものがありますが、
10gでは、Cost Base を使用してください。

Rule Base：Oracleが決めているルールに従ってアクセスパスを決める。
Cost Base：事前に取得した「統計情報」を元に、実際にそのパスでアクセスしたときの
コストを計算して、一番コストの小さいものを選ぶ。

もっと具体的に実行計画を教えて

ITの書き物らしくたとえでいってみましょうか。

たとえば、あなたが「Oracle DBA コマンドブック」の中から、
「EXPLAIN PLAN文」のSQLについて調べようとしたとき、
どのようにそれが書かれているページを探すでしょうか。

一度見たことのある本だったら、巻末に索引がついているのを知っているので、
索引の「E」のところから、「EXPLAIN PLAN文」のところを見て、
95ページという記述にしたがって、95ページ目を調べますよね？

この「索引を調べて->それに書かれてるページを参考に実データを探す。」が
Oracleでいうところの実行計画になります。
わかりましたでしょうか。

ちなみに、「Oracle DBA コマンドブック」はこれのこと。

Oracle DBAコマンドブック

作者: 中島益次郎
出版社/メーカー: ソフトバンククリエイティブ
発売日: 2005/01
メディア: 単行本
購入: 3人クリック: 25回
この商品を含むブログ (2件) を見る

DB管理者は買っておいて損はないですね。

具体的な使い方

まず、EXPLAIN PLAN 文を実行してSQLの解析を行ないます。

実行計画を確認したいSQLに「explain plan for」をつけて実行します。
たとえば、

select * from emp where empno = 7499 ;

の実行計画を知りたい場合は、下記のように実行します。

SQL> explain plan for
  2  select * from emp where empno = 7499 ;

解析されました。

この時、SQL自体の実行はされません。
よって本番環境でも、INSERT や UPDATE の実行計画をとることが可能ですし、
負荷の高いSQLでも気にせず解析が可能です。
# でも操作ミスで実行しちゃったー！
# とかいうことはやっちゃう可能性があるので、
# 十分注意してくださいね。

解析結果を出力します。

この状態では解析結果がデータベースの中に
格納されただけなので、取り出してやる必要があります。
昔は、長ったらしいSQLを書いて取り出してたんですが、
最近はこんなに簡単になりました。

SQL> set linesize 120
SQL> set pagesize 10000
SQL> select * from table(dbms_xplan.display);


PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2949544139

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    37 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    37 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   2 - access("EMPNO"=7499)

14行が選択されました。

いざ読みます。

ここはねぇ、正直もう経験値がモノをいうよねぇ・・・。
インターネッツ上にもいろいろと上がってるようなので、
Google先生に「Oracle チューニング実行計画」とかで聞いてみてください。
このブログでもおいおい書きたいとは思いますが。

いちおう、項目の説明だけはしておきます。

Plan hash value

Oracle上でのSQLの通し番号のようなものです。調査に必要になる場合があります。

中ごろにある表

ここが実行計画になります。
この場合、PK_IMP を「INDEX UNIQUE SCAN」で走査したのち、
そこで得た「ROWID」を元に EMP にアクセスしている。
ということになります。

Predicate Information

条件句のアクセス方法が書かれています。
access となっていると INDEX を使用した検索
filter だと全件検索となります。

今回の場合、 access("EMPNO"=7499) となっていますので、 "EMPNO"=7499 については、
INDEXを用いて検索がされたと読めます。どのINDEXかを知りたい場合は、
左側に出ている番号(今回なら 2)を実行計画の表の Id とマッチングしてみてください。
今回の場合、Id:2 を見ると、PK_EMP を用いた、INDEX UNIQUE SCAN であったということが
わかります。

ただしっ！！！
「Predicate Information」の内容に関しては、裏づけ文書がありません。
経験則から、access -> index 、filter -> full といっているだけなんです。
なので、もしかしたら、違うかも・・・。
違ったらごめんなさい。

ちなみに、全件検索の場合の例ね。

SQL> explain plan for
  2  select * from emp where ename = 'ALLEN' ;

解析されました。

SQL> set linesize 120
SQL> set pagesize 10000
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    37 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    37 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ENAME"='ALLEN')

13行が選択されました。

ほら、「filter」になってるでしょ？
だから、たぶんそうだと思う。思うんだ。

ではでは。
次回はヒント句なんか書いてみようかなぁ・・・。