七転八起SEの備忘録

通常から重かった検索がついに10分を超え、タイムアウトが発生してしまった。
ヒント句を利用し、改善を図る。



通常はOracleが最適な実行計画を選択してくれている。(注1)
ただし、複雑なSQLの場合（7つ以上の表を結合するなど）は最適な実行計画とならないことがある。
そのような場合に実行計画に対するヒントを利用する。

(注1)
複数のテーブルを結合するSQLを実行すると、ORACLEはネステッドループ、ハッシュ、マージの3種類のいずれかの結合方法を選択し実行計画を立てる。



一般的に結合する件数が多くなるほどHASH結合のほうが早くなる可能性が高い。
最も大きな要因は「ネステッドループ結合は物理読み込みが多いとパフォーマンス劣化が激しい」ため。




SELECT /*+ ヒント句 */ 項目,… FROM 〜


・コメントの記号「/*」の直後に「+」を付けるとヒント句となる
・「SELECT」の後に記載する


一見すると普通にコメントの様に見える。実際にヒントの書き方が間違っていたとしても、コメントとして無視されるためエラーにならない。したがって、ヒントを書いたら思い通りの実行計画になったか確認すること。

テーブル名の指定方法

ヒントの対象となるテーブルを指定する場合、FROM句に書かれているテーブル名をそのまま指定する。
よって、別名を定義している場合はその別名を書くこと。※別名を定義していない場合はテーブル名を指定する。


SELECT /*+ USE_HASH(B) */ *
FROM xxxxx A
INNER JOIN yyyyy B
ON A.zzz = B.zzz

今回試した主なヒント

USE_NL
　指定したテーブルをネステッドループ結合にてスキャンさせる
　＜使用例＞
　　　/*+ USE_NL(A B) */


USE_HASH
　指定したテーブルをハッシュ結合にてスキャンさせる
　＜使用例＞
　　　/*+ USE_HASH(A B) */


FULL
　指定したテーブルに対して、フルスキャンさせる
　＜使用例＞
　　　/*+ FULL(A) FULL(B) */
　　　※1テーブルずつ分けて書くこと(「FULL(A B)」のようにまとめて書かない)


ORDERED
　FROM句に記述した順番通りに結合する
　＜使用例＞
　　　/*+ ORDERED */



結果、10分以上かかっていた検索が13秒前後へ落ち着いた。
まだ改善は見込めそうだが、本事象はタイムアウトが発生し検索が出来ない、そのことでの影響が大きい。
リリースまでのスピードが優先されるため、今回はこれで良しとする。