Oracle の SQL の実行計画を調べる

Update: 2008-11-25

Oracle が SQL 文を実行するときに、内部で「実行計画」というのを使います。どのテーブルのどのインデックスを使ってどういう順番でデータを取得するのかという計画です。 9i までの「ルール・ベース」の場合 SQL 文の記述で決まります。「コスト・ベース」の場合、 SQL 文の記述内容と、各テーブル・インデックスの統計情報（データ量とか値のばらつきとか）で決まります。その「実行計画」を調べる方法について説明します。

ここではとっかかりのことだけ。詳しい情報は Oracle の各バージョンの正規のドキュメントを見てください。 http://otn.oracle.co.jp/ のアカウントは無料ですぐに作成できます。

EXPLAIN PLAN の基本的な使い方

「ルール・ベース」の場合、テーブルとインデックスを本番環境と同じ状態にしてください。「コスト・ベース」の場合は、それに加えて統計情報も本番環境と同じ状態にしてください。それができない場合、あまりいい方法ではありませんが、本番環境で実施してください。

例1

SET LINESIZE 140
SET PAGESIZE 0
SET ECHO ON
SPOOL SQLファイル実行計画取得.log

EXPLAIN PLAN FOR
SELECT
`COL1
, COL2
, COL3
FROM TABLE1
WHERE COL4 = :PARAM1
;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

SPOOL OFF

先頭の 3行は結果を見やすくするためのおまじないです。

この例1 の場合、 SELECT COL1, COL2, COL3 FROM TABLE1 WHERE COL4 = :PARAM1 が測定したい SQL です。プログラム中でパラメータ値を使う箇所は :param1 のような書式にしておいてください。パラメータ名は何でもいいです。

「コスト・ベース」の環境の場合実行計画だけ、「ルール・ベース」の場合それに加えてコスト値が表示されます。コスト値は、 Oracle が内部で処理量を判断するために使う尺度です。統計情報が現実のデータの状態をそこそこ正確に反映している場合は、このコスト値を下げることで十中八九 SQL の実際の処理も早くなります。統計情報が現実の状態から乖離している場合は、当てになりません。

「ルール・ベース」の場合も「コスト・ベース」の場合も TABLE ACCESS FULL が要注意です。行数の多いテーブルで WHERE句で検索対象を絞ったつもりなのになぜか TABLE ACCESS FULL していることがあれば、そもそも WHERE句で指定したカラムにインデックスが付いているか調べてみるとか、 SQL 文の書き方を変えてみるとかしてください。

本番と同じ統計情報を持つテスト環境の作成

「コスト・ベース」の場合、Oracle の統計情報は次の手順で他の DB にコピーできます。

コピー元のDBで、所定の定義のテーブルに統計情報を格納する。
そのテーブルをエクスポートする。
コピー先の DB にインポートする。
テーブルに格納された統計情報を適用する。

詳しくは Oracle のドキュメントを見てください。10g R1 の場合「PL/SQL パッケージ・プロシージャおよびタイプ・リファレンス」の DBMS_STATS の項を見てください。

この手順で統計情報をインポートしたテスト環境を使えば EXPLAIN PLAN で本番と同じ実行計画の取得ができます。