Oracle 10g's Undo Advisor

Thursday Oct 7th 2004 by James Koopmann
Share:

Oracle 10g simplifies the creation of undo records with Undo Advisor.

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

Switching to Automatic Undo

Oracle recommends that we all migrate our databases to automatic undo management. To accomplish this you need first to determine the proper sizing of the undo tablespace. These new set of functions allow you to get back an answer directly from Oracle regarding what the particular size should be based on the current configuration and usage of your current rollback segments defined within your database.

SQL > set serveroutput on
SQL > DECLARE
     utbsiz_in_MB NUMBER;
  BEGIN
     utbsiz_in_MB := 
      DBMS_UNDO_ADV.RBU_MIGRATION;
  dbms_output.put_line
 ('undo size : '||utbsiz_in_MB||'MB');
  end;
  /
undo size : 740MB
PL/SQL procedure successfully completed.

After Switching to Automatic Undo

After switching to automatic undo, you can now call the desired functions to check the current status of your undo, get suggestions on what to alter, and hand-off the administration to Oracle. Listing 1 gives a script you can run (Oracle 10g only) to view your current undo status. If you have not jumped on the bandwagon to automatic undo management, I need only mention the benefits. If you switch to AUM, you will no longer need to worry about creating, managing, and sizing rollback segments or the assignment of specific transactions to specific rollback segments. Moreover, probably the biggest reason is that after switching properly to AUM you will never need to worry about the ever present ORA-1555 snapshot too old error.

Listing 1

Script for function calls to Undo Advisor

DECLARE
tablespacename        varchar2(30);
tablespacesize        number;
autoextend            boolean;
autoextendtf          char(5);
undoretention         number;
retentionguarantee    boolean;
retentionguaranteetf  char(5);
autotuneenabled       boolean;
autotuneenabledtf     char(5);
longestquery          number;
requiredretention     number;
bestpossibleretention number;
requireundosize       number;


problem               varchar2(100);
recommendation        varchar2(100);
rationale             varchar2(100);
retention             number;
utbsize               number;
nbr                   number;
undoadvisor           varchar2(100);
instancenumber        number;
ret                   boolean;
rettf                 char(5);
BEGIN
   dbms_output.put_line('--x--x--x--x--x--x--x');
   dbms_output.put_line('--undo_info');
   dbms_output.put_line('--x--x--x--x--x--x--x');
   ret := dbms_undo_adv.undo_info(tablespacename,
tablespacesize, 
          autoextend, undoretention,
retentionguarantee);
   if ret then rettf := 'TRUE'; else rettf :=
'FALSE'; end if;
   if autoextend then autoextendtf := 'TRUE'; 
      else autoextendtf := 'FALSE'; end if;
   if retentionguarantee then retentionguaranteetf
:= 'TRUE'; 
      else retentionguaranteetf := 'FALSE'; end if;
   dbms_output.put_line ('Information Valid    :
'||rettf);
   dbms_output.put_line ('Tablespace Name      :
'||tablespacename);
   dbms_output.put_line ('Tablespace Size      :
'||tablespacesize);
   dbms_output.put_line ('Extensiable          :
'||autoextendtf);
   dbms_output.put_line ('undo_retention       :
'||undoretention);
   dbms_output.put_line ('Guaranteed Retention :
'||retentionguaranteetf);


   dbms_output.put_line('--x--x--x--x--x--x--x');
   dbms_output.put_line('--undo_health');
   dbms_output.put_line('--x--x--x--x--x--x--x');
   nbr := dbms_undo_adv.undo_health(problem,
recommendation, rationale, retention, utbsize);
   dbms_output.put_line ('Information Valid    :
'||nbr);
   dbms_output.put_line ('Problem              :
'||problem);
   dbms_output.put_line ('Recommendation       :
'||recommendation);
   dbms_output.put_line ('Rationale            :
'||rationale);
   dbms_output.put_line ('Retention            :
'||retention);
   dbms_output.put_line ('UTBSize              :
'||utbsize);


   dbms_output.put_line('--x--x--x--x--x--x--x');
   dbms_output.put_line('--undo_advisor');
   dbms_output.put_line('--x--x--x--x--x--x--x');
   select instance_number into instancenumber from
v$instance;
   undoadvisor :=
dbms_undo_adv.undo_advisor(instancenumber);
   dbms_output.put_line ('Undo Advisor         :
'||undoadvisor);


   dbms_output.put_line('--x--x--x--x--x--x--x');
   dbms_output.put_line('--undo_autotune');
   dbms_output.put_line('--x--x--x--x--x--x--x');
   ret :=
dbms_undo_adv.undo_autotune(autotuneenabled);
   if autotuneenabled then autotuneenabledtf :=
'TRUE'; 
       else autotuneenabledtf := 'FALSE'; end if;
   dbms_output.put_line ('Auto Tuning Enabled  :
'||autotuneenabledtf);


   dbms_output.put_line('--x--x--x--x--x--x--x');
   dbms_output.put_line('--longest_query');
   dbms_output.put_line('--x--x--x--x--x--x--x');
   longestquery :=
dbms_undo_adv.longest_query(sysdate-1,sysdate);
   dbms_output.put_line ('Longest Run Query    :
'||longestquery);


   dbms_output.put_line('--x--x--x--x--x--x--x');
   dbms_output.put_line('--required_retention');
   dbms_output.put_line('--x--x--x--x--x--x--x');
   requiredretention :=
dbms_undo_adv.required_retention;
   dbms_output.put_line ('Required Retention   :
'||requiredretention);


   dbms_output.put_line('--x--x--x--x--x--x--x');
  
dbms_output.put_line('--best_possible_retention');
   dbms_output.put_line('--x--x--x--x--x--x--x');
   bestpossibleretention :=
dbms_undo_adv.best_possible_retention;
   dbms_output.put_line ('Best Retention       :
'||bestpossibleretention);


   dbms_output.put_line('--x--x--x--x--x--x--x');
   dbms_output.put_line('--required_undo_size');
   dbms_output.put_line('--x--x--x--x--x--x--x');
   requireundosize := dbms_undo_adv.required_undo_size(444);
   dbms_output.put_line ('Required Undo Size   :
'||requireundosize);


END;
/

Extending the current Model

The prior script is only concerned with current or accumulated activity. We have all learned that our systems actually behave differently at peak and low times during the day. Sometimes the configuration of our undo should also reflect these peak times since configuration for "general" usage patterns during the day would leave un-favorable results and possible failure of the undo mechanism. To assist in these peak or unusual times, Oracle also lets us query these functions with snapshot IDS or beginning and ending dates. If this is something you are interested in, just describe the DBMS_UNDO_ADV package and take note of the extra in variables required and you will be on your way.

This article is not in any way a performance guide on how to tune your undo tablespaces. Oracle has given us the tools to directly access how we should tune undo for periods in time, and thus taken all hidden "expert" knowledge and put it into everyone's hands. The only real difficulty in running these queries was "finding" where they were and I need only thank Oracle Support for pointing me in the right direction. You may also find these set of function calls and what seems to be the only documentation to date in $ORACLE_HOME/rdbms/admin/dbmsuadv.sql.

» See All Articles by Columnist James Koopmann

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved