TUNED_UNDORETENTION
How often I did meet ORA-01555: snapshot too old: rollback segment too small ?
I cannot count. On one of the database I am currently administrating it is about once a day.
Back to Oracle 7 and 8, the solution was usually to size the rollback segments properly. Using big rollback segments for big jobs, and many small segments for OLTP.
Oracle 9i introduced the automatic undo management. When you meet ORA-01555, just increase the UNDO tablespace (or/and set it autoextensible). Well, how did I read complains from users which used to have tiny rollback segments, and did not understand why the undo tablespace grows to gigabytes in 9i. There is also a parameter, called UNDO_RETENTION, which prevents Oracle from rewritting old extents before it really needs them.
In 10g, you can also force oracle to keep old undo extents up to the UNDO_RETENTION by using
What I learned today is the TUNED_UNDORETENTION mechanism. By setting UNDO_RETENTION to 0, you enable auto tuned retention. That is, as long as your tablespace can autoextend, you will less probably get an ORA-01555. A recommended lecture is Metalink Note 240746.1
Back to my 9i database with ORA-01555, I will try to increase undo_retention to something bigger than
And see how long I will survive until the next ORA-01555...
I cannot count. On one of the database I am currently administrating it is about once a day.
Back to Oracle 7 and 8, the solution was usually to size the rollback segments properly. Using big rollback segments for big jobs, and many small segments for OLTP.
Oracle 9i introduced the automatic undo management. When you meet ORA-01555, just increase the UNDO tablespace (or/and set it autoextensible). Well, how did I read complains from users which used to have tiny rollback segments, and did not understand why the undo tablespace grows to gigabytes in 9i. There is also a parameter, called UNDO_RETENTION, which prevents Oracle from rewritting old extents before it really needs them.
In 10g, you can also force oracle to keep old undo extents up to the UNDO_RETENTION by using
alter tablespace UNDOTBS1 retention guarantee;
What I learned today is the TUNED_UNDORETENTION mechanism. By setting UNDO_RETENTION to 0, you enable auto tuned retention. That is, as long as your tablespace can autoextend, you will less probably get an ORA-01555. A recommended lecture is Metalink Note 240746.1
Back to my 9i database with ORA-01555, I will try to increase undo_retention to something bigger than
SQL> select max(maxquerylen) from v$undostat;
MAX(MAXQUERYLEN)
----------------
105047
And see how long I will survive until the next ORA-01555...
Posted by Laurent Schneider at 8/30/2006 02:33:00 PM
Permalink : TUNED_UNDORETENTION
Post to :
del.icio.us -
furl -
digg -
reddit -
co.mments
6 Comments:
I have posted feedback to metalink because the document contains errors.
One of them is :
undo_retention = 0 Which is default value in 10g
Of course the default is 900
select NAME,VALUE,ISDEFAULT
from v$parameter2
where name='undo_retention';
NAME VALUE ISDEFAULT
-------------- ----- ---------
undo_retention 900 TRUE
the document has been removed from metalink yet.
the document has been editted
By setting UNDO_RETENTION to 0, you enable auto tuned retention
As far as I know tuned undo retention is enabled by default. Setting the undo_retention acts as a minimal threshold.
There is a difference in the tuned undo retention value between fixed size undo tablespaces and autoextend enabled tablespaces. From the 10G Release 2 Administrator's Guide:
If the undo tablespace is
configured with the AUTOEXTEND option, undo retention tuning is slightly different. In
this case, the database tunes the undo retention period to be slightly longer than the
longest-running query on the system at that time.
Undo Retention Tuning and Alert Thresholds For a fixed size undo tablespace, the
database calculates the maximum undo retention period based on database statistics
and on the size of the undo tablespace. For optimal undo management, rather than
tuning based on 100% of the tablespace size, the database tunes the undo retention
period based on 85% of the tablespace size, or on the warning alert threshold
percentage for space used, whichever is lower. (The warning alert threshold defaults to
85%, but can be changed.) Therefore, if you set the warning alert threshold of the undo
tablespace below 85%, this may reduce the tuned length of the undo retention period.
here it is in three parts, you must reassemble it!
Lutz, you can use tinyurl.com to shorten long urls. You can even use its bookmarklet for Firefox to shorten the url with a single click.
thanks all for the comments. apart from tinyurl, notice lutz that the link is just "apparently" truncated, that is, if you select it for copy-paste, it will be selected full.
you can also use <a> tags on my blog !
Post a Comment
<< Home