OracleをバージョンアップしたらSQLが性能劣化したのでやったこと

OracleをバージョンアップしたらSQLが性能劣化したのでやったことです。

OracleをバージョンアップしたらSQLが性能劣化したのでやったこと

今更だけど、Oracleを11gから12cへバージョンアップしたシステムがあるんだけど、移行後に実行計画を確認したら、SQLが遅くなっていた。実行計画を見てみると、HashJoin結合からNestedLoop結合変更されてしまっていた。これにより、インデックスが有効活用されていないのだ。

一般的にテーブルのレコード数が少ない場合はNestedLoop結合が有利で、多い場合はHashJoin結合が有利になるっていわれている。今回12cに移行したことで、実行計画がHashJoin結合からNestedLoop結合変更になってしまい、これが原因でSQLが遅くなってしまったようです。もともと既存のSQLはNestedLoop結合になるようにチューニングされていたから当然といえば当然なのかな。

ここでは OracleをバージョンアップしたらSQLが性能劣化したのでやったこと を紹介します。

18c,19cへの移行でも同様の効果が出ていますが、実施は自己責任でお願いします。


OPTIMIZER_FEATURES_ENABLEの変更

Oracleバージョンアップ後のクエリ性能劣化の暫定処置として高い効果を期待できるのが、「OPTIMIZER_FEATURES_ENABLE」の変更です。移行前のバージョンでパフォーマンス実績がある場合にはぜひ試していただきたい方法です。

これは、インストールされているOracleのバージョンのオプティマイザ機能を変更して、別のバージョンのオプティマイザ機能を使うパラメータです。

指定方法としては、パラメータ、ヒント句の２種類があります。まずはセッション単位で指定して実行計画を確認しましょう。


--ALTER SESSION で11.2.0.4 に設定
ALTER SESSION SET OPTIMIZER_FEATURES_ENABLE='11.2.0.4';

--ヒント句で11.2.0.4 に設定
SELECT /*+ OPTIMIZER_FEATURES_ENABLE('11.2.0.4') */ no,name from …;

パラメータはインスタンス全体にも指定可能(ALTER SYSTEM SET ...)ですが、「OPTIMIZER_FEATURES_ENABLE」の設定を変更してしてしまうと他の多くの機能に影響を及ぼしてしまう可能性があります。また、いつまでも前のバージョンに縛られてしまうので、できれば使わずに済ませたいですね。

同様の効果として下記のパラメータもあります。


ALTER SESSION SET "_OPTIMIZER_UNNEST_SCALAR_SQ" = FALSE;

「_OPTIMIZER_UNNEST_SCALAR_SQ」とは、オプティマイザが実行計画を作成する際に、スカラー副問合せ式の展開をおこなうかどうかを制御するパラメータです。これが12cからはデフォルトがTRUEに設定されています。これで性能改善するなら、こっちのほうがいいでしょう。

OPTIMIZER_INDEX_COST_ADJを変更する。

SQLの基本はインデックスをいかに使ってもらうように書くかですよね。実際にインデックスが使われるかどうかはOracleオプティマイザ次第となるわけですが、このパラメータを変更することによりインデックスを使ってくれるようになります。

デフォルト値が100で、1～100の間で設定できます。


ALTER SESSION SET OPTIMIZER_INDEX_COST_ADJ = 10;

なにがなんでもインデックスを使わせたいのであれば「1」を設定すればそういった効果が出ます。

OPTIMIZER_INDEX_CACHINGを変更する。

このパラメータ値を高く設定すると、NestedLoop結合が採用されやすくなります。

デフォルト値が0で、0～100の間で設定できます。


ALTER SESSION SET OPTIMIZER_INDEX_CACHING = 90;

筆者が移行したシステムは、既存のSQLがNestedLoop結合になるようにチューニングされていたので、これは大きな効果がありました。

OPTIMIZER_DYNAMIC_SAMPLINGを変更する。

このパラメータは、統計情報が動的にサンプリングされ、共有メモリ上に仮の統計情報を自動的に利用する内部のチューニング機能です。

サンプリングレベルを｢0｣に設定すれば、動的サンプリング機能を使用しなくなります。

デフォルト値が2で、0～11の間で設定できます。


ALTER SESSION SET OPTIMIZER_DYNAMIC_SAMPLING = 0;

OPTIMIZER_DYNAMIC_SAMPLINGの各値に関する説明は割愛します。

12cから設定値「11」が追加されました。「11」を設定すると、オプティマイザが必要だと判断した場合に、自動的に統計が収集されるようになります。つまり、Oracle任せの設定ってことですね。サンプリングのブロック数を含めてすべて自動的に判断されるので、どれにするか悩んだら「11」って設定もありなのかも。

まとめ

OracleをバージョンアップしたらSQLが性能劣化したのでやったことを紹介しました。

バージョンアップって、結局のところオプティマイザとの戦いじゃないでしょうかね。せっかくバージョンアップしても、実行計画が意図しない方向に変わってしまい、パフォーマンスが落ちてしまったら意味がないですからね。システム担当者の心配は尽きないですよね。

筆者の経験では、18cや19cへの移行でも「OPTIMIZER_INDEX_COST_ADJ」「OPTIMIZER_INDEX_CACHING」「OPTIMIZER_DYNAMIC_SAMPLING」の３つの値を調整することで、既存のバージョンと変わらない結果が得られました。

皆さんも試してみてください。

おつかれさまでした。

この記事がお役に立ちましたら シェア をお願いいたします。