SHARE

Oracle 10g’s Undo Advisor

Written By

Oct 7, 2004

As a DBA, you are always faced with the critical question of
whether the undo that you have created is in fact sufficient for the
transaction mix on your database system. Oracle has now given us an Undo
Advisor in Oracle 10g to help simplify the answer.

Undo within an Oracle database
is used to store critical information for when your transaction may want to
issue a ROLLBACK command and back out the changes made after the last COMMIT
point. Undo is also used to recover a database from failure by applying undo
records from undo, to rollback any uncommitted changes. Undo records also provide
read consistency to satisfy a result set that is guaranteed at the time you
issue DML in relation to other users changing data mid-stream of your result
set. Being a DBA, you are always faced with the critical question of whether
the undo that you have created is in fact sufficient for the transaction mix on
your database system to provide the consistency and recovery requested by users.
Oracle to the rescue. We can now query a set of functions under the Undo
Advisor framework to simplify our lives and help answer the many questions of
how our undo configuration is standing up to current work loads. These sets of
procedures have been created to assist in the configuration and maintenance of
the undo area. Table 1 gives a quick look at the function, description, and
expected output of these function calls.

Table 1
Function calls within DBMS_UNDO_ADV

Function	Description	Outputs
undo_info	Provides basic information about your undo	Tablespace name Maximum size possible Current retention value If undo is auto extensible If undo is guaranteed undo retention
longest_query	Allows you to see what the longest running query was so you might be able to tune in relation to time	Length of the longest query
required_retention	Query this function to assist in determining what you should set undo_retention to in order to help prevent snap-shot-too-old errors. This value is based on the longest running query.	init.ora parameter undo_retention
best_possible_retention	Provides you with a value for undo_retention that best fits your current undo tablespace size and usage.	init.ora parameter undo_retention
required_undo_size	Provides a value for the size of the undo tablespace to create in order to support the current value of the init.ora parameter undo_retention.	Undo tablespace size
undo_health	Gives descriptive output if any problems are encountered with your current undo tablespace size or setting of the init.ora parameter undo_retention and provides recommendations to fix.	Problem / Resolution descriptions
undo_advisor	Uses the advisor framework to give descriptive output if any problems are encountered with your current undo configuration and possible resolutions.	Problem / Resolution descriptions
undo_autotune	Tells you if undo auto tuning for undo retention is enabled	TRUE / FALSE
rbu_migration	Provides the size required for undo tablespace size if you wish to switch to automatic undo management	Size of Undo Tablespace

James Koopmann

James Koopmann has fourteen years of database design, development and performance tuning experience. In addition, he has extensive database administration experience in Oracle and other relational databases in production environments, specializing in performance tuning of database engines and SQL based applications. Koopmann is an accomplished author with several technical papers in various Oracle related publications such as Oracle Magazine, Oracle Professional and SQL>UPDATE_RMOUG. He is a featured author and database expert for DatabaseJournal, a member of the editorial review committee for Select Journal (The Magazine for the International Oracle Users Group), an Oracle Certified Professional DBA and noted speaker at local Oracle User Groups around the country.

Oracle 10g’s Undo Advisor

James Koopmann

Recommended for you...

Company

Categories