Reading The Docs About Stuff You Know

Posted in: Technical Track

How often do you read the reference guide for something you already know?  When I find little things like the one to follow, I wonder why Oracle doesn’t broadcast these small improvements more clearly.

I needed to change the undo_retention parameter in a 10gR2 database. Unfortunately I forgot whether the parameter was in seconds or minutes, and I wanted to see what the default was. So I open up the docs, go into the Reference guide and get the info I need from the top few lines.

I wanted to set undo_retention to some large value in order to use flashback. So I decided to read if they were any notes about setting this too large. I started reading the notes. I’ve read this in the past, so didn’t expect anything new. Then I read the following:

“For fixed-size undo tablespaces, the system automatically tunes for the maximum possible undo”

What? Fixed size undo vs autoextend undo ? That’s new!

So I clicked the link to the administrator guide and kept reading.

Apparently, when your undo tablespace is NOT autoextensible, then Oracle will switch from time based undo retention, to size based retention. Same as the good old days, where you set X amount of space for rollback segments and if there were more changes in the database … well simply you could not use flashback that far into the past. As opposed to your undo tablespace growing to something you didn’t want.

I checked back, and this feature doesn’t seem to be in 10.1, so it’s brand new. According to the notes however, it’s still time based, but based on the amount of undo you are generating and the fixed size of your undo tablespace.

Stay tuned: I will be testing this “new” behaviour sometime in the near future!

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

An Oracle ACE with a deep understanding of databases, application memory, and input/output interactions, Christo is an expert at optimizing the performance of the most complex infrastructures. Methodical and efficiency-oriented, he equates the role of an ATCG Principal Consultant in many ways to that of a data analyst: both require a rigorous sifting-through of information to identify solutions to often large and complex problems. A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

1 Comment. Leave new

Anurag Varma
March 1, 2007 6:58 pm


Although I was aware of this autotune feature .. however, after reading your comments I paid a visit to Metalink to read more.
If you are researching this, you might be interested in reading the juicy details in Bug 5387030 : AUTOMATIC TUNING OF UNDO_RETENTION CAUSING SPACE PROBLEMS



Leave a Reply

Your email address will not be published. Required fields are marked *