夜間バッチにおけるチューニング

plsql oracle

チューニングを行ったポイントを挙げてみました。

  • SQL実行計画の確認

JOINする際の方法、インデックスの効果、WHERE句の指定方法や順番など実行計画を参照しながらチューニングを行う、、、というのはかなり一般的というか教科書的というか、各開発者が意識して行うと思いますが、集計を行うような性質のSQLだとあまり効果がないケースが多々あります。インデックスとかは関係なくあるテーブルの値を全件取得して、計算して、他のテーブルにインサートするとか。SQLの性質によって対応方法が違うという点に留意しました。

  • DMLのパラレル実行

パラレル実行はEnterpriseEditionのみ実行可能とのことです。それ以外のバージョンでも構文的にはエラーになりませんが、パラレル実行されませんでした。
DMLの実行前に、

EXECUTE IMMEDIATE 'ALTER SESSION FORCE PARALLEL DML';

を実行することによりDMLが並行処理され、スピードアップが図れる。この方法だけでいいんじゃないかと思ってしまう程、SQLによっては処理時間の短縮が可能です。処理時間1/5とか1/10とか。もしEnterprise Editionを使用しているなら試す価値は十分にあります。

関連初期化パラメータ デフォルト値 説明 備考
parallel_threads_per_cpu 2 CPUコア数１毎にいくつ並列処理を行うか 例：４コアのシステムでは８並列でDMLが実行される
parallel_max_servers core数*10 並列処理のために、最大でどのくらいスレッドを生成するか 多すぎる値を指定してもボトルネックになるとか
  • TEMP領域の分散化、データファイルの分散化

OSのリソースモニターのうち、DISKに関する値を観察していると、ボトルネックになっている部分を発見しました。初期設定では、TABLESPACEに配置するデータファイルを複数のDISK装置に分散させていなかったため、酷いボトルネックが発生しておりました。SQLの実行中に、CPU使用率が低い（０〜１０％）場合は、I/Oボトルネックを疑いましょう。OSのリソースモニターで、DISK QUEUE LENGTHという値も観測しましょう。

  • 自動PGA管理を無効にし、ソートエリアサイズの手動設定

集約処理（GROUP BY）、集合演算処理（UNION、MINUS、INTERSECT）、ソート処理（ORDER BY）では、メモリが足りなくなると判断されると、TEMP領域へのアクセスが大量となります。できる限りメモリ上でこれらの処理が行われるように、DML実行前に以下の文を実行します。

EXECUTE IMMEDIATE 'ALTER SESSION SET WORK_AREA_POLICY ＝ MANUAL';
EXECUTE IMMEDIATE 'ALTER SESSION SET SORT_AREA_SIZE ＝ 5000000';
EXECUTE IMMEDIATE 'ALTER SESSION SET HASH_AREA_SIZE ＝ 5000000';

だだし、これを行って劇的に早くなるPL/SQLと、逆に遅くなるPL/SQLとがあるので注意しましょう。また、OSのメモリ残量にも注意です。

  • JP1のジョブ多重度による時間短縮

JP1でジョブネットを作成する際にも、多重度を考慮にいれて実行。多重度を多くすると遅くなるPL/SQL、多くしたほうが直列実行より早くなるPL/SQLなどそれぞれ特性があるので、沢山のパターンを試して、実測値から最適なジョブネットを導くしかありません。