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 name | Meaning |
|---|---|
| BEGIN_TIME | The beginning time for this interval check |
| END_TIME | The ending time for this interval check |
| UNDOTSN | The undo tablespace number |
| UNDOBLKS | The total number undo blocks consumed during the time interval |
| TXNCOUNT | The total number of transactions during the interval |
| MAXQUERYLEN | The maximum duration of a query within the interval |
| MAXCONCURRENCY | The highest number of transactions during the interval |
| UNXPSTEALCNT | The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests |
| UNXPBLKRELCNT | The number of unexpired blocks removed from undo segments to be used by other transactions |
| UNXPBLKREUCNT | The number of unexpired undo blocks reused by transactions |
| EXPSTEALCNT | The number of attempts when expired extents were stolen from other undo segments to satisfy a space request |
| EXPBLKRELCNT | The number of expired extents stolen from other undo segments to satisfy a space request |
| EXPBLKREUCNT | The number of expired undo blocks reused within the same undo segments |
| SSOLDERRCNT | The number of ORA-1555 errors that occurred during the interval |
| NOSPACEERRCNT | The number of Out-of-Space errors |
Make DBA Life Easy 