ORACLE Undo Advisor

最新推荐文章于 2022-05-23 16:25:22 发布

coutuo2575

最新推荐文章于 2022-05-23 16:25:22 发布

阅读量228

点赞数

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;

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;

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

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

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23721637/viewspace-1057304/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23721637/viewspace-1057304/

coutuo2575

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ORACLE Undo Advisor

General InformationPurposeUndo advisor gives users recommendation on setting undo retention and sizing undo tables...
复制链接

扫一扫