【PL/SQL】パフォーマンス改善のためのヒントと注意点

【PL/SQL】パフォーマンス改善のためのヒントと注意点 PL/SQL

PL/SQLの性能はSQLの品質・I/O・コンテキストスイッチ・データ量・ロジック構造に大きく左右されます。安易なチューニングよりも「正しい設計と測定→ボトルネック特定→最小変更」の順序が重要です。ここでは実務で効くパフォーマンス改善の勘所と落とし穴、具体例を整理します。

まずは測定：どこが遅いかを特定する

闇雲な最適化は逆効果です。タイマーと統計を使って遅い箇所を切り分けます。タイミングはDBMS_UTILITY.GET_TIME（1/100秒）、SYSTIMESTAMPなどを利用し、セッション統計（V$SESSTAT）やAUTOTRACE/SQLトレース（TKPROF）でI/Oや実行計画を確認します。

DECLARE
  t0 PLS_INTEGER := DBMS_UTILITY.get_time;
BEGIN
  -- 対象処理
  NULL;
  DBMS_OUTPUT.put_line('elapsed='||(DBMS_UTILITY.get_time - t0)||' cs'); -- centisecond
END;
/

遅いのが「SQLそのもの」か「PL/SQLループ」か、「ネットワーク/COMMIT」かを切り分けるのが第一歩です。

SQLをファーストクラスで扱う：集合志向へ寄せる

多くのボトルネックは「行ごとの処理（row-by-row）」です。可能な限り1回のSQLでまとめます。MERGEでUPSERT、集計はGROUP BY、存在確認はEXISTS、条件付更新はCASEを活用します。

-- 悪い例（行ごとにUPDATE）
FOR r IN (SELECT id, val FROM t_src) LOOP
  UPDATE t_dst SET val = r.val WHERE id = r.id;
END LOOP;

-- 良い例（集合更新）
MERGE INTO t_dst d
USING (SELECT id, val FROM t_src) s
ON (d.id = s.id)
WHEN MATCHED THEN UPDATE SET d.val = s.val
WHEN NOT MATCHED THEN INSERT (id, val) VALUES (s.id, s.val);

PL/SQLとSQLのコンテキストスイッチを減らす（BULK処理）

大量行の取得・更新はBULK COLLECTとFORALLで往復回数を削減します。LIMITでメモリ消費を抑えつつバッチ処理します。

DECLARE
  TYPE t_ids IS TABLE OF t_src.id%TYPE;
  v_ids  t_ids;
BEGIN
  LOOP
    v_ids := t_ids();  -- 再初期化
    SELECT id BULK COLLECT INTO v_ids FROM t_src WHERE processed = 'N' FETCH FIRST 1000 ROWS ONLY;
    EXIT WHEN v_ids.COUNT = 0;
    FORALL i IN 1..v_ids.COUNT
      UPDATE t_src SET processed = 'Y' WHERE id = v_ids(i);
    COMMIT; -- コミット頻度は要検討
  END LOOP;
END;
/

BULK収集時のメモリ使用に注意し、必要列のみ取得します。RETURNING BULK COLLECTで往復をさらに減らせます。

バインドとパース削減：ハードパースを避ける

文字列連結で動的SQLを都度生成すると、共有プールを汚しハードパースを招きます。バインド変数を使用し、EXECUTE IMMEDIATEでもUSING句を活用します。

EXECUTE IMMEDIATE 'UPDATE t SET val = :1 WHERE id = :2' USING v_val, v_id;

カーソルのリユース（セッションキャッシュ）も効果的です。

インデックスと実行計画：アクセスパスを整える

WHERE句、JOINキー、ORDER BY/ GROUP BYに合わせ適切なインデックスを設計します。選択度が低い列の単独インデックスは効果が薄いことがあります。結合順序・結合手法（HASH/MERGE/NESTED LOOPS）は統計の鮮度に依存するため、統計収集（DBMS_STATS）を定期実行します。ヒントは最後の手段とし、原因（統計・スキュー）を正すのが基本です。

不要なI/Oを減らす：列絞り込みとログ/UNDOの最小化

