ORA-01555: snapshot too old error when deleting with transactions

Question

I hope someone can explain me why I'm seeing ORA-01555. I have a function and a procedure to perform a cleanup in a huge table:

-- Small cleanup in separate transaction.
FUNCTION clean_single(ts_until IN TIMESTAMP, datapoint_id IN NUMBER) RETURN NUMBER IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    DELETE FROM VALUES WHERE DATAPOINT_ID = datapoint_id AND TS < ts_until;
    COMMIT;
    RETURN sql%rowcount;
END clean_single;

PROCEDURE prc_clean IS
    count_all_deleted_vals NUMBER(10) := 0;
BEGIN
    BEGIN
        FOR dps IN (
            SELECT x AS dpid, y as tsUntil FROM Z where some conditions
            LOOP
                count_all_deleted_vals := count_all_deleted_vals + clean_single(dps.tsUntil, dps.dpid);
            END LOOP;
        DBMS_OUTPUT.PUT_LINE('Removed ' || count_all_deleted_vals || ' values');
    END;
END prc_clean;

The idea is to run prc_clean() from a job and after the relevant datapoint ids are selected, the deletion per datapoint-id is done in a single transaction to avoid having one huge transaction.

But when I run this it runs for a while and then fails with ORA-01555. In detail i do not understand why this is happening. Why does the PRAGMA AUTONOMOUS_TRANSACTION; in the function not prevent this? What can I do to prevent it?

Answer 1

The ORA-01555 comes most likely from the SELECT in the FOR.

Even though one DELETE is a separate transaction and it may finish fairly quickly, the cursor for the SELECT must be held open for the whole execution and it always has to return results from the point of time it started. If Z is modified while this is running, the undo necessary to reconstruct Z as of the time when execution was started, may be overwritten => ORA-01555.

Writing such a loop is a very common mistake.

A much better approach is to store the results of SELECT x AS dpid, y as tsUntil FROM Z where some conditions in a PL/SQL collection, then iterate through that collection in a loop, instead of iterating through a query in a loop.

Answer 2

Every Oracle Transaction is executed as of the point it time that it started.

To achieve this, Oracle effectively "flashes back" every Data Block used within that Transaction to that initial SCN. To perform these "On-the-fly Flashbacks", Oracle needs room in the Undo Tablespace. As soon as it finds it can't get a Data Block "back" far enough, you get the ORA-01555.

Unless datapoint_id is unique, then the delete will have to scan multiple rows (performing the "On-the-fly Flashback" on every single one of them) thereby multiplying up the work that Oracle has to do and increasing the risk of "running out" of Undo to support it.

Whilst it comes with its own Risks (i.e. Row Movement), one alternative might be to identify the ROWID of each row to be deleted and pass that to the clean_single function. That way, the delete would only consider that one row.