Error ORA-01555: snapshot too old terjadi saat query atau cursor membutuhkan citra data masa lalu (read consistency) dari segmen undo, namun blok data tersebut telah ditimpa (overwritten) oleh transaksi lain atau oleh proses itu sendiri. Pada backend batch processing, penyebab paling umum bukanlah kekurangan alokasi storage, melainkan implementasi kode yang mengeksekusi COMMIT di dalam loop kursor (fetch-across-commit).

Gejala pada Log Aplikasi Backend

Batch job yang memproses jutaan baris data tiba-tiba berhenti setelah berjalan beberapa puluh menit atau hitungan jam. Log aplikasi biasanya mencatat stack trace berikut:

java.sql.SQLException: ORA-01555: snapshot too old: rollback segment number 14 with name "_SYSSMU14_3829104821$" too small
ORA-06512: at "PAYMENT_SVC.PROCESS_INVOICES", line 42
ORA-06512: at line 1

Karakteristik insiden ini adalah crash yang konsisten terjadi di tengah pemrosesan, bukan di awal eksekusi. Waktu kegagalan berkorelasi langsung dengan volume data yang diproses dan tingkat konkurensi database saat batch berjalan.

Mekanisme Read-Consistency dan Masalah Fetch-Across-Commit

Oracle menerapkan statement-level read consistency menggunakan System Change Number (SCN). Ketika sebuah kursor dibuka, Oracle mencatat SCN awal (SCN0). Kursor harus mengembalikan data persis seperti keadaannya pada SCN0.

Jika transaksi lain memodifikasi dan melakukan commit pada blok tabel yang belum sempat dibaca oleh kursor, Oracle membaca segmen undo untuk merekonstruksi citra blok sebelum modifikasi terjadi (Consistent Read / CR block). Masalah timbul ketika batch loop melakukan update lalu memanggil COMMIT pada setiap iterasi kursor:

  • Kursor induk dibuka pada SCN0 dan membaca data secara bertahap (fetch).
  • Di dalam loop, perintah DML dijalankan dan diakhiri dengan COMMIT.
  • Perintah COMMIT mengubah status blok undo dari active menjadi unexpired atau inactive.
  • Siklus transaksi berikutnya dalam sesi yang sama atau transaksi konkuren dari sesi lain membutuhkan ruang undo. Karena status undo sudah tidak active, Oracle menimpa blok undo tersebut jika ruang bebas menipis.
  • Ketika kursor melanjutkan fetch ke blok data yang telah berubah, Oracle mencari citra blok pada SCN0 di segmen undo. Karena blok undo telah ditimpa, rekonstruksi CR block gagal dan database melempar exception ORA-01555.

Investigasi Root Cause Melalui V$UNDOSTAT

Untuk memverifikasi apakah kegagalan disebabkan oleh retensi undo yang terlalu pendek atau beban kursor yang berjalan melebihi kapasitas retensi, query view sistem V$UNDOSTAT:

SELECT 
    BEGIN_TIME,
    END_TIME,
    TUNED_UNDORETENTION,
    MAXQUERYLEN,
    SSOLDERRCNT,
    NOSPACERRCNT
FROM 
    V$UNDOSTAT
WHERE 
    SSOLDERRCNT > 0
ORDER BY 
    BEGIN_TIME DESC;

Evaluasi metrik berikut:

  • SSOLDERRCNT: Menunjukkan jumlah insiden ORA-01555 pada interval 10 menit tersebut. Nilai di atas 0 menandakan terjadi snapshot too old.
  • MAXQUERYLEN: Durasi kueri terpanjang dalam detik. Bandingkan nilai ini dengan TUNED_UNDORETENTION.
  • Jika MAXQUERYLEN jauh melampaui TUNED_UNDORETENTION, kursor berjalan lebih lama daripada kemampuan database mempertahankan data undo.
  • Jika NOSPACERRCNT > 0, tablespace undo kehabisan ruang bebas untuk alokasi baru.

Refactor Kode PL/SQL: Menghilangkan Fetch-Across-Commit

Pola anti-pattern berbasis kursor baris-demi-baris dengan commit loop harus diubah menjadi pemrosesan batch terstruktur menggunakan BULK COLLECT dengan klausa LIMIT dan FORALL.

Kode Sebelum Refactor (Penyebab ORA-01555)

CREATE OR REPLACE PROCEDURE process_invoices_bad IS
    CURSOR cur_inv IS
        SELECT id, amount FROM invoices WHERE status = 'PENDING';
BEGIN
    FOR r IN cur_inv LOOP
        UPDATE invoices 
        SET status = 'PROCESSED', processed_at = SYSDATE 
        WHERE id = r.id;
        
        -- Anti-pattern: Commit di dalam cursor fetch loop
        COMMIT;
    END LOOP;
END;

Kode Sesudah Refactor (Bulk Batching)

CREATE OR REPLACE PROCEDURE process_invoices_fixed (p_batch_size IN PLS_INTEGER DEFAULT 5000) IS
    CURSOR cur_inv IS
        SELECT id FROM invoices WHERE status = 'PENDING';
        
    TYPE t_id_list IS TABLE OF invoices.id%TYPE;
    v_ids t_id_list;
BEGIN
    OPEN cur_inv;
    LOOP
        FETCH cur_inv BULK COLLECT INTO v_ids LIMIT p_batch_size;
        EXIT WHEN v_ids.COUNT = 0;
        
        FORALL i IN 1..v_ids.COUNT
            UPDATE invoices
            SET status = 'PROCESSED', processed_at = SYSDATE
            WHERE id = v_ids(i);
            
        COMMIT; -- Commit per batch aman karena cursor read-consistency diminimalkan durasinya
    END LOOP;
    CLOSE cur_inv;
EXCEPTION
    WHEN OTHERS THEN
        IF cur_inv%ISOPEN THEN
            CLOSE cur_inv;
        END IF;
        ROLLBACK;
        RAISE;
END;

Jika memungkinkan secara logika bisnis dan ukuran data tidak melebihi jutaan baris per eksekusi, transformasi set-based SQL langsung (satu perintah UPDATE ... WHERE tanpa kursor) adalah solusi paling efisien.

Konfigurasi Database: Optimasi Undo Tablespace

Selain perbaikan kode, pastikan parameter instans database dikonfigurasi untuk menangani query berdurasi panjang.

1. Sesuaikan Nilai UNDO_RETENTION

Atur parameter UNDO_RETENTION (dalam satuan detik) agar lebih besar dari estimasi durasi eksekusi query terpanjang pada sistem:

ALTER SYSTEM SET UNDO_RETENTION = 14400 SCOPE=BOTH; -- 4 Jam

2. Terapkan RETENTION GUARANTEE

Secara default, jika ruang undo penuh, Oracle dapat menimpa blok undo yang berstatus unexpired sebelum durasi UNDO_RETENTION tercapai. Untuk mencegah perilaku ini pada sistem analitik atau batch kritis, aktifkan opsi RETENTION GUARANTEE pada tablespace undo:

ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
Trade-off: Mengaktifkan RETENTION GUARANTEE memastikan integritas retensi undo agar query terhindar dari ORA-01555. Namun, jika ruang disk tablespace undo habis dan transaksi DML baru membutuhkan alokasi, transaksi DML tersebut akan gagal dengan error ORA-01650: unable to extend rollback segment. Pastikan sizing datafile undo tablespace memadai (aktifkan AUTOEXTEND dengan batas maksimum yang aman).