Oracle SQLチューニング Season2（第27回）第5章 Dynamic SQLチューニング方法 （2/2）
今回のSQLチューニングブログ2nd Season（第27回）は
「Dynamic SQLチューニング適用時の注意事項・Dynamic SQLにヒントを適用する」について解説していきます。
では、早速始めましょう。
5.3 Dynamic SQLチューニング適用時の注意事項
Dynamic SQLの性能改善をするには、通常の単一SQLとは異なり、まずは照会パターン別に性能分析を行い、きちんと改善案を導き出してから、他の照会パターンに影響を与えないことを確認の上で適用する必要があります。
その為、単一SQLに比べて性能改善作業の難易度が増すことをまず覚えておきましょう。
5.3.1 特定の条件に合ったヒントもDynamic適用
理解を助けるため、上のSQLについての説明を先にします。
- /*+ leading(z) */ ヒントを適用すると、常にインラインビューZから実行されます。
- TBF42、TBB12テーブルは合計2回ずつ実行されます。インラインビューZで最初に照会条件でデータを減らした後、抽出したROWIDで再ジョインを実行します。
- インラインビューZの役割はTBF42, TBB12テーブルのWhere節の照会条件に対するデータ処理を担当します。 照会条件処理後、ROWIDを抽出し、再び二つのテーブルとROWIDジョインを実行します。 このような方式は、二つのテーブルの照会条件と結合カラムがインデックスカラムで構成されており、テーブルデータへのアクセスなしでデータ処理が可能な場合に使用する方法です。
例えば、TBF42条件で抽出されたデータが10000件で、TBB12条件で抽出されるデータが10000件なのに、二つのテーブルのジョイン後、100件に減る状況で、二つのテーブルの条件処理やジョイン実行をインデックスのみで処理が可能な場合に、このような改善方法をよく使います。
上記のようなSQLであった場合、インラインビューZで作成してLEADING（Z）ヒントを適用する意図はとても良いと思います。
しかしその反面、SQLには致命的な欠点が2つ存在します。
まず、’Dynamic SQLチューニングの進行方法’でも説明した内容になりますが、TBF42テーブルの照会条件のうち、効率的な条件は受給者住民番号と受給者名です。ところが、インラインビューZの外部にも条件があり、インラインビューZで作成してLEADING（Z）ヒントを適用したSQLを作成しても性能上におけるメリットがありません。
なぜなら、顧客が画面上で受給者住民番号または受給者名で照会した場合には、性能がむしろ悪くなるからです。
次にインラインビューZは、支払年月日または抜粋年月日（以下、条件1）では常に照会されるものの、支店条件（条件2）では常に照会される必須条件ではありません。条件1は、TBF42テーブルの照会条件であり、条件2はTBB12テーブルの照会条件です。
もし、条件1と条件2が一緒に照会されない場合、条件2のTBB12テーブルは不必要に同じデータへ2回アクセスすることになります。
その結果、パフォーマンスに悪影響を与える結果を招く可能性があります。
又、上のXMLファイルのSQLは、照会条件によってWhere節の条件が変わるDynamic SQLであり、先に説明したようにDynamic SQLにヒントを追加する時、特定の条件に該当するヒントを全ての照会パターンに適用されないように注意する必要があります。
5.3.2 効率的な条件を含む照会は性能が保証されなければならない
「特定の条件に合うヒントもDynamic適用」でも紹介したように、受給者住民番号や受給者名のように効率的な条件で照会する場合は、常に性能が保証されなければなりません。
例えば、1件の照会をするために所要時間が10秒かかるとしたらどうでしょうか？
グループ化後に出力されるデータが1件だったとすれば、照会するユーザーは所要時間が10秒かかったのは当然の結果であると判断することができます。しかし、住民番号のように当該照会条件で照会後に出力されたデータが1件なのに対して、所要時間が10秒かかるとすれば、おそらくユーザーが納得することは難しいでしょう。
そして、このような照会は業務特性上、頻繁に照会されることが多く、DBサーバーの性能に大きく影響を与える可能性があります。
したがって、Dynamic SQLの性能改善における最初の目標としては、効率的な照会条件下では、必ず性能が保証されなければならないという結論になります。
そのため、条件分析段階で効率的と評価した照会条件は、最初に改善策を適用後、残りの条件に対する改善策を適用する必要があります。
5.4 Dynamic SQLにヒントを適用する
ここまで、Dynamic SQLとは何か？どんな利点があるのか？改善案を適用する時の注意点は何か？性能改善はどのように行われるのか？ などについて説明をしてきましたが、ここからは、以下の例を活用して性能改善手順に合わせてチューニング（ヒント適用）を行ってみましょう。
Dynamic SQLの性能改善に向けて、まずはSQLの照会条件を把握しましょう。
上記の場合、非常に単純なパターンとなっていますが、実際に使用される業務ではもっと複雑である可能性が高いです。したがって、SQLの照会パターンを間違えずに効果的に把握するためには、テーブル別の使用条件を先に整理することが望ましいと言えます。
SALESテーブルのTIME_ID（取引時間）は範囲検索で、PRODUCTSテーブルのPROD_ID（製品番号）とCUSTOMERSテーブルのCUST_ID（顧客番号）カラムは=条件で検索されることが確認できます。また、テーブルごとに一つの条件を持っています。
照会条件の把握ができたら、次は各照会条件及びジョインカラムの効率性を確認するために必要なデータを下記のように調査します。
上記の表を見てみると、検索条件の一つであるCUSTOMERSテーブルのCUST_IDカラムは平均1件を必ず抽出するので、非常に効率的です。
さらに、もう一つの検索条件であるPRODUCTSテーブルのPROD_IDカラムもCUST_IDカラムと同じように平均1件を抽出するものの、SALESテーブルのPROD_IDカラムと結合すると抽出される件数が非常に多くなり、非効率的であることが分かります。この部分についてもう少し説明すると、PRODUCTSテーブルのPROD_IDで照会される場合、1件のデータだけが抽出されますが、SALESテーブルのPROD_IDカラムとジョインすると(918430/72 – 0)*1 = 12755件が抽出されるため、効率的ではあるとは言い難く、最後の検索条件であるSALESテーブルのTIME_IDカラムでは、平均630件を抽出するため、CUST_IDカラムよりは非効率的であるものの、PROD_IDカラムよりは効率的であることが分かります。
その結果、効率的な照会条件カラムの順序は、CUST_ID、TIME_ID、PROD_IDで判断することができます。
最も効率的な照会条件の順序
CUST_ID → TIME_ID → PROD_ID
上記表を通じて確認された照会条件の効率性を基に改善案の導出及び適用作業を行うことにします。
照会カラムのNDV（Number of Distinct Value）値、ジョインカラムの効率など全ての情報を組み合わせた結果、効率的な照会条件カラムの順序は表[5-3]のようにCUST_ID、TIME_ID、PROD_IDに決定されました。
では、各照会パターン別に最適な実行計画を立てることができるようにXMLファイルにヒントを適用してみましょう。
それぞれの照会パターンごとにヒントを通じた補正が完了したら、優先順位に合わせてヒントを適用する必要があります。例えば、CUSTOMERSテーブルのCUST_ID（顧客番号）とSALESテーブルのTIME_ID（取引時間）の照会条件が同時に検索される場合、より効率的なCUST_ID（顧客番号）の条件が先行して検索されるようにしなければならないからです。そのためには、ヒント追加時にCUST_ID（顧客番号）が先行するように誘導するヒントを追加しなければなりません。
したがって、優先順位に合致するDynamicヒントの適用は必要であると考えます。
尚、優先順位に合致するDynamicヒントの最終適用案は下記の通りです。
Dynamic SQLの性能改善作業を進めることは、そう簡単ではありません。
何故なら、様々なパターンのWhere節についての分析をすべて行う必要がある上、それぞれの照会パターンに対応する改善案についての用意が必要となるからです。
また、条件に入力される値が持つ性格、業務についてきちんと把握する作業も非常に重要となってきます。
単一SQLの性能改善作業と比べると多くのエネルギーが消費されることはどうしても避けられませんが、Dynamic SQLを一つの単一SQLとして認識して、性能改善案を安易に適用してしまう事によって、むしろDatabaseシステムに性能問題を引き起こす致命的なミスを防ぐことができるという点においては、十分に支払う価値があるコストであると私たちは確信しています。
次回のSQLチューニングブログは・・・
SQLチューニングブログ 2nd Season（第28回）
第6章「 大量のデータ処理性能改善方法 」
※ 次回から新しいテーマでお送りします。（全5回シリーズ）
