You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




30 August 2006

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 Comments:

Blogger Laurent Schneider said...

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

30/8/06 15:34  
Blogger Laurent Schneider said...

the document has been removed from metalink yet.

31/8/06 13:43  
Blogger Laurent Schneider said...

the document has been editted

1/9/06 14:23  
Blogger yas said...

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.

5/9/06 14:41  
Blogger lutz_hartmann said...

Hi Laurent,
I would like to point out a brilliant presentation from Oracle Open World by Munghees Minhas (principle product manager) on UNDO MANAGEMENT amoung other topics on OTN:
http://www.oracle.com/technology/products/manageability/database/pdf/ow04/1241_minhas_pres.pdf

=;-)
Lutz

6/9/06 18:59  
Blogger lutz_hartmann said...

uups, sorry for the cutted link,
here it is in three parts, you must reassemble it!:

http://www.oracle.com/technology/
products/manageability/database/
pdf/ow04/1241_minhas_pres.pdf

LUTZ

6/9/06 19:02  
Blogger lutz_hartmann said...

... and here is the white paper which I like even better:
http://www.oracle.com/technology/
products/manageability/database/pdf/
ow04/1241_minhas.pdf

=;-)
LUTZ

6/9/06 19:05  
Blogger yas said...

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.

8/9/06 12:21  
Blogger Laurent Schneider said...

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 !

8/9/06 13:06  
Blogger lutz_hartmann said...

hope you like the papers anyway,
=;-)

14/9/06 07:55  

Post a Comment

<< Home