大量データを一括処理するPL/SQLでは、コミット頻度の設計とUNDOの最適化が性能と安定性の成否を分けます。コミットを細かく刻みすぎればオーバーヘッドと断片化が増え、粗すぎればUNDO不足やロールバック時のダメージが大きくなります。ここでは「どのくらいの間隔で確定すべきか」「UNDOをどう節約し確保するか」を、集合志向SQL・FORALL・DIRECT PATH・ERRLOG・並列DML・パーティション運用などと絡めて実務視点で整理します。
コミット頻度の原則とトレードオフ
コミットはチェックポイントやログ書き出しを誘発するため頻度を上げるほど遅くなりますが、障害時に巻き戻す量とUNDO保持量は小さくできます。よって再実行戦略とUNDO容量のバランスで「チャンク」を決めるのが要点です。目安としては行数やバイト量、経過時間のいずれかで閾値を設け、一定量処理→検証→COMMITというサイクルを回します。外部副作用（メール送信やAPI呼び出し）が絡む場合はトランザクション・アウトボックス方式で分離し、DB側はデータ確定のみに集中させると安定します。
チャンク設計の実装テンプレート
行数ベースの確定はシンプルで、多くの現場に向きます。UNDOやREDOの伸びを見ながら段階的に閾値を調整します。
DECLARE
c_commit_every CONSTANT PLS_INTEGER := 50000; -- 目安。環境に応じて調整
v_done PLS_INTEGER := 0;
BEGIN
FOR r IN (
SELECT /*+ CARDINALITY(t 5000000) */ id, val
FROM staging t
WHERE processed = 'N'
ORDER BY id
) LOOP
-- 集合志向が原則。行単位が不可避な前処理のみPL/SQLで行う
UPDATE target SET col = r.val WHERE id = r.id;
v_done := v_done + 1;
IF MOD(v_done, c_commit_every) = 0 THEN
COMMIT;
-- モニタリングや進捗ログは自治トランザクションに逃がすのが安全
END IF;
END LOOP;
COMMIT;
END;
/
経過時間ベースにする場合はDBMS_UTILITY.GET_TIMEやSYSTIMESTAMPで測り、例えば三分ごとに確定する実装へ置き換えます。再実行可能性を高めるため、入力にロードIDやキー範囲を持たせ、途中失敗時は未処理範囲だけを再開できるようにしておきます。
集合志向＋ERRLOGで「成功は確定・失敗は退避」に寄せる
FORALLや行ループより、可能な限り一文のINSERT/UPDATE/MERGEでまとめるとUNDO/REDOの効率が上がります。制約や型変換で落ちる行はDBMS_ERRLOG＋LOG ERRORS句で退避し、成功分のみ確定する構成が堅牢です。チャンクはキー範囲や期間で自然に区切り、各チャンク末尾でCOMMITします。
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('TARGET'); -- 初回のみ
INSERT /*+ APPEND */ INTO target(cols...)
SELECT ... FROM staging WHERE load_id = :load_id
LOG ERRORS INTO err$_target('LOAD_20251004') REJECT LIMIT UNLIMITED;
COMMIT;
END;
/
FORALL×SAVE EXCEPTIONSでPL/SQL側のUNDOを抑える
行前処理が避けられずPL/SQLが必要な場合はFORALLでバインド送信をまとめ、SAVE EXCEPTIONSでエラー行のみ後処理します。BULK COLLECTはLIMITで小分けにし、PGAとUNDO双方の圧迫を避けます。
DECLARE
TYPE t_row IS RECORD(id NUMBER, v VARCHAR2(100));
TYPE t_tab IS TABLE OF t_row;
v_rows t_tab;
BEGIN
LOOP
SELECT id, v BULK COLLECT INTO v_rows
FROM staging WHERE processed='N'
FETCH FIRST 10000 ROWS ONLY;
EXIT WHEN v_rows.COUNT = 0;
BEGIN
FORALL i IN 1..v_rows.COUNT SAVE EXCEPTIONS
UPDATE target SET col = v_rows(i).v WHERE id = v_rows(i).id;
EXCEPTION
WHEN OTHERS THEN
NULL; -- SQL%BULK_EXCEPTIONSでログ化（省略）
END;
COMMIT; -- チャンク確定
END LOOP;
END;
/
DIRECT PATHとNOLOGGINGの位置づけ
INSERT主体のロードではDIRECT PATH（APPENDヒント）を使うとUNDOとREDOの生成を相対的に抑え、セグメント高水位の後ろ側へ連続書き込みできます。ターゲットをNOLOGGINGにしていると障害時復旧やData Guardスタンバイへ反映できない点に注意が必要で、本番では一時的NOLOGGING→ロード→必要に応じてバックアップや同期ののちLOGGINGへ戻す、といった手順設計が求められます。UPDATE/DELETEはDIRECT PATH対象外のため、INSERT…SELECTで別表へ作り直してEXCHANGE PARTITIONで入れ替えるとUNDOを大幅に節約できます。
並列DMLとコミットの相性
ENABLE PARALLEL DMLと表・索引のPARALLEL指定、SQLにPARALLELヒントを併用するとスループットが伸びますが、トランザクション終端まで結果が見えない点と、頻繁なコミットで並列ワーカーの再起動が発生する点を踏まえ、チャンク内は可能な限り単一コミットに留めます。
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ APPEND PARALLEL(t 8) */ INTO target t
SELECT /*+ PARALLEL(s 8) */ * FROM staging s WHERE load_id=:id;
COMMIT;
UNDO最適化の実務論点
UNDOは「読み取り一貫性」と「ロールバック」に使われます。保持不足はORA-01555（スナップショットが古い）やORA-30036（UNDO拡張不可）に直結します。まずはUNDO表領域のサイズとUNDO_RETENTIONを、想定最大のロングランクエリやロード時間に見合う値へ設定します。大きくしすぎればディスクを圧迫し、小さすぎれば長時間SELECTとの干渉で01555が出やすくなります。長尺DML中に参照系が同じブロックを読み続けると競合しやすいため、夜間バッチやパーティション単位の隔離で時間・データの干渉を減らすのが効果的です。更新主体では局所更新よりも「作り直して入れ替える」構成を選び、パーティションEXCHANGEやCTAS（CREATE TABLE AS SELECT）でUNDOとロック競合を根本から避けます。
パーティション運用でUNDOとコミットを小さく保つ
対象期間が明確なら表をレンジ分割し、パーティション限定DMLで処理範囲を狭めます。古いデータはEXCHANGEでアーカイブ表へ瞬時に退避し、TRUNCATE PARTITIONでUNDOをほとんど使わずに削除できます。追加は月次パーティションを先行作成しておき、月単位でチャンク確定するだけで運用が安定します。
インデックスと制約の扱いで無駄なUNDOを抑える
大量INSERT時に多数のセカンダリインデックスを維持するとUNDO/REDOが増えます。許されるならロード前に不要な索引を無効化または削除し、ロード後に並列で再作成します。チェック制約やトリガは実行コストとUNDO生成を増やすため、業務要件と整合が取れる範囲で一時的に無効化し、整合チェックはERRLOGや検証SQLで事後検証に切り替える設計が現実解です。
コミット書き出しポリシーと待ち時間の調整
COMMIT WRITE句でIMMEDIATE/NOWAITやBATCHを調整するとログ書き出し待ちの挙動を変えられます。遅延許容のバルク処理ではBATCH NOWAITが有利になることがありますが、障害時の可視化タイミングや基盤ポリシーとの整合を必ず確認します。
COMMIT WRITE BATCH NOWAIT; -- セッション単位の既定を変更
再実行と冪等性を前提にしたコミット設計
チャンク確定は「いつ切っても同じ最終状態に収束する」冪等性が前提です。UPSERTは外部キーではなく自然キーや外部リクエストIDの一意制約に乗せ、途中失敗時は未処理チャンクだけを再実行します。ログは自治トランザクションで確定し、失敗点を確実に特定できる情報（キー、範囲、バッチID、件数）を必ず残します。
総合テンプレート：ロード、検証、確定の三段構え
現実的な大量処理は、ステージングへ一括取り込み、ERRLOG付きの集合志向DMLで本表へ反映、パーティションまたはキー範囲ごとに確定、の三段で構成します。
-- 1) CTASや外部表でステージングへ（必要ならAPPEND/NOLOGGING）
CREATE TABLE stg_sales NOLOGGING AS SELECT * FROM ext_sales;
-- 2) 期間チャンクで本表へ反映（ERRLOG＋DIRECT PATH）
ALTER SESSION ENABLE PARALLEL DML;
BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('SALES');
FOR r IN (
SELECT ADD_MONTHS(TRUNC(:from,'MM'), LEVEL-1) as m_from,
ADD_MONTHS(TRUNC(:from,'MM'), LEVEL) as m_to
FROM dual CONNECT BY LEVEL <= MONTHS_BETWEEN(:to, :from)
) LOOP
INSERT /*+ APPEND PARALLEL(8) */ INTO sales PARTITION FOR (r.m_from)
SELECT /*+ PARALLEL(8) */ * FROM stg_sales
WHERE sale_dt >= r.m_from AND sale_dt < r.m_to
LOG ERRORS INTO err$_sales(TO_CHAR(r.m_from,'YYYYMM')) REJECT LIMIT UNLIMITED;
COMMIT;
END LOOP;
END;
/
-- 3) 統計更新や索引再作成などの後処理を必要最小で実施
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(USER, 'SALES', partname => 'P202510');
END;
/
まとめ
大量データの一括処理では、コミット頻度は「UNDO容量」「再実行計画」「外部副作用の分離」を軸にチャンクで決め、処理そのものはできる限り集合志向で行うのが王道です。INSERTはDIRECT PATHとパーティション設計でUNDOを抑え、UPDATE/DELETEは作り直し＋EXCHANGEで置換する選択肢を常に検討します。ERRLOGで失敗行を逃がして成功を先に確定し、並列DMLと索引・制約の戦略でスループットを底上げします。UNDOは容量と保持時間を業務実行時間に合わせ、参照系との干渉を避ける計画を添えると、安定性と速度の両立が実現します。
