Resolving Snapshot too old error of Undo Tablespace

The following query based on the V$UNDOSTAT view shows how Oracle automatically tunes undo
retention (check the TUNED_UNDORETENTION column) based on the length of the longest-running query (MAXQUERYLEN column) in the current instance workload.

select to_char(begin_time,'hh24:mi:ss') BEGIN_TIME,
 to_char(end_time,'hh24:mi:ss') END_TIME,
maxquerylen,nospaceerrcnt,tuned_undoretention
from v$undostat;

Note that the value of the TUNED_UNDORETENTION column fluctuates continuously, based on the value of the maximum query length (MAXQUERYLEN) during any interval. You can see that the two columns are directly related to each other, with Oracle raising or lowering the tuned undo retention based on the maximum query length during a given interval (of ten minutes). The following query shows the usage of undo blocks and the transaction count during each ten-minute interval.

SQL> selectto_char(begin_time,'hh24:mi:ss'),to_char(end_time,'hh24:mi:ss'),
 maxquerylen,ssolderrcnt,nospaceerrcnt,undoblks,txncount from v$undostat
order by undoblks

Oracle provides an easy way to help set the size of the undo tablespace as well as the undo retention period, through the OEM Undo Advisor interface. You can specify the length of time for the advisor’s analysis, for a period going back to a week—the advisor uses the AWR hourly snapshots to perform its analysis. You can specify the undo retention period to support a flashback transaction query. Alternatively, you can let the database determine the desired undo retention based on the longest query in the analysis period.

Finding What’s Consuming the Most Undo

SQL> select s.sql_text from v$sql s, v$undostat u
where u.maxqueryid=s.sql_id;

You can join the V$TRANSACTION and the V$SESSION views to find out the most undo used by a session for a currently executing transaction, as shown here:

SQL> select s.sid, s.username, t.used_urec, t.used_ublk
from v$session s, v$transaction t
where s.saddr = t.ses_addr
order by t.used_ublk desc;

You can also issue the following query to find out which session is currently using the most undo in
an instance:

SQL>select s.sid, t.name, s.value
from v$sesstat s, v$statname t
where s.statistic# = t.statistic#
and t.name = 'undo change vector size'
order by s.value desc;

The query’s output relies on the statistic undo change vector size in the V$STATNAME view, to show the SID for the sessions consuming the most undo right now. The V$TRANSACTION view shows details about active transactions. Here’s another query that joins the V$TRANSACTION, V$SQL and V$SESSION views:

SQL> select sql.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks,
(t.USED_UBLK*8192/1024) KBytes from v$transaction t, v$session s, v$sql sql
where t.addr = s.taddr 
and s.sql_id = sql.sql_id
and s.username ='&USERNAME'


The column USED_UREC shows the number of undo records used, and the USED_UBLK column shows the undo blocks consumed by a transaction.

Resolving an ORA-01555 Error

You’re receiving the ORA-01555 (snapshot too old) errors during nightly runs of key production batch jobs. You want to eliminate these errors.

Solution
While setting a high value for the UNDO_RETENTION parameter can potentially minimize the possibility of receiving “snapshot too old” errors, it doesn’t guarantee that the database won’t overwrite older undo data that may be needed by a running transaction. You can move long-running batch jobs to a separate time interval when other programs aren’t running in the database, to avoid these errors.
Regardless, while you can minimize the occurrence of “snapshot too old” errors with these
approaches, you can’t completely eliminate such errors without specifying the guaranteed undo
retention feature. When you configure guaranteed undo retention in a database, no transaction can fail
because of the “snapshot too old” error. Oracle will keep new DML statements from executing when you set up guaranteed undo retention. Implementing the guaranteed undo feature is simple. Suppose you want to ensure that the database retains undo for at least an hour (3,600 seconds). First set the undo retention threshold with the alter system command shown here, and then set up guaranteed undo
retention by specifying the retention guarantee clause to alter the undo tablespace.

SQL> alter system set undo_retention=3600;
System altered.
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered.
SQL>

You can switch off guaranteed undo retention by executing the alter tablespace command with
the retention noguarantee clause.