Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Oct 7, 2004

Oracle 10g's Undo Advisor

By James Koopmann

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



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM