ORA-1555: "Snapshot Too Old" error

Undo data can be divided into 3  parts."Active" undo is undo data that might be needed to roll back transactions in progress in progress. Active undo data can never be overwritten,until the transaction completes.
"Expired" undo is undo data from committed transaction that oracle is no longer obliged to store-though it might be needed for consistency reads,if there are any long running queries in progress.This data can be overwritten if oracle needs the space for another active transaction .
" Unexpired "extents are non-active extents that still honour UNDO_RETENTION. The transactions belonging to these undo extents are committed, but the retention time has not passed: You still want/need these for Read Consistency! When the Undo mechanism requires more extents for ACTIVE extents, it is allowed to steal UNEXPIRED extents when there are no EXPIRED extents left for reuse and it can not allocate more free extents (autoextend maxsize reached or fixed tablespace size). One can check the steal-count in UNXPSTEALCNT in V$UNDOSTAT. You will get ‘ORA-01555 snapshot too old‘ errors if no Read Consistency information for a query is available. The SSOLDERRCNT in V$UNDOSTAT will show a count of these errors.

The undo tablespace follows two simple principles:

#1.There should always be sufficient undo space to allow all transactions to continue.It requires that your undo tablespace be large enough to accommodate the worst case for undo demand.It should have enough space allocated for the worst case--The peak usage of active undo data generated by your transaction workload.

#2.There should always be sufficient undo data for all queries in progress to succeed.It requires that there be additional space in the undo tablespace to store the expired undo data that might be needed for the read consistency,so that long running queries will not fail with Oracle error ora-1555.

