CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle DBMS_UNDO_ADV
Version 11.1
 
General Information
Purpose Undo advisor gives users recommendation on setting undo retention and sizing undo tablespace. Undo retention is in seconds.
Source {ORACLE_HOME}/rdbms/admin/dbmsuadv.sql
First Available 10.1
Data Type CREATE OR REPLACE TYPE dbms_uadv_arr as VARRAY(100) of NUMBER;
/
Dependencies

DBA_TABLESPACES

DBMS_UNDO_ADV PRVT_ADVISOR
DBMS_SQL GV$DATABASE UTL_LMS
DBMS_SYS_ERROR GV$PARAMETER X$KSPPCV2
DBMS_UADV_ARR GV$ROLLSTAT X$KSPPI
DBMS_UNDOADV_LIB PLITBLM  
Overload Definitions
Overload 1 Subprogram is based on historical information in memory or in SWRF from start time to end time
Overload 2 Subprogram is based on historical information in memory or in SWRF from sysdate-7 to sysdate
Overload 3 Subprogram is based on historical information in SWRF from snapid s1 to snapid s2 (AWR)
Security Model execute is granted to the DBA role
 
BEST_POSSIBLE_RETENTION

Returns the best possible undo retention current undo tablespace can support based on undo stats between the start time and end time

Overload 1
dbms_undo_adv.best_possible_retention(
starttime IN DATE,
endtime   IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE)
FROM dual;
Overload 2 dbms_undo_adv.best_possible_retention RETURN NUMBER;
SELECT dbms_undo_adv.best_possible_retention
FROM dual;

Overload 3
dbms_undo_adv.best_possible_retention(s1 IN NUMBER, s2 IN NUMBER)
RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT dbms_undo_adv.best_possible_retention(578, 600)
FROM dual;
 
LONGEST_QUERY

Returns the length of the longest query (between the start time and the end time)

Overload 1
dbms_undo_adv.longest_query(starttime IN DATE, endtime IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE)
FROM dual;
Overload 2 dbms_undo_adv.longest_query RETURN NUMBER;
SELECT dbms_undo_adv.longest_query
FROM dual;

Overload 3
dbms_undo_adv.longest_query(s1 IN NUMBER, s2 IN NUMBER) RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT dbms_undo_adv.longest_query(578, 600)
FROM dual;
 
RBU_MIGRATION
Undocumented

