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

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

動的サンプリングは、SQLの実行前に、SQLがアクセスするテーブルのデータをサンプリングし、
サンプリングした結果を元にCBOがSQLの実行計画を作成する機能です。
通常の場合、すなわち動的サンプリングが実行されない場合、
CBOはオプティマイザ統計を元に実行計画を作成します。オプティマイザ統計は
データの特性を集約したサマリ情報
（具体的には行の平均長や列値の最小値・最大値、ヒストグラムなど）に過ぎないため、
仮にオプティマイザ統計が最新の状態であっても、CBOのコスト予測が外れて最適な
実行計画が作成されない場合がないのは、先のエントリで示したとおりですが、
実際のデータをサンプリングすれば、予測が外れる可能性を大幅に減らすことが
できます（実際にデータを先読みしているので、予測が当たるのは当たり前といえば当たり前ですが・・・）。

では、初期化パラメータOPTIMIZER_DYNAMIC_SAMPLING を指定して、動的サンプリングを
有効にしてから、先のエントリのSQLを実行し、Oracle Database が最適と判断した実行計画を
見てみましょう。

なんと！（わざとらしいですね）想定どおりのインデックススキャン(INDEX RANGE SCANオペレーション)
を用いた実行計画が選択されました！

SQL> alter session set statistics_level=ALL;
セッションが変更されました。

SQL> alter session set OPTIMIZER_DYNAMIC_SAMPLING=10;
セッションが変更されました。

SQL> SELECT sum(val1) FROM tbl0 WHERE flg1 = ‘X’ AND flg2 = ‘X’;
SUM(VAL1)
———-
1

SQL> select * from table(DBMS_XPLAN.display_cursor(’42fxsw7fcxuht’,0, ‘ALL ALLSTATS LAST’));

PLAN_TABLE_OUTPUT
————————————————————————————————————————————-
SQL_ID 42fxsw7fcxuht, child number 0
————————————-
SELECT sum(val1) FROM tbl0 WHERE flg1 = ‘X’ AND flg2 = ‘X’

Plan hash value: 1204532613

————————————————————————————————————————————
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
————————————————————————————————————————————
| 0 | SELECT STATEMENT | | 1 | | | 2 (100)| | 1 |00:00:00.01 | 3 |
| 1 | SORT AGGREGATE | | 1 | 1 | 7 | | | 1 |00:00:00.01 | 3 |
| 2 | TABLE ACCESS BY INDEX ROWID| TBL0 | 1 | 1 | 7 | 2 (0)| 00:00:01 | 1 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | IDX0_TBL0 | 1 | 1 | | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 2 |
————————————————————————————————————————————

Query Block Name / Object Alias (identified by operation id):
————————————————————-

1 – SEL$1
2 – SEL$1 / TBL0@SEL$1
3 – SEL$1 / TBL0@SEL$1

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

3 – access(“FLG1″=’X’ AND “FLG2″=’X’)

Column Projection Information (identified by operation id):
———————————————————–

1 – (#keys=0) SUM(“VAL1”)[22]
2 – “VAL1″[NUMBER,22]
3 – “TBL0”.ROWID[ROWID,10]

Note
—–
– dynamic sampling used for this statement (level=10) ←★

38行が選択されました。

動的サンプリングが実行された場合は、Noteセクションにその旨の表示がされることに
注意してください。ここで動的サンプリングの実行有無をチェックできます。

動的サンプリングレベルには0から10を指定でき、マニュアルでは基本的にサンプリング
対象のブロック数に影響するような表記があります。
しかし、ブロック数だけではなく、動的サンプリングの実行有無の
判断にも使用されるようです（小さいレベルを指定すると動的サンプリングが実行されないような動作が見られる場合もある模様・・・詳細不明）。

動的サンプリングを用いて作成された実効計画が適切であると判断できる場合は、
この実行計画に近づくように、元のSQLにヒントを追加したりするなどして、
元のSQLがこの実行計画で実行されるようにするよう誘導することもできます。

なお、動的サンプリングの結果は共有プールに保管され、再利用されるようです。
毎回動的サンプリングを実行するのはナンセンスですので、妥当な動作であると思われますが、
再利用のロジックが明確でないため、いわゆるアプリケーションからのSQL実行時において
動的サンプリングを使用することには十分な検討が必要です。（特にサンプリングレベルの設定）

### ではSQLチューニングアドバイザは不要か？

動的サンプリングは非常に便利な機能です。
しかし、動的サンプリングがあれば、SQLチューニングアドバイザが不要かというと
そんなことはありません。
SQLチューニングアドバイザでは、アドバイザ結果をSQLプロファイルして提供すること
があります。SQLプロファイルは最適な実行計画を選ぶことを可能にする補足的な統計であり、
ヒントと異なり、実行計画を完全に固定する情報ではありません。
したがって、データ量が増減して最適な実行計画が変化した場合でも、それに追従することができます。
ヒントではこのような柔軟な処理は実現できません（ターゲットとする実行計画が変化するたびに、
それに応じてヒントを書き換える必要があります）。

また、動的サンプリングを用いる方法では、実行計画の誘導作業を自動化することが
できません。SQLにヒントを埋め込む作業は、SQLをコーディングするエンジニアが行う必要があります。

したがって、あくまでも動的サンプリングを用いる方法は、「SQLチューニングアドバイザ
が利用できない環境で、やむを得ず使用する方法」と位置づけることが妥当でしょう。