Oracle12cで性能問題になったときの点検ポイント

記事内に広告を含みます

この記事には広告を含む場合があります。記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。

リプレースにおいて同じ製品の後継機なのに何故か性能問題になることがありますよね。

私が携わった、とある案件でもoracle11gから12cにした際、性能問題が発生しました。
その際に点検したポイントをまとめてみました。
（今回の事例ではここを変えることでうまくいったということであり汎用的ではないことに注意してください。）

点検ポイント

今回点検したポイントは3つあります。
1つは動的統計、もう1つはオプティマイザ、最後の1つはSQL計画ディレクティブです。
詳細はOracle社のOracle SQLおよびPL/SQLの改善というページで確認できます。

動的統計

動的統計は11gでも提供されている機能ですが12cで強化が図られています。
この動的統計を無効、あるいはサンプリングレベルを0に近づけることで改善することがあります。

2.2.4.7 動的統計
SQL文のコンパイル中に、オプティマイザが、適切な実行計画を生成するために既存の統計で十分かどうかを検討して、動的統計を使用するかどうかを決定します。
既存の統計が十分でない場合は、動的統計が使用されます。
動的統計は永続的であり、他の問合せで使用できます。
1つのタイプの動的統計は、動的サンプリングによって収集された情報です。
従来、問合せの1つ以上の表について統計がない場合にのみ、動的サンプリングが自動的に発生していました。
動的サンプリングによってそれらの表の基本統計が収集されてから、文が最適化されました。
現在は、動的統計がすべてのSQL文に対して役立つかどうか、また動的サンプリングが正しい方法かどうかは、オプティマイザによって自動的に決定されます。
そうである場合、使用する動的サンプリング・レベルもオプティマイザにより決定されます。
動的サンプリングで収集される統計の範囲には、JOIN句とGROUP BY句も含まれるようになりました。
オプティマイザで必要と認められると、動的統計は自動的に使用され、生成される統計は統計リポジトリに保持され、他の問合せでも使用することができます。

オプティマイザ

新しくなったオプティマイザが原因の可能性があります。

オプティマイザは過去のオプティマイザを指定することが可能です。
旧バージョンのオプティマイザを指定することで改善することがあります。

2.2.4.12 新しいタイプのオプティマイザ統計
カーディナリティ見積りを改善するため、Oracleは、データ・スキューがある列にヒストグラムを作成します。
個別値の数が254を上回る列のために新たに2種類のヒストグラムが導入され、その結果、ヒストグラムによって収集されるカーディナリティ見積りが改善されました。
高頻度ヒストグラムが作成されるのは、少数の個別値がデータのほとんど(データの99%超)を占める場合です。
このヒストグラムは、特に頻度が高い少数の個別値を使用して作成されます。
統計的に重要ではない、頻度の低い値を無視することにより、頻度の高い値に対応した高品質のヒストグラムが作成されます。
あるいは、高さ調整ヒストグラムと頻度ヒストグラムを組み合せたハイブリッド・ヒストグラムを作成することもできます。
高さ調整ヒストグラムでは、頻度の高い値が常に終了点の値になり、1つの値が複数のバケットにまたがることはありません。
各終了値の頻度を記録することにより、よく出現する値の頻度を記録します。
高頻度ヒストグラムの方が正確なカーディナリティ見積りを提供できるのは、列の個別値が254個を超えるが、非常に頻度が高い少数の個別値も含まれる場合です(データの99%超にそれらの値の1つが含まれる)。

SQL計画ディレクティブ

上記の問題を引き起こしている根本的な原因が12cから実装されたSQL計画ディレクティブです。

性能問題が出るフェーズにもよりますがSQL計画ディレクティブの設定値をデータベース全体で変更するか特定のSQL実行前後だけ変更するかは慎重に決定する必要があります。
データベース全体の設定を変えるときにはSQLの実行結果に変動がないことを保証する必要があるのでより時間がかかることになります。

2.2.4.16 SQL計画ディレクティブ
PL/SQL DBMS_STATSパッケージを使用して収集される統計の他に、オプティマイザは、コンパイル時には動的サンプリングを使用して、実行時には適応実行計画を使用して統計を収集できます。
以前のリリースでは、コンパイルと実行の統計はカーソル・キャッシュのみに格納され、永続的ではありませんでした。
SQL計画ディレクティブの導入により、コンパイルと実行の統計がディスク上のSYSAUX表領域で永続化されます。
SQL計画ディレクティブを使用すると、オプティマイザが実行計画を生成するときに、アクセス対象のオブジェクトに関してより多くの情報にアクセスできます。
情報には、表t1とt2がSQL文で結合される場合、または列の間に相関の可能性がある場合に、動的サンプリングを使用すべきかどうかというものも含まれます。
SQL計画ディレクティブにより、コンパイル統計(動的サンプリングの結果)と実行統計(適応実行計画の結果)の両方がSYSAUX表領域に永続化され、複数のSQL文で使用できるようになるため、実行計画の正確さが向上します。

今日のまとめ

私が携わった案件では検索結果が変わってしまった特定のSQLを実行するときのみSQL計画ディレクティブを使わないように処理を組み込むことで性能劣化を避けることができました。
ただし、Oracle社などの見解などでは上記の設定が有効化している方が性能は良くなるそうです。
確かにわざわざ性能劣化する機能を組み込むとは考えづらいですね。
安直に無効化するとリプレース直後などは早いかもしれませんが徐々に劣化する可能性もあるので選択は慎重に行うようにしましょう。

検索結果が一致しない！Oracle 12cとヒント句の組合せにはご用心