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 - Page 2

By James Koopmann

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



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


















Thanks for your registration, follow us on our social networks to keep up-to-date