新人研修のネタ。その１
はじめに
コストベース・ルールベースの違いについて聞かれると、統計情報を使って
実行計画を決めるか、決まったルールによって実行計画を決めるかの違いと
答えてしまいますが、実際はもう少し複雑でコストベースのオプティマイザ
にはルールベースの頃には無かったQuery Transformationという機能が追加
されており統計情報を元に受け取ったSQLの冗長な部分をけずったり置き換え
たりする最適化の機能が追加されています。
１．Query Transformationの中身
Query Transformationは幾つかの機能に分かれており、Oracle11gだと下記の
通り。（全てではありません。他にも公開・未公開含め幾つかあるはず）
Aggregate Subquery Elimination
Common subexpression elimination
Complex View Merging
Filter predicate push-down in a view
Group by placement
Intersect conversion into join
Join Elimination
Join predicate push down (JPPD)
Materialized view rewrite
Minus conversion into anti-join
Native Full Outer Join
OR expansion
Order by Elimination
Outer to inner join conversion
Predicate move-around
Star transformation
Subquery unnesting
IN / EXISTS -> SEMI JOIN
NOT IN / NOT EXISTS -> ANTI JOIN
Null-Aware and Single Null-Aware ANTI JOIN
Transitive Predicates
上記のそれぞれの機能のうち幾つかは、インターネット上の資料や
研修資料で公開されています。（インターネット上でCost Based Query
TransformationやCBQTで検索してください。Oracleの研修だと
「SQL チューニングワークショップ」に概要説明が入っています)
ここでは判りやすいやつ1点
OR expansion
SELECT *
FROM emp
WHERE job = 'CLERK' OR deptno = 10;
のようなSQLがあり、jobとdeptnoにそれぞれINDEXがついている場合、
下記のように置き換えることがあります。（かならずという訳ではなく
統計情報等使って判定）
SELECT *
FROM emp
WHERE job = 'CLERK'
UNION ALL
SELECT *
FROM emp
WHERE deptno = 10 OR job <> 'CLERK' ;
２．最適化の無効化
話を聞くと便利そうなQuery Transformationですが、複雑でより過激に
進化している機能の為不具合が多く、この為か当機能を無効化する
非公開の初期化パラメータが用意されています。
_optimizer_cost_based_transformation
Query Transformation全部の機能のON,OFF
これをOFFにすると上記全てがOFFになる為、稼動後サポートから不具合
対応で設定変更を勧められても無理。
個別の機能についてもそれぞれ、ON、OFFが可能になるパラメータが用意
されていると思われますが、まとまった形では公開されていません。
個別の不具合対応で一つづつ調べている状況。
（おそらく、サポートの[1274553.1]に上げられているパラメータ全て
調べれば判ると思います）
Order by Elimination
_optimizer_order_by_elimination_enabled
Optimizer Join Push Predicate
_optimizer_multi_level_push_pred
Group by placement
_optimizer_group_by_placement
参考資料
Oracleのオプティマイザが問い合わせ処理を最適化する方法～ソースコードを書いている
エンジニアが語る
1. 問合せの変換（Query Transformation）の部分
これ読んでもらえれば気づくと思いますが、各機能の名前は資料によって
微妙に異なります。
Cost Based Query Transformations by Riyaj Shamsudeen
インターネットの各サイトに出回っているRiyaj Shamsudeenの講演資料。
Optimizer Cost Based Query Transformation [ID 1082127.1]
サポートのページ
Dion Cho氏のページ
Query Transformation関連の記事有り。（韓国の方が書かれた日本語なので読みにくい所
はありますが、日本語で書かれた記事として貴重。）
ablog
http://d.hatena.ne.jp/yohei-a/20110224/1298529324
Vew Mergingの例示。 10053のログで結果の説明をされているのでわかりやすい。
Oracle10g Cost Base Optimizerにまつわる検証
http://www.insight-tec.com/mailmagazine/ora3/vol228.html
Insight Technologyさんの「おら! オラ! Oracle どっぷり検証生活」。10gの頃。
