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