If a query fails on consistent read with "Snapshot too old", it means that the query hit a block that had been changed since the query started,but when it went to the undo segment to find the pre-updated version of the data,that bit of undo data had been overwritten (Only expired undo can be overwritten.Oracle can't overwrite Active undo data).

If the transaction runs out of undo space,it will fail with the error ORA-30036,unable to extend segments in undo tablespace.The statement that hits the problem is rolled back ,but rest of the transaction remains intact and uncommitted.The algorithm that assigns space within the undo tablespace to undo segments means that this error condition will arise only if the undo tablspace is absolutely full on unexpired undo data(so cannot be overwritten).

Undo tablespace and retension recommendation script

SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

DECLARE
    v_analyse_start_time    DATE := SYSDATE - 7;
    v_analyse_end_time      DATE := SYSDATE;
    v_cur_dt                DATE;
    v_undo_info_ret         BOOLEAN;
    v_cur_undo_mb           NUMBER;
    v_undo_tbs_name         VARCHAR2(100);
    v_undo_tbs_size         NUMBER;
    v_undo_autoext          BOOLEAN;
    v_undo_retention        NUMBER(5);
    v_undo_guarantee        BOOLEAN;
    v_instance_number       NUMBER;
    v_undo_advisor_advice   VARCHAR2(100);
    v_undo_health_ret       NUMBER;
    v_problem               VARCHAR2(1000);
    v_recommendation        VARCHAR2(1000);
    v_rationale             VARCHAR2(1000);
    v_retention             NUMBER;
    v_utbsize               NUMBER;
    v_best_retention        NUMBER;
    v_longest_query         NUMBER;
    v_required_retention    NUMBER;
BEGIN
    select sysdate into v_cur_dt from dual;
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
    DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

    v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
    select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;

    DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
    DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
    DBMS_OUTPUT.PUT_LINE('End Time   : ' || v_analyse_end_time);
 
    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
    DBMS_OUTPUT.PUT_LINE('--------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
    IF V_UNDO_AUTOEXT THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
    ELSE
        DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
    END IF;
    DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);

    IF v_undo_guarantee THEN
        DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
    ELSE
        dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
    END IF;
    DBMS_OUTPUT.PUT_LINE(CHR(9));

    SELECT instance_number INTO v_instance_number FROM V$INSTANCE;

    DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
    DBMS_OUTPUT.PUT_LINE('---------------------------');

    v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
    DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
    DBMS_OUTPUT.PUT_LINE('-------------------------');

    v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
    IF v_undo_health_ret > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Minimum Recommendation           : ' || v_recommendation);
        DBMS_OUTPUT.PUT_LINE('Rationale                        : ' || v_rationale);
        DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
    END IF;
 
    SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
    SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
    SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;

    DBMS_OUTPUT.PUT_LINE(CHR(9));
    DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
    DBMS_OUTPUT.PUT_LINE('------------------------');
    DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
    DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');

END;
/


Sample output:

- Undo Analysis started at : 10/10/2014 10:35:45 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 03/10/2014 10:35:45
End Time   : 10/10/2014 10:35:45

Current Undo Configuration
--------------------------
Current undo tablespace                                 : UNDOTBS1
Current undo tablespace size (datafile size now)        : 3072M
Current undo tablespace size (consider autoextend)      : 32768M
AUTOEXTEND for undo tablespace is                       : ON
Current undo retention                                  : 2700
UNDO GUARANTEE is set to                                : FALSE

Undo Advisor Summary
---------------------------
Finding 1:The undo tablespace is OK.

Undo Space Recommendation
-------------------------
Allocated undo space is sufficient for the current workload.

Retention Recommendation
------------------------
The best possible retention with current configuration is    : 114475 Seconds
The longest running query ran for                            : 5211 Seconds
The undo retention required to avoid errors is               : 5211 Seconds

PL/SQL procedure successfully completed.


select a.tablespace_name,b.total_space,a.used_space, (b.total_space - a.used_space) Free_Space, round((a.used_space/b.total_space)*100,2) PCT_USED,round(((b.total_space - a.used_space)/b.total_space)*100,2) PCT_FREE from
(select tablespace_name,sum(BYTES/1024/1024/1024) used_space from dba_undo_extents where status in ('UNEXPIRED','ACTIVE') group by tablespace_name) a,
(SELECT   tablespace_name, SUM (size_to_grow)/1024/1024/1024 total_space
          FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow
                    FROM dba_data_files
                   WHERE tablespace_name in (select UPPER(VALUE) from gv$parameter where name='undo_tablespace') and autoextensible = 'YES'
                GROUP BY tablespace_name
                UNION
                SELECT   tablespace_name, SUM (BYTES) size_to_grow
                    FROM dba_data_files
                   WHERE tablespace_name in (select UPPER(VALUE) from gv$parameter where name='undo_tablespace') and autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name) b where a.tablespace_name=b.tablespace_name

Calculate Needed UNDO Size for given Database Activity
-----------------------------------------------------------------------------------------

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       (TO_NUMBER(e.value) * TO_NUMBER(f.value) *

       g.undo_block_per_sec) / (1024*1024)

      "NEEDED UNDO SIZE [MByte]"

  FROM (

       SELECT SUM(a.bytes) undo_size

         FROM v$datafile a,

              v$tablespace b,

              dba_tablespaces c

        WHERE c.contents = 'UNDO'

          AND c.status = 'ONLINE'

          AND b.name = c.tablespace_name

          AND a.ts# = b.ts#

       ) d,

      v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

         undo_block_per_sec

         FROM v$undostat

       ) g

 WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size'

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec]      NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
                   11590 900                                     1186.40625

Optimal Undo Retention

------------------------------

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",

       SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",

       ROUND((d.undo_size / (to_number(f.value) *

       g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"

  FROM (

       SELECT SUM(a.bytes) undo_size

          FROM v$datafile a,

               v$tablespace b,

               dba_tablespaces c

         WHERE c.contents = 'UNDO'

           AND c.status = 'ONLINE'

           AND b.name = c.tablespace_name

           AND a.ts# = b.ts#

       ) d,

       v$parameter e,

       v$parameter f,

       (

       SELECT MAX(undoblks/((end_time-begin_time)*3600*24))

              undo_block_per_sec

         FROM v$undostat

       ) g

WHERE e.name = 'undo_retention'

  AND f.name = 'db_block_size'




AUM Common Analysis/Diagnostic Scripts (Doc ID 877613.1) Script - Check Current Undo Configuration and Advise Recommended Setup (Doc ID 1579035.1)