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.