ORA-01555 Snapshot Too Old
Oracle Database Tips by Donald BurlesonConsulting
The ORA-01555 is caused by Oracle read
consistency mechanism. If you have a long running SQL that
starts at 10:30 AM, Oracle ensures that all rows are as they appeared
at 10:30 AM, even if the query runs until noon!
Oracles does this by reading the "before image"
of changed rows from the online undo segments. If you have lots
of updates, long running SQL and too small UNDO, the ORA-01555 error
From the docs we see that the ORA-01555 error
relates to insufficient undo storage or a too small value for the
ORA-01555: snapshot too old: rollback segment number
string with name "string" too small
Rollback records needed by a reader for consistent read are
overwritten by other writers.
Action: If in
Automatic Undo Management mode, increase the setting of
UNDO_RETENTION. Otherwise, use larger rollback segments.
You can get an ORA-01555 error
with a too-small undo_retention, even with a large undo
tables. However, you can set a super-high value for
undo_retention and still get an ORA-01555 error.
Also see these important notes on
commit frequency and the ORA-01555 error
The ORA-01555 snapshot too old
error can be addressed
by several remedies:
Re-schedule long-running queries when the system
has less DML load.
the size of your rollback segment (undo) size. The ORA-01555 snapshot too old also relates to
your setting for
automatic undo retention.
Don't fetch between commits.
Avoiding the ORA-01555 error
Adams has good notes on avoiding the ora-1555 snapshot too old
Schedule long running queries and transactions out of hours,
so that the consistent gets will not need to rollback changes
made since the snapshot SCN. This also reduces the work done by
the server, and thus improves performance.
Oracle ACE Steve Karam also has
advice on avoiding the ORA-01555: Snapshot too old, rollback segment
too small with UNDO sizing.
Question: I am updating 1
million rows on Oracle 10g, and I run it as batch process,
committing after each batch to avoid undo generation. But in Oracle
10g I am told undo management is automatic and I do not need run the
update as batch process.
Answer: Automatic undo management was available in 9i as
well, and my guess is you were probably using it there. However,
I'll assume for the sake of this writing that you were using manual
undo management in 9i and are now on automatic.
Automatic undo management depends upon the UNDO_RETENTION
parameter, which defines how long Oracle should try to keep
committed transactions in UNDO segments. However, the UNDO_RETENTION
parameter is only a suggestion. You must also have an UNDO
tablespace that's large enough to handle the amount of UNDO you will
be generating/holding, or you will get "ORA-01555: Snapshot too old,
rollback segment too small" errors.
You can use the UNDO advisor to find out how large this tablespace
should be given a desired UNDO retention, or look online for some
scripts…just Google for: oracle undo size
Oracle 10g also gives you the ability to guarantee undo. This means
that instead of throwing an error on SELECT statements, it
guarantees your UNDO retention for consistent reads and instead
errors your DML that would cause UNDO to be overwritten.
Now, for your original question…yes, it's easier for the DBA to
minimize the issues of UNDO when using automatic undo management. If
you set the UNDO_RETENTION high enough with a properly sized undo
tablespace you shouldn't have as many issues with UNDO.
you commit should have nothing to do with it, as long as your DBA
has properly set UNDO_RETENTION and has an optimally sized UNDO
tablespace. Committing more often will only result in your script
taking longer, more LGWR/DBWR issues, and the "where was I" problem
if there is an error (if it errors, where did it stop?).
Lastly (and true even for manual undo management), if you commit
more frequently, you make it more possible for ORA-01555 errors to
occur. Because your work will be scattered among more undo segments,
you increase the chance that a single one may be overwritten if
necessary, thus causing an ORA-01555 error for those that require it
for read consistency.
It all boils down to the size of the undo tablespace and the undo
retention, in the end…just as manual management boiled down to the
size, amount, and usage of rollback segments. Committing frequently
is a peroxide band-aid: it covers up the problem, tries to clean it,
but in the end it just hurts and causes problems for otherwise
Oracle guru Joel Garry offers another great
explanation of the machinations of the ORA-01555 error:
You have to understand, in
general, ORA-01555 means something else is causing it to die -
Oracle needs to be able to create a read-consistent view of the
table for the query as it looked at the start of the query, and
it is unable to because something has overwritten the undo
necessary to create such a view. Since you have the same table
over and over in your alert log, that probably means the
something is the previous queries your monitoring software is
making, not ever releasing the transaction.
10AM query starts, never
11AM query starts, never
Noon query starts, never
1PM query starts
Meanwhile, the undo needed
from the 10AM query for the 1PM query gets overwritten, 1PM
query dies with ORA-01555, since it needs to know what the table
looked like before the 10AM query started mucking with it.
Also if the query is a loop with a commit in it, it can do the
same thing without other queries, as eventually the next
iteration requires looking back at it's own previous first
generation, can't do it, and barfs.
Upping undo_retention may help, or may not, depending on the
real cause. Also check v$undostat, you may still have
information in there if this is ongoing (or may not, since by
the time you check it the needed info may be gone).
Also see our notes on ORA-01555
Get the Complete
Oracle SQL Tuning Information
The landmark book
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
for 30% off directly from the publisher.