始めに
超絶性能が劣化したSQLをチューニングした時の顛末を書いてみる
データ構造
顛末
夜間バッチの運用試験を実施したら、バッチが2時間経過しても完了しなかった。
1か月前、同じテストを実施したときは10分程度で完了していたのに
調査
V$SQLからロングランしたSQLを特定した
ロングランしたSQLのデータ構造はデータ件数が1千万件のエンティティがA,B,C,Dと4つあり、リレーションは下の図のような感じになっている
SQL_IDから実行計画を取得し、1か月前の実行計画と比較したところ、A→Bのinner joinしている個所の結合がHashJoin結合から、NestedLoop結合に変化していた。
どうやら、Bのデータが1か月前のテスト時からデータ量が増えたため、OracleのオプティマイザがNestedLoop結合を選択するようにしたらしい。
AもBも大量のデータがあるので、NestedLoop結合はしてほしくないのに
チューニング
A→BをinnerjoinしているサブクエリにUSE_HASH(A,B)のHint句をつけてバッチを実行したら、1か月前と同じ10分で処理が完了したよ
おわりに
Oracle12cはNestedLoop結合を好んで用いる傾向があるので、大量のデータ同士をJoinするクエリが遅くなったら、まずは実行計画が変動していないか確認してみよう