Oracle DBMS_UNDO_ADV
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose Undo advisor gives users recommendation on setting undo retention and sizing the undo tablespace.
AUTHID CURRENT_USER
Data Types CREATE OR REPLACE TYPE sys.dbms_uadv_arr AS VARRAY(100) OF NUMBER;
/
Dependencies
CDB_TABLESPACES DBMS_STANDARD PLITBLM
DBA_TABLESPACES DBMS_SYS_ERROR PRVTEMX_ADMIN
DBMS_ADVISOR DBMS_UADV_ARR PRVT_UADV
DBMS_SQL DBMS_UNDOADV_LIB UTL_LMS
Documented No
First Available 10.1
Overload Definitions
Error Code Reason
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 Owned by SYS with EXECUTE granted to the DBA role.
Source {ORACLE_HOME}/rdbms/admin/dbmsuadv.sql
Subprograms
BEST_POSSIBLE_RETENTION REQUIRED_RETENTION UNDO_AUTOTUNE
LONGEST_QUERY REQUIRED_UNDO_SIZE UNDO_HEALTH
RBU_MIGRATION UNDO_ADVISOR UNDO_INFO
 
BEST_POSSIBLE_RETENTION
Returns best possible value for the  init.ora parameter undo_retention in order to maxmize the usage of current undo tablespace based on historical information of given period
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;

DBMS_UNDO_ADV.BEST_POSSIBLE_RETENTION(SYSDATE-1/24,SYSDATE)
-----------------------------------------------------------
                                                  227584832
Overload 2 dbms_undo_adv.best_possible_retention RETURN NUMBER;
SELECT dbms_undo_adv.best_possible_retention
FROM dual;

BEST_POSSIBLE_RETENTION
-----------------------
                 907529
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;

Instance     DB Name        Snap Id   Snap Started        Level
------------ ------------ --------- --------------------- -----
test21db     TEST21DB          3295     28 APR 2021 00:58     1
                               3296     28 APR 2021 01:58     1
                               3297     28 APR 2021 02:58     1
...
                               3507     06 MAY 2021 18:58     1
                               3508     06 MAY 2021 19:58     1
                               3509     06 MAY 2021 20:58     1

SELECT dbms_undo_adv.best_possible_retention(3485, 3509)
FROM dual;

DBMS_UNDO_ADV.BEST_POSSIBLE_RETENTION(3485,3509)
------------------------------------------------
                                        14056602
 
LONGEST_QUERY
Returns the length of the longest query in seconds (between the start and end time)

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

DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-2,SYSDATE)
----------------------------------------------
                                         19662
Overload 2 dbms_undo_adv.longest_query RETURN NUMBER;
SELECT dbms_undo_adv.longest_query
FROM dual;

LONGEST_QUERY
-------------
         1921
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(3485, 3509)
FROM dual;

DBMS_UNDO_ADV.LONGEST_QUERY(3485,3509)
--------------------------------------
                                  1921
 
RBU_MIGRATION
Returns the required undo tablespace size if users want to migrate from rbu to aum. This function should be called only when undo management is manual
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;

DBMS_UNDO_ADV.RBU_MIGRATION(SYSDATE-1/24,SYSDATE)
-------------------------------------------------
                                               27
Overload 2 dbms_undo_adv.rbu_migration RETURN NUMBER;
SELECT dbms_undo_adv.rbu_migration
FROM dual;

RBU_MIGRATION
-------------
           27
 
REQUIRED_RETENTION
Returns the required value for init.ora parameter undo_retention in order to prevent snap-shot-too-old error based on historical information of given period

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;

DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-30,SYSDATE)
----------------------------------------------------
                                                1921

SELECT dbms_undo_adv.required_retention(SYSDATE-2000/1440, SYSDATE)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-2000/1440,SYSDATE)
-----------------------------------------------------------
                                                        253
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(3485, 3509)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_RETENTION(3485,3509)
-------------------------------------------
                                       1921
 
REQUIRED_UNDO_SIZE
Returns the required undo tablespace size, in MB, to support undo retention based on undo stats
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;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(900,SYSDATE-30,SYSDATE)
--------------------------------------------------------
                                                      96
Returns the required undo tablespace size, in MB, to support undo retention based on undo stats

Overload 2		 dbms_undo_adv.required_undo_size(retention IN NUMBER)
RETURN NUMBER;
SELECT dbms_undo_adv.required_undo_size(20)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(20)
------------------------------------
                                  10

SELECT dbms_undo_adv.required_undo_size(1440)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(1440)
--------------------------------------
                                   100
Returns the required undo tablespace size, in MB, to support undo retention based on undo stats

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, 3485, 3509)
FROM dual;

