Oracle 10g's Undo Advisor - Page 2

October 7, 2004

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








The Network for Technology Professionals

Search:

About Internet.com

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