12cへのアップグレード後、性能劣化が発生する

年が明けたばかりのある日、パフォーマンス・チューニングのサポート支援を依頼されました。

11.2.0.4から12.1.0.2へのアップグレード後、一部のSQLが著しく性能劣化したとの事です。

本番リリース前の新本番環境で、環境は以下の通りです。

Oracle RAC（2ノード） EE 12.1.0.2

RHEL Version 7.3

11.2.0.4から12.1.0.2へのアップグレード後に性能劣化したとの事なので、まず11gとの差異を確認します。

具体的にはOPTIMIZER_FEATURES_ENABLE='11.2.0.4'を指定し、APの性能テストを実施してもらいました。

結果、数割のSQLで性能改善が見られたので、原因をMOS(MyOracleSupport)で調査したところ、以下のドキュメントに辿り着きました。

12CでANSI SQL文の実行が遅くなる (ドキュメントID 2287961.1)

データベースを11.2.0.4から12.1.0.2にアップグレードした後、ANSIバージョンのSQL文が実行完了までに時間が掛かり、実行計画を確認すると、関連する多くのビューに対し、ブルーム・フィルタリングとともにスカラー副問合せのネスト解除が行われるという現象です。

性能改善したSQLについてもOPTIMIZER_FEATURES_ENABLE='11.2.0.4'の設定前後で実行計画を確認し、ドキュメントID 2287961.1に該当していると判断しました。

回避策としては以下のいずれかになります。

_optimizer_unnest_scalar_sq とは、オプティマイザが実行計画を作成する際に、スカラー副問合せ式（Scalar subquery）*1の展開を行うかどうかを制御するパラメータで、12cからデフォルトがTRUEに変更されています。

OPTIMIZER_FEATURES_ENABLE = '11.2.0.4'を設定してしまうと他の多くの機能に影響を及ぼしてしまう為、_OPTIMIZER_UNNEST_SCALAR_SQ = FALSEを設定する事になりました。

しかしながら、これで性能改善しなかったSQLも残っています。

そのチューニング顛末は、「12cへのアップグレード後、性能劣化が発生する(2)」に続きます。

*1:1つの行から1つの列値のみを戻す副問合せ

おらくるのいる生活