WR blog

さて、これまでで、実行計画のツリーのたどり方と、ツリーの構成要素である各オペレーションで 実行されている処理の概要がわかったと思います。

しかし、これだけの情報では、実行計画と実行されたSQLのWHERE条件を対応づけて理解することは 難しいです。（できなくはありませんが、かなり推測に頼る形になります。）

SQLのWHERE条件と対応付けるためには、フィルタ述語とアクセス述語に着目する必要があります。

### Predicate Informationセクション

実行計画をDBMS_XPLAN.DISPLAY_CURSORで取得した場合、フィルタ述語とアクセス述語に関する情報は Predicate Informationセクションに表示されます。

—————————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | —————————————————————————————– | 0 | SELECT STATEMENT | | | | 20 (100)| | | 1 | NESTED LOOPS | | 10 | 40120 | 20 (0)| 00:00:01 […]

これまでのエントリに記載してきたように、実行計画は階層的なツリー構造で表現 されます。 本エントリでは、実行計画の読み方のfirst stepとして、 実際の処理順序に即したツリー構造のたどり方を説明します。

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, ‘LAST’));

PLAN_TABLE_OUTPUT ——————————————————————————————— SQL_ID dvv5bah5b4k51, child number 1 ————————————- SELECT /*+ FULL(PA) INDEX(CH idx_chpa) USE_NL(CH) LEADING(PA) */ cid, cname, pa.pid, pname FROM ch, pa WHERE ch.pid = pa.pid and pa.pid = 1

Plan hash value: 3514264536

—————————————————————————————– | Id | […]

[先のエントリ]( http://www.csus4.net/d/2011/04/30/check_cbo/ ) で、実行計画の見積もりミスの可能性を調べるため、CBOの見積もり行数と実行時行数の 差異をチェックする方法をお伝えしました。 実際にCBOの見積もり行数と実行時行数の差異が大きいSQLを見つけた場合は、 現在使用している実行計画が最適かどうか、最適な実行計画はどのようなものかを 検討する必要が出てきます。

しかし、この作業は簡単なものではありません。 [先のエントリ]( http://www.csus4.net/d/2011/04/30/check_cbo/ ) の例は、1つのテーブルにのみアクセスするきわめてシンプルなものであり、 テーブルスキャン(TABLE ACCESS FULL) よりもインデックススキャン (INDEX RANGE SCAN) が効率的であるとすぐにわかりました。 しかし、通常のアプリケーションではSQLはもっと複雑であり、 最適な実行計画の検討は、Oracle Databaseに関するSQLチューニングスキルが必要であり、時間もかかる作業です。

Oracle Database 9.2 以前など、アドバイザ機能が充実していなかったバージョンを 使用していたときは、スキルを持ったデータベースエンジニアが、さまざまな角度から 検討し、最適と思われる実行計画を検討していたと思います。 この作業は、実際に効果が得られるかどうか不透明なわりに、時間と労力を要する非効率的なものでした。 チューニング作業は、時間をかければかけただけ、必ず改善されるという保障があるわけではないという 側面があるからです。

Oracle Database 10g以後では、アドバイザ機能が充実し、SQLチューニングアドバイザ を活用して、Oracle Database が自動的に最適な実行計画を作成してくれるように なっており、作業コストを大幅に削減できます。 しかし、SQLチューニングアドバイザを使用するためには Enterprise Edition の利用に くわえて、別売のオプション機能 Diagnostic Pack と Tuning Packが必要であるため、 使用できる人が限られるのが現実ではないでしょうか。

### 動的サンプリングによる最適な実行計画検討の自動化

実は、動的サンプリングという機能を使うことで、SQLチューニングアドバイザが使用で きない場合でも、最適と思われる実行計画の検討をOracle […]

先日のエントリ [SQLパフォーマンス問題調査でEXPLAIN PLAN、SQLトレースは（ほとんど）使わない](http://www.csus4.net/d/2011/02/24/whynotuse_explainplan_sqltrace/) でお勧めしたDBMS_XPLAN.DISPLAY_CURSORについて、基本的な使い方を説明します。

### 基本的なDBMS_XPLAN.DISPLAY_CURSORの使い方

詳細はマニュアル(Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス) のDBMS_XPLANパッケージの箇所をご確認いただきたいのですが、

* 10.2 : [DISPLAY_CURSORファンクション](http://otndnld.oracle.co.jp/document/products/oracle10g/102/doc_cd/appdev.102/B19245-02/d_xplan.htm#i998364) * 11.1 : [DISPLAY_CURSORファンクション](http://otndnld.oracle.co.jp/document/products/oracle11g/111/doc_dvd/appdev.111/E05686-02/d_xplan.htm#i998364) * 11.2 : [DISPLAY_CURSORファンクション](http://download.oracle.com/docs/cd/E16338_01/appdev.112/b56262/d_xplan.htm#i998364)

DISPLAY_CURSORファンクションには以下の引数を指定します。

DBMS_XPLAN.DISPLAY_CURSOR( sql_id IN VARCHAR2 DEFAULT NULL, child_number IN NUMBER DEFAULT NULL, format IN VARCHAR2 DEFAULT ‘TYPICAL’);

sql_id: SQLの識別子 child_number : 子カーソル番号 format: 出力フォーマット(後ほど簡単に説明)

このファンクションはテーブルファンクションであるため、TABLE演算子で変換すると 通常の表のように扱えます。典型的には引数sql_idに調査対象のSQLのsql_idを指定して、 以下のように使用します。

[…]

[先日のエントリ](http://www.csus4.net/d/2010/12/29/batch_sq/) に、 Oracle Database 11.1 からは優れたカーソル共有(ACS, Adaptive Cursor Sharing, 適用カーソル共有) という機能が導入されているため、エントリの内容が当てはまらないと書きました。 優れたカーソル共有が機能すると、バインド変数化されたSQLと共有カーソルの対応関係が異なってきます。

具体的には、従来(10.2以前)では、バインド変数値によらず、 原則的にバインド変数化されたSQLと共有カーソルは1対1関係にありましたが、 11.1で優れたカーソル共有が機能する(デフォルトで有効)と、 バインド変数に指定された値によって、既存の共有カーソル（≒実行計画）を使用 すべきでないとOracleが判断すると、新規に別の共有カーソル（≒実行計画）を作成し、 SQL実行にこの共有カーソルを使用します。 すなわち、バインド変数化されたSQLと共有カーソルは1対多の関係となる可能性があります。

(*1) ここの説明において、共有カーソルは子カーソルを指します。

### では、優れたカーソル共有により、意図しない実行計画が使用される事態を予防できるか？

[…]