What is "snapshot too old" error
and how to avoid it?
ORA-01555: snapshot too old: rollback segment number with name "" too small.
First thing to be understood here is how rollback segments work. These work in a circular fashion by looping around to reuse the extents which have been released by committed transactions. Thus in case of long running queries if data is changed while the query is running, rollback segments created for that change are needed to make a read consistent image for the lon running query. When these extents are reused while these were still need by the query, this error occurs, because oracle can no longer provide a read consistent image of the data.
To avoid this error you need to postpone the reuse of extents. Thus increasing the size of rollback segments and the value of OPTIMAL parameter should do the trick. Committing less often would be other solution. As until transaction is c omitted rollback segment can not be reused thus reducing the chances of a snapshot too old error.
Solution:-
You'll probably need to dive into Oracle DB administration if you want to solve it via increasing the UNDO log. Basically you do (as SYSDBA):
However, Oracle will only keep 6 hours of old data if the UNDO log files are big enough, which depends on the size of the rollback segments and the amount of updates executed on the database.
So in addition to changing the undo retention time, you should also make sure that few concurrent updates are executed while your job is running. In particular, updates of the data your job is reading should be minimized.
If everything fails, increase the UNDO logs.
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.
Increasing the size of your rollback segments(undo)size. The ORA-01555 snapshot too old also relates to your setting for automatically undo retention.
Don’t fetch between commits.
Ways to avoiding the ORA-01555 error are mention below:
Do not run discrete transactions while sensitive queries or transactions are running, unless you are confident that the data sets required are mutually exclusive.
Schedule long running queries and transactions out of hours, so that the consistent gets will not to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.
Code long–running processes as a series of restart–able steps.
Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce the risk of consistent get rollback failure due to extent reallocation.
Use a large optimal value on all rollback segments, to delay extent reuse.
Don’t fetch across commits, that is, don’t fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.
Use a large database block size to maximize the number of slots in the rollback segment transaction tables, and thus delay slot reuse.
Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.
If necessary, add extra rollback segments (undo logs) to make more transaction slots available.
ORA-01555: snapshot too old: rollback segment number with name "" too small.
First thing to be understood here is how rollback segments work. These work in a circular fashion by looping around to reuse the extents which have been released by committed transactions. Thus in case of long running queries if data is changed while the query is running, rollback segments created for that change are needed to make a read consistent image for the lon running query. When these extents are reused while these were still need by the query, this error occurs, because oracle can no longer provide a read consistent image of the data.
To avoid this error you need to postpone the reuse of extents. Thus increasing the size of rollback segments and the value of OPTIMAL parameter should do the trick. Committing less often would be other solution. As until transaction is c omitted rollback segment can not be reused thus reducing the chances of a snapshot too old error.
Solution:-
You'll probably need to dive into Oracle DB administration if you want to solve it via increasing the UNDO log. Basically you do (as SYSDBA):
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string APPS_UNDOTS1
SQL> ALTER SYSTEM SET UNDO_RETENTION = 21600; However, Oracle will only keep 6 hours of old data if the UNDO log files are big enough, which depends on the size of the rollback segments and the amount of updates executed on the database.
So in addition to changing the undo retention time, you should also make sure that few concurrent updates are executed while your job is running. In particular, updates of the data your job is reading should be minimized.
If everything fails, increase the UNDO logs.
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.
Increasing the size of your rollback segments(undo)size. The ORA-01555 snapshot too old also relates to your setting for automatically undo retention.
Don’t fetch between commits.
Ways to avoiding the ORA-01555 error are mention below:
Do not run discrete transactions while sensitive queries or transactions are running, unless you are confident that the data sets required are mutually exclusive.
Schedule long running queries and transactions out of hours, so that the consistent gets will not to rollback changes made since the snapshot SCN. This also reduces the work done by the server, and thus improves performance.
Code long–running processes as a series of restart–able steps.
Shrink all rollback segments back to their optimal size manually before running a sensitive query or transaction to reduce the risk of consistent get rollback failure due to extent reallocation.
Use a large optimal value on all rollback segments, to delay extent reuse.
Don’t fetch across commits, that is, don’t fetch on a cursor that was opened prior to the last commit, particularly if the data queried by the cursor is being changed in the current session.
Use a large database block size to maximize the number of slots in the rollback segment transaction tables, and thus delay slot reuse.
Commit less often in tasks that will run at the same time as the sensitive query, particularly in PL/SQL procedures, to reduce transaction slot reuse.
If necessary, add extra rollback segments (undo logs) to make more transaction slots available.
No comments:
Post a Comment