Oracle DBMS_UNDO

Oracle DBMS_UNDO_ADV Version 11.2.0.3

General Information

Purpose

Undo advisor gives users recommendation on setting undo retention and sizing the undo tablespace.

AUTHID

DEFINER

Data Types

CREATE OR REPLACE TYPE sys.dbms_uadv_arr AS VARRAY(100) OF NUMBER;
/

Dependencies

DBA_TABLESPACES	PLITBLM	V$PARAMETER
DBMS_SQL	PRVT_ADVISOR	V$ROLLSTAT
DBMS_SYS_ERROR	UTL_LMS	X$KSPPCV2
DBMS_UADV_ARR	V$DATABASE	X$KSPPI
DBMS_UNDOADV_LIB

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 with a PUBLIC synonym

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;

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;

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

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;

Overload 2

dbms_undo_adv.rbu_migration RETURN NUMBER;

SELECT dbms_undo_adv.rbu_migration
FROM dual;

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;

SELECT dbms_undo_adv.required_retention(SYSDATE-12/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;

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;

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;

SELECT dbms_undo_adv.required_undo_size(1440)
FROM dual;

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;

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;
/

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;
/

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;
/

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

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;
/

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;
/