Overload 1
dbms_undo_adv.rbu_migration(starttime IN DATE, endtime IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.rbu_migration(SYSDATE-1/24, SYSDATE)
FROM dual;
Overload 2 dbms_undo_adv.rbu_migration RETURN NUMBER;
SELECT dbms_undo_adv.rbu_migration
FROM dual;
 
REQUIRED_RETENTION
Returns the required undo retention to support longest query based on undo stats between the start time
and the end time


Overload 1
dbms_undo_adv.required_retention(starttime IN DATE, endtime IN DATE)
RETURN NUMBER;
SELECT dbms_undo_adv.required_retention(SYSDATE-30, SYSDATE)
FROM dual;

SELECT dbms_undo_adv.required_retention(SYSDATE-1/1440, SYSDATE)
FROM dual;
Overload 2 dbms_undo_adv.required_retention RETURN NUMBER;
SELECT dbms_undo_adv.required_retention
FROM dual;

Overload 3
dbms_undo_adv.required_retention(s1 IN NUMBER, s2 IN NUMBER)
RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT dbms_undo_adv.required_retention(578, 600)
FROM dual;
 
REQUIRED_UNDO_SIZE
Returns the required undo tablespace size to support undo retention based on undo stats between the start time and the end time

Overload 1
dbms_undo_adv.required_undo_size(retention IN NUMBER, 
starttime IN DATE, endtime IN DATE) RETURN NUMBER;
SELECT dbms_undo_adv.required_undo_size(900, SYSDATE-30, SYSDATE)
FROM dual;
Overload 2 dbms_undo_adv.required_undo_size(retention IN NUMBER) RETURN NUMBER;
SELECT dbms_undo_adv.required_undo_size(20)
FROM dual;

SELECT dbms_undo_adv.required_undo_size(1440)
FROM dual;

Overload 3
dbms_undo_adv.required_undo_size(retention IN NUMBER, s1 IN NUMBER, 
s2 IN NUMBER) RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

SELECT dbms_undo_adv.required_undo_size(1440, 578, 600)
FROM dual;
Overload 4 dbms_undo_adv.required_undo_size(
retention IN     dbms_uadv_arr,
utbsize   IN OUT dbms_uadv_arr,
starttime IN     DATE,
endtime   IN     DATE)
RETURN NUMBER;
TBD
Overload 5 dbms_undo_adv.required_undo_size(
retention IN     dbms_uadv_arr,
utbsize   IN OUT dbms_uadv_arr)
RETURN NUMBER;
TBD
Overload 6 dbms_undo_adv.required_undo_size(
retention IN     dbms_uadv_arr,
utbsize   IN OUT dbms_uadv_arr,
s1        IN     NUMBER,
s2        IN     NUMBER)
RETURN NUMBER;
TBD
 
UNDO_ADVISOR
Uses the advisor framework to identify problems and provide recommendations

Overload 1
dbms_undo_adv.undo_advisor(
starttime IN DATE,
endtime   IN DATE,
instance  IN NUMBER) 
RETURN VARCHAR2;
set serveroutput on

DECLARE
 v VARCHAR2(300);
BEGIN
  v := dbms_undo_adv.undo_advisor(SYSDATE-1/1440, SYSDATE, 1);
  dbms_output.put_line(v);
END;
/

Overload 2
dbms_undo_adv.undo_advisor(instance IN NUMBER) RETURN VARCHAR2;
set serveroutput on

DECLARE
 v VARCHAR2(100);
BEGIN
  v := dbms_undo_adv.undo_advisor(1);
  dbms_output.put_line(v);
END;
/

Overload 3
dbms_undo_adv.undo_advisor(s1 IN NUMBER, s2 IN NUMBER,
instance IN NUMBER) RETURN VARCHAR2;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

set serveroutput on

DECLARE
 retval VARCHAR2(200);
BEGIN
  retval := dbms_undo_adv.undo_advisor(578, 600, 1);
  dbms_output.put_line(retval);
END;
/
 
UNDO_AUTOTUNE

Undocumented
dbms_undo_adv.undo_autotune(autotune_enabled OUT BOOLEAN)
RETURN BOOLEAN;
DECLARE
 bp BOOLEAN;
 br BOOLEAN;
BEGIN
  br := dbms_undo_adv.undo_autotune(bp);

  IF bp THEN
    dbms_output.put_line('Parameter is true');
  ELSE
    dbms_output.put_line('Parameter is false');
  END IF;

  IF br THEN
    dbms_output.put_line('Return value is true');
  ELSE
    dbms_output.put_line('Return value is false');
  END IF;
END;
/
 
UNDO_HEALTH

Find out the problem in undo tablespace and provide recommendation to fix the problem. If no problem found, return value is 0


Overload 1
dbms_undo_adv.undo_health(
problem        OUT VARCHAR2,
recommendation OUT VARCHAR2,
rationale      OUT VARCHAR2,
retention      OUT NUMBER,
utbsize        OUT NUMBER) RETURN NUMBER;
set serveroutput on

DECLARE
 prob VARCHAR2(100);
 reco VARCHAR2(100);
 rtnl VARCHAR2(100);
 retn PLS_INTEGER;
 utbs PLS_INTEGER;
 retv PLS_INTEGER;
BEGIN
  retv := dbms_undo_adv.undo_health(prob, reco, rtnl, retn, utbs);
  dbms_output.put_line('Problem: ' || prob);
  dbms_output.put_line('Recmmnd: ' || reco);
  dbms_output.put_line('Rationl: ' || rtnl);
  dbms_output.put_line('Retentn: ' || TO_CHAR(retn));
  dbms_output.put_line('UTBSize: ' || TO_CHAR(utbs));
END;
/

Overload 2
dbms_undo_adv.undo_health(
starttime       IN  DATE,
endtime         IN  DATE,
problem         OUT VARCHAR2,
recommendation  OUT VARCHAR2,
rationale       OUT VARCHAR2,
retention       OUT NUMBER,
utbsize         OUT NUMBER) RETURN NUMBER; 
set serveroutput on

DECLARE
 prob VARCHAR2(100);
 reco VARCHAR2(100);
 rtnl VARCHAR2(100);
 retn PLS_INTEGER;
 utbs PLS_INTEGER;
 retv PLS_INTEGER;
BEGIN
  retv := dbms_undo_adv.undo_health(SYSDATE-1/24, SYSDATE, prob, reco, rtnl, retn, utbs);
  dbms_output.put_line('Problem: ' || prob);
  dbms_output.put_line('Recmmnd: ' || reco);
  dbms_output.put_line('Rationl: ' || rtnl);
  dbms_output.put_line('Retentn: ' || TO_CHAR(retn));
  dbms_output.put_line('UTBSize: ' || TO_CHAR(utbs));
END;
/

Overload 3
dbms_undo_adv.undo_health(
s1             IN  NUMBER,
s2             IN  NUMBER,
problem        OUT VARCHAR2,
recommendation OUT VARCHAR2,
rationale      OUT VARCHAR2,
retention      OUT NUMBER,
utbsize        OUT NUMBER) RETURN NUMBER;
col instart_fmt noprint;
col inst_name format a12 heading 'Instance';
col db_name format a12 heading 'DB Name';
col snap_id format 99999990 heading 'Snap Id';
col snapdat format a18 heading 'Snap Started' just c;
col lvl format 99 heading 'Snap|Level';
set heading on;
break on inst_name on db_name on host on instart_fmt skip 1;
ttitle off;

SELECT TO_CHAR(s.startup_time,' DD MON "at" HH24:MI:SS') INSTART_FMT,
di.instance_name INST_NAME, di.db_name DB_NAME, s.snap_id SNAP_ID,
TO_CHAR(s.end_interval_time,'DD MON YYYY HH24:MI') SNAPDAT,
s.snap_level LVL
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND di.startup_time = s.startup_time
ORDER BY snap_id;

DECLARE
 prob VARCHAR2(100);
 reco VARCHAR2(100);
 rtnl VARCHAR2(100);
 retn PLS_INTEGER;
 utbs PLS_INTEGER;
 retv PLS_INTEGER;
BEGIN
  retv := dbms_undo_adv.undo_health(578, 600, prob, reco, rtnl, retn, utbs);
  dbms_output.put_line('Problem: ' || prob);
  dbms_output.put_line('Recmmnd: ' || reco);
  dbms_output.put_line('Rationl: ' || rtnl);
  dbms_output.put_line('Retentn: ' || TO_CHAR(retn));
  dbms_output.put_line('UTBSize: ' || TO_CHAR(utbs));
END;
/
 
UNDO_INFO

Returns current undo tablespace name, max possible size, if it is auto extensible, current undo retention value and if the undo tablespace has guaranteed undo retention
dbms_undo_adv.undo_info(
table_space_name    OUT VARCHAR2,
table_space_size    OUT NUMBER,
auto_extend         OUT BOOLEAN,
undo_retention      OUT NUMBER,
retention_guarantee OUT BOOLEAN)
RETURN BOOLEAN;
set serveroutput on

DECLARE
 tsn    VARCHAR2(40);
 tss    NUMBER(10);
 aex    BOOLEAN;
 unr    NUMBER(5);
 rgt    BOOLEAN;
 retval BOOLEAN;
BEGIN
  retval := dbms_undo_adv.undo_info(tsn, tss, aex, unr, rgt);
  dbms_output.put_line('Undo Tablespace Is: ' || tsn);
  dbms_output.put_line('Undo Tablespace Size Is: ' || TO_CHAR(tss));

  IF aex THEN
    dbms_output.put_line('Undo Autoextend Is Set To: TRUE');
  ELSE
    dbms_output.put_line('Undo Autoextend Is Set To: FALSE');
  END IF;

  dbms_output.put_line('Undo Retention Is: ' || TO_CHAR(unr));

  IF rgt THEN
    dbms_output.put_line('Undo Guarantee Is Set To: TRUE');
  ELSE
    dbms_output.put_line('Undo Guarantee Is Set To: FALSE');
  END IF;
END;
/
 
Related Topics
Tablespaces
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [224 users online]    © 2010 psoug.org