Snapshot Too Old Error

The ORA-1555 or Snapshot Too Old errors are reported when the read consistent images are unavailable in the Undo tablespace. This happens when there is not enough space in the Undo tablespace to retain the undo records for the long running queries.

Snapshot Too Old Error detected: SQL ID 3jgzkm92q02i8, Snapshot SCN 0x074c.3280364e, Recent SCN 0x074c.33f38d0c, Undo Tablespace UNDOTBS1, Current Undo Retention 14542.

INVESTIGATION and SOLUTION

Tune the SQL

Find sql from v$sql or dba_hist_sqltext, and tune it accordingly

SQL> select SQL_TEXT, SQL_FULLTEXT from v$sql where sql_Id='3jgzkm92q02i8';

SQL>  select SQL_TEXT from DBA_HIST_SQLTEXT where sql_Id='3jgzkm92q02i8';

Check and Increase UNDO_RETENTION

SQL> show parameter undo_retention

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     1000

SQL> select max(maxquerylen) from v$UNDOSTAT;

MAX(MAXQUERYLEN)
----------------
            1961

SQL> select max(maxquerylen) from DBA_HIST_UNDOSTAT;

MAX(MAXQUERYLEN)
----------------
           22330

Set UNDO_RETENTION to the max of the above values, and make sure the UNDO tablespace has big enough space.

SQL> alter system set UNDO_RETENTION=23000;

UNDO Tablespace Size Advisor

To Get The Output using the historical information in memory :

SQL> SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;

REQUIRED_UNDO_SIZE
-----------------------------------------------------------------------------------------------------------
The Required undo tablespace size using Statistics In Memory is 2048 MB

To Get The Output using Begin/End AWR snapshot id :

SQL> SELECT 'The Required Undo tablespace size During This AWR snaps Range is ' || dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) || ' MB' required_undo_size FROM dual;

REQUIRED_UNDO_SIZE
------------------------------------------------------------------------------------------------------------
The Required Undo tablespace size During This AWR snaps Range is 2048 MB

Monitor UNDO Tablespace

The V$UNDOSTAT view holds undo statistics for 10-minute intervals, which represents statistics across instances, thus each begins time, end time, and statistics value will be a unique interval per instance.

Column nameMeaning
BEGIN_TIMEThe beginning time for this interval check
END_TIMEThe ending time for this interval check
UNDOTSNThe undo tablespace number
UNDOBLKSThe total number undo blocks consumed during the time interval
TXNCOUNTThe total number of transactions during the interval
MAXQUERYLENThe maximum duration of a query within the interval
MAXCONCURRENCYThe highest number of transactions during the interval
UNXPSTEALCNTThe number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests
UNXPBLKRELCNTThe number of unexpired blocks removed from undo segments to be used by other transactions
UNXPBLKREUCNTThe number of unexpired undo blocks reused by transactions
EXPSTEALCNTThe number of attempts when expired extents were stolen from other undo segments to satisfy a space request
EXPBLKRELCNTThe number of expired extents stolen from other undo segments to satisfy a space request
EXPBLKREUCNTThe number of expired undo blocks reused within the same undo segments
SSOLDERRCNTThe number of ORA-1555 errors that occurred during the interval
NOSPACEERRCNTThe number of Out-of-Space errors

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.