Oracle 10g’s Undo Advisor

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
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.

Latest Articles