WR blog

これまでのエントリに記載してきたように、実行計画は階層的なツリー構造で表現
されます。
本エントリでは、実行計画の読み方の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 | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————————–
| 0 | SELECT STATEMENT | | | | 20 (100)| |
| 1 | NESTED LOOPS | | 10 | 40120 | 20 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | PA | 1 | 2004 | 9 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| CH | 10 | 20080 | 11 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_CHPA | 10 | | 1 (0)| 00:00:01 |
—————————————————————————————–

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

2 – filter(“PA”.”PID”=1)
4 – access(“CH”.”PID”=1)

24 rows selected.

### 実行計画のツリー構造とオペレーションの親子関係

実行計画は、一般に複数のオペレーションから構成されます。オペレーションは、親子関係に
したがってインデントして整形されたツリー構造となります。

たとえば、上記の実行計画は、NESTED LOOPS, TABLE ACCESS FULL などのオペレーション
から構成されます。(オペレーションの種類と処理内容については次のエントリで説明します。）

オペレーションの親子関係についてですが、
Id=1 NESTED LOOPSは Id=2 TABLE ACCESS FULLと、Id=3 TABLE ACCESS BY INDEX ROWID
の親となります。同様に Id=3 TABLE ACCESS BY INDEX ROWID
Id= 4 INDEX RANGE SCAN の親となります。

Id=0 SELECT STATEMENT
↑
│行ソース
│
Id=1 NESTED LOOPS
↑ ↑
行ソース┌─────┘ └─────┐行ソース
│ │
Id=2 TABLE ACCESS FULL Id=3 TABLE ACCESS BY INDEX ROWID
↑
│行ソース
│
Id=4 INDEX RANGE SCAN

それぞれのオペレーションは、行ソースとよばれる表から取得した行データ（のサブセット）を出力します。
親オペレーションは子オペレーションの行ソースを入力として処理を行います。

概念的な説明だけではわかりにくいので、先の実行計画がどのように処理されるかを
イメージしながら、読み方を説明してみましょう。

Continue reading 実行計画のツリーのたどり方 (実行計画の読み方#1)

[先のエントリ]( 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 Database任せにすることが
できます。

Continue reading SQLチューニングアドバイザの代用品としての動的サンプリング

先日のエントリ
[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を指定して、
以下のように使用します。
Continue reading DBMS_XPLAN.DISPLAY_CURSORの使い方とちょっとした落とし穴

[先日のエントリ](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) ここの説明において、共有カーソルは子カーソルを指します。

### では、優れたカーソル共有により、意図しない実行計画が使用される事態を予防できるか？
Continue reading 優れたカーソル共有があればバッチ処理SQLのリテラル化は不要か？