DBMS_UNDO_ADV.REQUIRED_UNDO_SIZE(1440,3485,3509)
------------------------------------------------
                                              73
Returns the required undo tablespace size given undo retention value

Overload 4		 dbms_undo_adv.required_undo_size(
retention IN     sys.dbms_uadv_arr,
utbsize   IN OUT sys.dbms_uadv_arr,
starttime IN     DATE,
endtime   IN     DATE)
RETURN NUMBER;
set serveroutput on

DECLARE
 uar sys.dbms_uadv_arr;
 utb sys.dbms_uadv_arr;
 ret NUMBER;
BEGIN
  uar := sys.dbms_uadv_arr(900,1800,3600);
  utb := sys.dbms_uadv_arr();
  ret := dbms_undo_adv.required_undo_size(uar, utb, SYSDATE-2, SYSDATE);
  dbms_output.put_line(ret);
  dbms_output.put_line(utb(1));
  dbms_output.put_line(utb(2));
  dbms_output.put_line(utb(3));
END;
/
1
42
42
42

PL/SQL procedure successfully completed.
Returns the required undo tablespace size given undo retention value

Overload 5		 dbms_undo_adv.required_undo_size(
retention IN     sys.dbms_uadv_arr,
utbsize   IN OUT sys.dbms_uadv_arr)
RETURN NUMBER;
set serveroutput on

DECLARE
 uar sys.dbms_uadv_arr;
 utb sys.dbms_uadv_arr;
 ret NUMBER;
BEGIN
  uar := sys.dbms_uadv_arr(900,1800,3600);
  utb := sys.dbms_uadv_arr();
  ret := dbms_undo_adv.required_undo_size(uar, utb);
  dbms_output.put_line(ret);
  dbms_output.put_line(utb(1));
  dbms_output.put_line(utb(2));
  dbms_output.put_line(utb(3));
END;
/
1
96
100
122

PL/SQL procedure successfully completed.
Returns the required undo tablespace size given undo retention value

Overload 6		 dbms_undo_adv.required_undo_size(
retention IN     sys.dbms_uadv_arr,
utbsize   IN OUT sys.dbms_uadv_arr,
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;

set serveroutput on

DECLARE
 uar sys.dbms_uadv_arr;
 utb sys.dbms_uadv_arr;
 ret NUMBER;
BEGIN
  uar := sys.dbms_uadv_arr(900,1800,3600);
  utb := sys.dbms_uadv_arr();
  ret := dbms_undo_adv.required_undo_size(uar, utb, 250, 270);
  dbms_output.put_line(ret);
  dbms_output.put_line(utb(1));
  dbms_output.put_line(utb(2));
  dbms_output.put_line(utb(3));
END;
/
 
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;
/
Finding 1:There is not enough data in AWR.

PL/SQL procedure successfully completed.
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;
/
Finding 1:The undo tablespace is OK.

PL/SQL procedure successfully completed.
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(3485, 3509, 1);
  dbms_output.put_line(retval);
END;
/
Finding 1:The undo tablespace is OK.

PL/SQL procedure successfully completed.
 
UNDO_AUTOTUNE
Determines if auto tuning of undo retention is enabled for the current undo tablespace. The output parameter is meaningful only when the return value is TRUE. dbms_undo_adv.undo_autotune(autotune_enabled OUT BOOLEAN)
RETURN BOOLEAN;
set serveroutput on

DECLARE
 bp BOOLEAN;
 br BOOLEAN;
BEGIN
  br := dbms_undo_adv.undo_autotune(bp);

  IF bp THEN
    dbms_output.put_line('Meaningful');
  ELSE
    dbms_output.put_line('Not Meaningful');
  END IF;

  IF br THEN
    dbms_output.put_line('True');
  ELSE
    dbms_output.put_line('False');
  END IF;
END;
/
Meaningful
True

PL/SQL procedure successfully completed.
 
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;
/
Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

PL/SQL procedure successfully completed.
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;
/
Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

PL/SQL procedure successfully completed.
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(3485, 3509, 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;
/
Problem: No problem found
Recmmnd:
Rationl:
Retentn: 0
UTBSize: 0

PL/SQL procedure successfully completed.
 
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;
/
Undo Tablespace is: UNDOTBS1
Undo Tablespace size is: 32768
Undo Autoextend is set to: TRUE
Undo Retention is: 900
Undo Guarantee is set to: FALSE

PL/SQL procedure successfully completed.

Related Topics
Built-in Functions
Built-in Packages
Oracle Enterprise Manager
Tablespaces
What's New In 21c
What's New In 23c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx