はじめに
Oracle Databaseには非常に多くの初期化パラメータがありますが、設定によっては期待するようなパフォーマンスが出なかったり、極端な場合はシステムがまともに動かない状態になってしまいます。
今回は、私が過去関わったOLTPシステムで、パフォーマンス改善のために検討したパラメータについて紹介したいと思います。
※システムにより特性が異なりますので、あくまで参考情報です。システムに反映するかどうかは、負荷テストなどの検証を実施の上、検討ください。
なお、Oracleの初期化パラメータの調整による、パフォーマンスチューニングでは、SGA、PGAのメモリ割り当て調整によるチューニングが一番効果がありますが、この記事では、メモリチューニング以外のパラメータについて紹介します(メモリチューニングについては、別途記載しようと思います)。
前提条件
Oracle12c R1以降のバージョンを前提にしています。
エディションは、Standard Edition2を想定しています。
設定を検討したいOracleの初期化パラメータ
初期化パラメータの詳細については、Oracleデータベースのマニュアルを参照ください。
DB_BLOCK_SIZE
データブロックサイズを指定するパラメータです。
OLTPシステムでは、通常8KBを指定します(基本、このサイズで問題ないと思います)。
DWHなどのシステムでは、8KBより大きな値を指定します。
以下の検証例のように、テーブルのレコード長よりブロックサイズが小さい場合、パフォーマンスが悪化する場合があります。ただし、その場合でも、次に説明する、DB_FILE_MULTIBLOCK_READ_COUNTの設定により、パフォーマンス悪化を軽減する事が可能です。
DB_FILE_MULTIBLOCK_READ_COUNT
全表スキャンまたはインデックス高速全スキャン中に、単一のI/Oで読み取られるデータブロック数を指定します。
この値が大きいほど、全表スキャンのコストが低く見積もられ、オプティマイザがインデックススキャンよりも全表スキャンを選択する可能性があります。
このパラメータのデフォルト値は、Windowsなど殆どのプラットフォームで1MBである最大I/Oサイズを、データブロックサイズで割った値です。(DB_BLOCK_SIZE=8192の場合、1024×1024÷8192=128)
OLTPシステムやバッチ処理環境では、この値には通常4～16の値を割り当てます。
DWHなどでは、全表スキャンが殆どになるため、この値は最大値に設定する方がメリットがあります。
システムの特性やデータサイズなどを加味し、オプティマイザに全表スキャンを選択させたいか、そうでないかにより、設定を検証する必要があります。
DB_KEEP_CACHE_SIZE
メモリチューニングの記事の中で別途説明予定。
PGA_AGGREGATE_LIMIT
PGAのサイズは、PGA_AGGREGATE_TARGEで指定しますが、このサイズは目標値であり、実際にはこのサイズ以上のメモリが使われる事があります。
PGA_AGGREGATE_LIMITは、PGAで使うメモリを厳密に制御する為のパラメータで、指定した値以上のメモリをPGAで確保しようとすると、「ORA-04036」が発生し、セッションが中断されます。
これに対する解決策は、PGA_AGGREGATE_LIMITの値を十分大きくするか、PGA_AGGREGATE_LIMITを無効にする(値に0を指定)ですが、どこまで値を大きくすればよいか見積もる事は困難である事、指定以上のメモリが使われていないか、Oracleがチェックする事によるオーバーヘッドが大きい為、このパラメータには0を指定し、PGA_AGGREGATE_LIMITを無効にする事をお勧めします。
OPTIMIZER_ADAPTIVE_PLANS
適応実行計画機能の有効/無効を指定するパラメータです。
Oracle12cR1までは、OPTIMIZER_ADAPTIVE_FEATURESパラメータで有効/無効を制御していましたが、12.2から、OPTIMIZER_ADAPTIVE_PLANSと後述するOPTIMIZER_ADAPTIVE_STATISTICSとに分離されました。
デフォルト値はTRUEになっていますが、EEでのみ使える機能であるため、SE利用の場合は内部的に機能がOFFになっています(Adaptive Execution Plans – not available in Oracle SE2)。
この機能は、SQL実行時に、統計情報と実際のデータ状況とで乖離がある場合に、実行計画を予め用意されたサブプランに動的に切り替えるものです。
定期的に最新の統計情報を取得しており、統計情報と実データとの乖離が無ければ恩恵も少ない事が予想されますが、オーバーヘッドが比較的少ないので、EEの環境で利用する事を検討してもよいと思います。
※オプティマイザの動作がEEとSE2とで異なる点は意識しておく必要があります。
OPTIMIZER_ADAPTIVE_STATISTICS
適応統計機能の有効/無効を指定するパラメータです。
Oracle12cR1までは、OPTIMIZER_ADAPTIVE_FEATURESパラメータで有効/無効を制御していましたが、12.2から、OPTIMIZER_ADAPTIVE_PLANSとOPTIMIZER_ADAPTIVE_STATISTICSとに分離されました。
デフォルト値はFALSEです。
この機能を有効にすると、自動再最適化、動的統計、SQL計画ディレクティブといった機能が利用できるようになります。
この機能は、オプティマイザ統計が不十分なときに補正してくれる便利な機能ですが、動的統計によりパース時間が長くなったり、列グループ統計の自動作成などによるオーバーヘッドが大きくなるなどして、パフォーマンス悪化の要因になる事があります。
その為、このパラメータは無効にしておく事をお勧めします。
OPTIMIZER_ADAPTIVE_FEATURES
Oracle12cR1まで使われていた、適応実行計画機能および適応統計機能の有効/無効を指定するパラメータで、12cR2から、OPTIMIZER_ADAPTIVE_PLANS、OPTIMIZER_ADAPTIVE_STATISTICSとに分離され、このパラメータは廃止されています。
上述の通り、適応統計機能を有効にする事によるオーバーヘッドが大きい為、12cR1までの環境では、OPTIMIZER_ADAPTIVE_FEATURESの値をFALSEとする事を個人的にはお勧めします。
適応実行計画、適応統計についての詳細は、以下のサイトも確認ください。
OPTIMIZER_FEATURES_ENABLE
このパラメータでは、オプティマイザの動作モードをOracleデータベースのどのバージョンにするかを指定できます。
例えば、Oracle11.2.0.4から19cにアップグレードした際、オプティマイザの動作変更の影響でパフォーマンスが悪化してしまった場合に、このパラメータの値を’11.2.0.4’にする事で、19cであってもオプティマイザは11.2.0.4相当の動作をします。
※アップグレード時にパフォーマンスが悪化してしまった際の暫定対処的な用途で利用を検討するとよいと思います。
このパラメータに指定できる値と指定値により有効/無効になるオプティマイザ機能については、以下を参照してください。
OPTIMIZER_DYNAMIC_SAMPLING
動的統計(過去バージョンでは動的サンプリング)は、SQLを解析する際に、オプティマイザが動的に統計を収集する機能です。
この機能を有効にする事により、長期間統計情報が取られていないなどして、実データと統計情報とに乖離がある場合にも、より実際に近い実行計画が採用される事が期待できます。
しかし、この機能が有効である事により、頻繁に動的統計が取得されてしまうと、かえってパフォーマンスが悪化してしまう場合があります(AWRやStatsPackレポートに、動的統計に関するSQLが多く載っている場合、その可能性があります)。
このパラメータには、0～11(11は12cR1以降、デフォルトは2)の値を指定しますが、環境毎に、どの値を設定すべきかの判断および検証が難しい為、0(動的統計を無効にする)もしくは、11(動的統計を取得するか全てOracleにお任せ)を設定する事がお勧めです。(参考: 動的統計レベル)
OPTIMIZER_INDEX_CACHING
このパラメータには、0～100の値(デフォルトは0)を指定する事ができます。
この値が大きければ大きい程、オプティマイザは、ハッシュまたはソート・マージ結合ではなくネステッド・ループ結合が選択されやすくなり、また、他の索引または全表スキャンではなくINリスト・イテレータを使用する索引が選択されやすくなります。
オプティマイザに対して、細かな指定ができるパラメータではありますが、パッケージソフトなどでは、環境、機能や設定などによりデータ分布もデータ量も異なるため、全てに最適な設定値を見極めるのは非常に難しいです。
参考までに紹介しますが、OLTPシステムでは、このパラメータはデフォルト値の0にしておく事をお勧めします。
OPTIMIZER_INDEX_COST_ADJ
このパラメータでは、オプティマイザがアクセス・パスを選択するときに、索引を使用する傾向の強さを調整できます。
つまり、オプティマイザが全表スキャンよりも索引アクセス・パスを選択する傾向を、強めたり弱めたりすることができます。
デフォルト値は100(%)で、この場合、オプティマイザは索引アクセス・パスを通常コスト(Oracleが内部的に持っているアクセス・パスの通常の優先基準)で評価します。
この値を100より小さくすると、通常よりも全表スキャンが選択されやすくなります。
DWHなどでは値の調整が有効になる場合がありそうですが、OLTPシステムでは、デフォルトの100にしておく事を推奨します。
_OPTIM_PEEK_USER_BINDS
このパラメータは、バインドピーク機能の有効/無効を指定する隠しパラメータです(隠しパラメータなので、通常はOracleサポートの指示を受けた上で変更するものですが、Oracleサポート契約があるユーザー向けには公開されており、サポートもされるものです)。
デフォルトはTRUE(有効)ですが、FALSEを指定する事で、バインドピーク機能を無効にする事が可能です。
古いOracleバージョンからアップグレードした際など、バインド変数を利用しているSQLでパフォーマンス悪化が見られる場合に、切り分けの為に無効化して検証するなどの用途で利用します。
以下、蛇足です。
バインドピークについての詳細説明は、ここではしませんが、バインドピークを有効にすべきか、SQLにバインド変数を使うべきかは、システムの特性により異なる部分もあり、判断が難しいものだと思います。
ただ、以下のような指針を考慮した上で検証を行い決定するのがよいのではないかと思います。
- バインド変数を使用する目的は、同じ構文のSQLの実行計画を1つに統一し、リソース使用効率(特に共有プールのメモリとハードパース削減)を向上すること。
- 1つの実行計画がすべてのバインド値に対して最適となるはずはなく、現実的にはユーザーが使用する「大部分」（例えば90％）の値で良好な性能が得られれば、バインドピーク利用は可とすべき。
- 1つの実行計画が「大部分」の要求を満たせない場合、複数の実行計画を使い分ける必要がある。この場合は、バインドピーク利用有無に関わらず、バインド変数は(あえて)使わず、リテラル値で指定する方が適切。
おわりに
最後に少し脱線しましたが、私が過去にパフォーマンス改善のために検討したパラメータについて紹介してみました。
今回紹介しなかった、メモリチューニング関連やOracleのその他の設定によるパフォーマンス改善策の紹介は、別記事にて書いてみようと思います。