Wednesday, February 4, 2015

ORA-01555 Snapshot Too Old

This is one of the most common error messages faced by the DBAs when they receive complains of failing SQLs with this error message. Alert log may contain a message as follows

############################
ORA-01555 caused by SQL statement below (SQL ID: 1f345hftyx6hq, Query Duration=14217 sec, SCN: 0x0007.e56cf09f):

############################

The error occurs when an SQL needs old image of a data block from UNDO segment which was there when SQL started execution, but is no longer available (over written) when SQL needs that block during the course of its execution.
The reason for block to be over written in the UNDO segment could be insufficient size of UNDO tablespace or sub-optimal value of parameter UNDO_RETENTION.

First of all, you should make sure that you are using the recommended way of UNDO management i.e. Automatic Undo Management – by creating an undo tablespace and setting UNDO_MANAGEMENT init parameter to AUTO.
Secondly, UNDO_RETENTION parameter should have a value; more than the duration of your longest running query. You can use following query to find out your queries duration

SELECT maxqueryid, maxquerylen FROM v$undostat ORDER BY 2;

Moreover, the size of UNDO tablespace should also be monitored by the DBAs while system is at its peak load and should increase the size if required because insufficient size of UNDO tablespace would also cause undo data to be over written which is still needed by a query.

ORA-01555 may surface during data export, if CONSISTENT parameter is set to Y during export. In this case, your UNDO_RETENTION should be set to a value higher than the duration of your export process


No comments: