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

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…

10 thoughts on “TUNED_UNDORETENTION”

  1. Laurent Schneider

    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

  4. yas

    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.

  8. yas

    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.

  9. Laurent Schneider

    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 !

Comments are closed.