SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは（ほとんど）使わない

Oracle DatabaseのSQLパフォーマンス問題が発生したときの、実行計画の取得についてまとめます。

SQLパフォーマンス問題が発生したときに、まず確認すべき情報はSQLの実行計画ですが、
Oracle Databaseには、実行計画を確認する方法が、大きく分けて3つあります。

* EXPLAN PLAN : （一般にSQL*Plusから）EXPLAN PLAN FOR <実行したいSQL>を実行して、実行計画を確認します。
* SQLトレース : SQLの実行前にalter session sql_trace=true;を実行して、トレースファイルに出力された情報から実行計画を確認します。
* DBMS_XPLAN.DISPLAY_CURSORプロシージャ : 共有プールに保管された共有カーソルの情報から、実行計画を確認します。共有カーソルが共有プールからage-outされた場合、実行計画は確認できません。

### EXPLAIN PLANとSQLトレースを使わない理由

従来から存在しており、かつ、広く知られている実行計画の取得方法は、
EXPLAIN PLANとSQLトレースの2つです。しかし、私がOracle DatabaseのSQLパフォーマンス問題
に対応する場合、これらの2つの方法は原則的に使いません。

これら2つの方法には、いくつか使いにくい点があるからです。

まず、EXPLAIN PLANで実行計画を確認する方法では、得られた実行計画が、パフォーマンス
問題が発生したときと同じであるという保障がありません。
EXPLAIN PLANを実行した環境(一般にSQL*Plusを実行する環境)と、
問題となっているSQLを実行したアプリケーションの環境で、初期化パラメータ値が
同じであるという保障はありませんし、バインド変数などの、
プログラミングインタフェースに依存する部分が、実行計画の作成に影響する可能性もあります。

次に、SQLトレースですが、SQLを実行する前にあらかじめトレース設定をしておく
必要があります。たいていのアプリケーション実行環境では、あらかじめSQLトレース
設定をしていませんから、当然ながらパフォーマンス問題が発生したときの実行計画を
SQLトレースから確認することはできません。
また、SQLトレースから確認できる実行計画は、非常にシンプルな形式であるため、
Predicate Infomation(フィルタ条件やアクセスパスに関する情報)
などの実行計画の詳細情報が確認できません。

SQL ID: 6377g9s3af8u6
Plan Hash: 3732797803
select ch, cname, pa.pa, pname
from
ch, pa where ch.pa = pa.pa and pa.pa = 1

call count cpu elapsed disk query current rows
——- —— ——– ———- ———- ———- ———- ———-
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 112 0 100
——- —— ——– ———- ———- ———- ———- ———-
total 10 0.00 0.00 0 112 0 100

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91

Rows Row Source Operation
——- —————————————————
100 NESTED LOOPS (cr=112 pr=0 pw=0 time=0 us cost=2 size=1113 card=1)
1 TABLE ACCESS BY INDEX ROWID PA (cr=3 pr=0 pw=0 time=0 us cost=2 size=85 card=1)
1 INDEX UNIQUE SCAN IDX_PA (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74595)
100 TABLE ACCESS BY INDEX ROWID CH (cr=109 pr=0 pw=0 time=0 us cost=0 size=1028 card=1)
100 INDEX RANGE SCAN IDX_CHPA (cr=9 pr=0 pw=0 time=1386 us cost=0 size=0 card=1)(object id 74598)

### DBMS_XPLAN.DISPLAY_CURSORプロシージャのすすめ

このような理由により、私は、SQLパフォーマンス問題が発生したときには、
10.1から導入された(※3)
DBMS_XPLAN.DISPLAY_CURSORプロシージャ(※1) を使います。
このプロシージャはV$SQL_PLAN(共有プール内の実行計画情報)から実行計画を取得する
ため、ほぼ確実に(※2)、SQL実行時に使用されていた実行計画を取得できますし、
Predicate Infomationなどの実行計画の詳細情報を確認できます。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(‘6377g9s3af8u6’));

PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID 6377g9s3af8u6, child number 0
————————————-
select ch, cname, pa.pa, pname from ch, pa where ch.pa = pa.pa and
pa.pa = 1

Plan hash value: 3732797803

—————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | NESTED LOOPS | | 1 | 1113 | 2 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| PA | 1 | 85 | 2 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | IDX_PA | 1 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| CH | 1 | 1028 | 0 (0)| |
|* 5 | INDEX RANGE SCAN | IDX_CHPA | 1 | | 0 (0)| |
—————————————————————————————–

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

3 – access(“PA”.”PA”=1)
5 – access(“CH”.”PA”=1)

24行が選択されました。

* (※1) またはその仲間のDBMS_XPLAN.DISPLAY_AWRなど
* (※2) 共有カーソルが共有プールからpurgeされた場合は確認できません。また、例外的に複数の子カーソルが存在する場合は、 子カーソルを特定するために、ASH(Active Session History)と付き合わせる 必要がある場合があります。
* (※3) 9.x では、直接V$SQL_PLANを問い合わせるカスタムSQLを使用します。

EXPLAIN PLANとSQLトレースは、
Oracle 8iでも使用できるOracle Databaseに昔から存在している方法です。
したがって、昔からOracle Databaseを使用しているエンジニアの中には、これ以外の実行計画の取得方法をしらずに、
EXPLAIN PLANとSQLトレースを使用している場合もあるでしょう。
しかし、上記の理由から、Oracle Database 10g以降(*3)では、DBMS_XPLAN.DISPLAY_CURSORプロシージャ
を使用することを、強くお勧めします。

### そのほかの実行計画確認方法

なお、上記の3つ以外にも、実行計画を確認する方法があります。
しかし、これらは、上記の3つのバリエーションと考えることができます。(※4)

* SQL*Plusのautotrace

→ 内部的にEXPLAIN PLANを実行しています。

* StatspackのSQLレポート
* (10.2-) AWRのSQLレポート (要 Enterprise Edition + Diagnostics Pack)

→ これらの機能では、V$SQL_PLANの情報をスナップショットとしてコピーした情報を
元に実行計画を取得しているため、 DBMS_XPLAN.DISPLAY_CURSORプロシージャ(V$SQL_PLAN)
と同等であると分類できます。
SQLの共有カーソルが、共有プールからage-outされた場合、V$SQL_PLAN から実行計画を確認すること
ができませんが、これらの方法では、共有プールからage-outされた過去のSQL実行についても
実行計画を確認できる場合があります。
(スナップショット収集時に、共有カーソルが共有プールに存在しているかどうかに依存します）

* (※4) 11.1から導入されたリアルタイムSQL監視の分類は難しいですが、別の方法と分類するか、
V$SQL_PLANを強化したものと位置づけるのが妥当でしょうね。