SELECT * を避け、必要列のみ取得します。大量更新は無駄なトリガーや不要なインデックスを見直し、可能ならパーティション分割や直列処理のバッチ化を検討します。ロギング量が多い場合はNOLOGGING（再作成可能な一時領域に限る）やAPPENDなどを検討します（復旧戦略とのトレードオフに注意）。

コミット頻度：過剰コミットは遅い

トランザクション中の頻繁なCOMMITはLGWRの同期を増やし遅くなります。適切なバッチサイズでまとめてコミットし、失敗時のロールバック量とのバランスを取ります。コミットは性能最適化ではなく整合性設計の一部として決めます。

条件分岐の粒度：短絡評価とCASEの活用

IFのネストを浅くし、CASE式でSQL側に寄せると最適化されやすくなります。

UPDATE t
   SET val = CASE WHEN flag = 'A' THEN val1
                  WHEN flag = 'B' THEN val2
                  ELSE val3 END
 WHERE ...;

カーソルとコレクションの設計：メモリとガベージ

REF CURSORやコレクションはスコープを短く保ち、使い切ったら明示的に初期化・クローズします。巨大なPL/SQLテーブルは段階的に処理し、PGAの枯渇を防ぎます。BULK COLLECTはLIMITとの併用で分割取得します。

トリガーとファンクションの副作用に注意

行トリガー内での複雑なSQLや外部呼び出しは隠れたコスト源です。監査・整合性以外のロジックはアプリ層やバッチに寄せることを検討します。PL/SQLファンクションをSQL内で呼ぶと行ごとにコンテキストスイッチが発生するため、高頻度の呼び出しはインラインSQLやWITH句で代替します。

パーティションと並列：データ量に応じた構成

大規模テーブルはレンジ/リスト/ハッシュ等でパーティション化し、対象範囲だけをスキャンします。並列DML/並列クエリはI/O帯域とCPU余力があるときに有効ですが、ログ/UNDO増大やロック粒度に注意し、適切なDOP（Degree of Parallelism）を選びます。

例外処理：コストと可観測性

例外は高頻度で発生すると遅くなります。正常系の判定に例外を使わず、事前にEXISTSやCOUNTで分岐します。ログにはエラーコードとバインド値、所要時間を記録し、再現性を高めます。

BEGIN
  -- 正常系
EXCEPTION
  WHEN OTHERS THEN
    DBMS_APPLICATION_INFO.set_module('pkg_job','step1');
    -- ログ出力・再スロー方針を統一
    RAISE;
END;

並行性とロック：待ち時間を削る

ホット行の更新はロック競合の原因です。更新順序を固定し、バッチ時間帯を分散し、適切なインデックスで更新対象を絞ります。待ちが多い場合はV$SESSION/V$LOCKで原因を特定し、アプリ側の再試行戦略（指数バックオフ）を導入します。

動的SQLの取り扱い：最小限・ホワイトリスト

EXECUTE IMMEDIATEは柔軟ですが、パース負荷とリスクが上がります。ステートメントはテンプレート化し、バインドを必須に。DDLを動的に実行する処理は権限と依存の管理を厳格に行います。

ベンチマークと回帰防止：小さく計測して積み上げる

代表データでユニットベンチを作り、ベースラインを保存します。変更時は前後比較（所要時間、論理読み取り、物理読み取り、CPU）を取り、リグレッションをCIで検出します。テストデータは選択度・スキューを現実に近づけることが重要です。

チェックリスト（要点の総括）

1) 測定して原因を特定する（SQLかPL/SQLかI/Oか） 2) 集合志向SQLに寄せる 3) BULK COLLECT + FORALLで往復削減 4) バインドでパース削減 5) インデックスと統計の整備 6) 列を絞ってI/O削減 7) コミットは計画的に 8) ファンクション呼び出しをSQL内で濫用しない 9) パーティション・並列を適材適所で 10) 例外は例外として扱いログを整える

まとめ

PL/SQLの性能改善は「集合志向」「往復削減」「I/O最小化」「適切な統計・索引」「観測性向上」の積み重ねです。ボトルネックを測定で絞り込み、最少の変更で最大の効果を狙い、回帰を防ぐベンチとログを仕組みに組み込めば、安定して速いPL/SQLを保てます。