[S] oracle12cで性能問題になったときの点検ポイント

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

oracle11gから12cにした際、やはり性能問題が発生しました。その際に点検したポイントです。(今回の事例ではここを変えることでうまくいったということであり汎用的ではないことを強調しておきます。)

1つは動的統計、もう1つはオプティマイザ、最後の1つはSQL計画ディレクティブです。

http://docs.oracle.com/cd/E57425_01/121/NEWFT/chapter12101.htm#OBJECTIVENO00004

2.2.4.7 動的統計

SQL文のコンパイル中に、オプティマイザが、適切な実行計画を生成するために既存の統計で十分かどうかを検討して、動的統計を使用するかどうかを決定します。既存の統計が十分でない場合は、動的統計が使用されます。動的統計は永続的であり、他の問合せで使用できます。1つのタイプの動的統計は、動的サンプリングによって収集された情報です。従来、問合せの1つ以上の表について統計がない場合にのみ、動的サンプリングが自動的に発生していました。動的サンプリングによってそれらの表の基本統計が収集されてから、文が最適化されました。

現在は、動的統計がすべてのSQL文に対して役立つかどうか、また動的サンプリングが正しい方法かどうかは、オプティマイザによって自動的に決定されます。そうである場合、使用する動的サンプリング・レベルもオプティマイザにより決定されます。動的サンプリングで収集される統計の範囲には、JOIN句とGROUP BY句も含まれるようになりました。オプティマイザで必要と認められると、動的統計は自動的に使用され、生成される統計は統計リポジトリに保持され、他の問合せでも使用することができます。

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

2.2.4.12 新しいタイプのオプティマイザ統計

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

高頻度ヒストグラムの方が正確なカーディナリティ見積りを提供できるのは、列の個別値が254個を超えるが、非常に頻度が高い少数の個別値も含まれる場合です(データの99%超にそれらの値の1つが含まれる)。

もう1つは新しいオプティマイザが原因の可能性があります。オプティマイザは過去のオプティマイザを指定することが可能です。

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

上記の問題を引き起こしている根本的な原因が12cから実装されたSQL計画ディレクティブでした。
性能問題が出るフェーズにもよりますが設定値をデータベース全体で変更するか特定のSQL実行前後だけ変更するかは慎重に決定する必要があります。