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