優れたカーソル共有があればバッチ処理SQLのリテラル化は不要か？

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

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

先日のエントリ ( http://www.csus4.net/d/2010/12/29/batch_sq/ ) では、
バインド変数値の指定がパターンXの場合は索引を使った実行計画が適切であり、
パターンYの場合はテーブルスキャンを使った実行計画が適切である状況を例にして、
パターンXとパターンYのバインド変数値が指定された2回のSQL実行において、
同じ共有カーソルが使いまわされてしまうために、
意図しない実行計画が使用され、結果として突然パフォーマンスダウンが発生する
事象がおこりうると説明しました。

(バインド変数値にパターンXを指定) 共有プール
SELECT * FROM ORDERS WHERE …; ─┬→ 共有カーソルA
│ 索引を使った実行計画
(バインド変数値にパターンYを指定) │
SELECT * FROM ORDERS WHERE …; ─┘

優れたカーソル共有が機能すれば、このような事象は回避できるのでしょうか？

結論からいうと、完全には回避できません。
既存の共有カーソル（≒実行計画）を使用すべきでないとOracleが判断するためには、
どうやら、最低１回は既存の共有カーソル（≒実行計画）を用いて実際にSQLを実行する
ことが必要なようです。
上記の例で言えば、パターンXのバインド変数値を指定してSQLを実行し、
パターンYのバインド変数値を指定してSQLを実行した後で、

(バインド変数値にパターンXを指定)　　　 共有プール
SELECT * FROM ORDERS WHERE …;　 ─┬→　共有カーソルA
　　　　　　　　　　　　　　　　　　│　　　 索引を使った実行計画
(バインド変数値にパターンYを指定)　 │
SELECT * FROM ORDERS WHERE …;　 ─┘

再度、パターンYのバインド変数値を指定してSQLを実行すると、
このタイミングで既存の共有カーソル（≒実行計画）を使用すべきでないとOracleが判断
するようです。

　　　　　　　　　　　　　　　　　　　　 共有プール
　　　　　　　　　　　　　　　　　　　　 　共有カーソルA
　　　　　　　　　　　　　　　　　　　　 　　 索引を使った実行計画
(バインド変数値にパターンYを指定)　　　
SELECT * FROM ORDERS WHERE …;　 ──→　共有カーソルB
　　　　　　　　　　　　　　　　　　　　　　 テーブルスキャンを使った実行計画

「ようです」と書いたのは、このあたりの動作については、あまり明確に仕様が公開
されていないため、よくわからないのです。

まぁ、正確なところはわからないにせよ、最低１回は既存の共有カーソル（≒実行計画）
を用いて実際にSQLを実行する必要があるのは確かなようです。
というのも、ハードパースも実行せず、SQLも実行せずに、
既存の共有カーソル（≒実行計画）を使用すべきかどうかを判断するのは難しい
（というか論理的に考えれば無理）ためです。

### というわけで結局→リテラルSQLを使用しましょう。

11.1で導入された優れたカーソル共有が機能する状況でも、
突然パフォーマンスダウンが発生する事象は回避できません。
もちろん、上記のとおり、同一のバインド変数値を指定して複数回SQLを実行すれば、
2回目以降において適切な実行計画が使用され、パフォーマンスダウンから復旧する
ことは可能ですが、
バッチ処理やデータウェアハウス系で実行されるSQLが、
同一のバインド変数値を指定して複数回実行されることは考えにくいため、
効果はきわめて限定されるでしょう。

* (*2) 1/6 09:22 追記 :ただ、そもそもACSの動作が明確でないですから、「限定される」と言い切るのは若干微妙ですが・・・。ただ、論理的にリテラルSQLが適切ですし、動作が明確でないACSを「頼り」にするのは適切な設計方針ではないでしょう。

### 優れたカーソル共有は意味がないのか？
では、そもそも優れたカーソル共有にはまったく存在価値がないのでしょうか？
そんなことはありません。今回の一連のエントリでは、
OLTP系の処理、バッチ処理やデータウェアハウス系の処理という
類型化された2つのSQLの実行形態を例にして説明していますが、
実際には、これらの中間的な実行形態も存在しうるでしょう。
たとえば、WHERE句の指定が複雑なSQLを頻繁に繰り返し実行するような実行形態などです。

このような場合には、優れたカーソル共有はトータルのパフォーマンス向上に寄与します。
仮にパフォーマンスダウンが発生した場合でも、n回目(n>=2) 以降のSQL実行時に適切な実行計画に
修正し、不適切な実行計画が継続的に使用されてしまう事態を避けることができます。

### 参考にさせていただいたレポート

インサイトテクノロジーさんのすばらしいレポートです。
参考にさせていただきました。貴重なレポートを公開いただき、ありがとうございますー

* [Oracle 11g検証 隠れた新機能検証　その５｜おら! オラ! Oracle｜技術情報｜株式会社インサイトテクノロジー](http://www.insight-tec.com/mailmagazine/ora3/vol426.html)
* [Oracle 11g検証 隠れた新機能検証　その６｜おら! オラ! Oracle｜技術情報｜株式会社インサイトテクノロジー](http://www.insight-tec.com/mailmagazine/ora3/vol427.html)
* [Oracle 11g検証 隠れた新機能検証　その７｜おら! オラ! Oracle｜技術情報｜株式会社インサイトテクノロジー](http://www.insight-tec.com/mailmagazine/ora3/vol428.html)