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
» 
IE 7
Firefox 2.0
Comment and Contribute
