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 |
|
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 |
Length of the longest query |
|
required_retention |
Query this function to assist in determining what you |
init.ora parameter undo_retention |
|
best_possible_retention |
Provides you with a value for undo_retention that best |
init.ora parameter undo_retention |
|
required_undo_size |
Provides a value for the size of the undo tablespace to |
Undo tablespace size |
|
undo_health |
Gives descriptive output if any problems are encountered |
Problem / Resolution descriptions |
|
undo_advisor |
Uses the advisor framework to give descriptive output if |
Problem / Resolution descriptions |
|
undo_autotune |
Tells you if undo auto tuning for undo retention is |
TRUE / FALSE |
|
rbu_migration |
Provides the size required for undo tablespace size if you |
Size of Undo Tablespace |
