dcsimg

Oracle 10g's Undo Advisor

October 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








The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers