如何通过DBMS_UNDO_ADV包使用undo顾问

原创笨鸟 All China Database Union 2022-04-16

1572

参考：文档 ID 1580225.1

获取连续Snapshot id

Note : The Instance must not have been shutdown between the times that the begin and end snapshots specified , To Get The continous snap shot IDs You can use the following Query :

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 2;
set pages 1000
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,'YYYY-MM-DD 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;

获取当前实例undo表空间信息

set serveroutput on
DECLARE
tbs_name    VARCHAR2(30);
tbs_size    NUMBER(10);
tbs_autoextend    BOOLEAN;
tbs_retention    NUMBER(5);
tbs_guarantee    BOOLEAN;
undo_adv BOOLEAN;
BEGIN
dbms_output.put_line('=====================================================================');
undo_adv := dbms_undo_adv.undo_info(tbs_name, tbs_size, tbs_autoextend, tbs_retention, tbs_guarantee);
If undo_adv=TRUE then
dbms_output.put_line('UNDO Tablespace Name : ' || tbs_name);
dbms_output.put_line('UNDO tablespace is '|| CASE WHEN tbs_autoextend THEN  'Auto Extensiable' ELSE 'Fixed Size' END);
If tbs_autoextend=TRUE then dbms_output.put_line('UNDO Tablespace Maximum size (MB) is : ' || TO_CHAR(tbs_size));
else dbms_output.put_line('UNDO Tablespace Fixed size (MB) is : ' || TO_CHAR(tbs_size));
end if;
dbms_output.put_line('Undo Retention is ' || TO_CHAR(tbs_retention)||' Seconds' ||' Equivelant to ' ||round((tbs_retention/60),2) ||' Minutes');
dbms_output.put_line('Retention : '||CASE WHEN tbs_guarantee THEN 'Guaranteed ' ELSE 'Not Guaranteed' END);
else dbms_output.put_line('Function undo_info can only run if parameters undo_management is auto');
end if;
dbms_output.put_line('=====================================================================');
END;
/

输出示例:
=====================================================================
UNDO Tablespace Name : UNDOTBS2
UNDO tablespace is Auto Extensiable
UNDO Tablespace Maximum size (MB) is : 6144
Undo Retention is 900 Seconds Equivelant to 15 Minutes
Retention : Not Guaranteed
=====================================================================

确定是否启用了undo保留期自动调整

set serveroutput on
DECLARE
tbs_autotune    BOOLEAN;
chk BOOLEAN;
BEGIN
tbs_autotune := dbms_undo_adv.undo_autotune(chk);
dbms_output.put_line('=========================================================================');
If tbs_autotune=TRUE then dbms_output.put_line('Auto Tuning of Undo Retention for the current Undo Tablespace is Enabled');
else dbms_output.put_line('Auto Tuning of Undo Retention for the current Undo Tablespace is Disabled');
end if;
dbms_output.put_line('=========================================================================');
end;
/

输出示例:
=========================================================================
Auto Tuning of Undo Retention for the current Undo Tablespace is Enabled
=========================================================================

检查给定时间内最长查询的长度

如果有关给定时间段的信息不可用，则将返回零。

-- 1.基于内存：
SELECT 'The Length of the Longest Query in Memory is ' || dbms_undo_adv.longest_query LONGEST_QUERY FROM dual;
-- 2.基于时间：
SELECT 'The Length of the Longest Query During This Time Range is ' ||dbms_undo_adv.longest_query(SYSDATE-1/24, SYSDATE) LONGEST_QUERY FROM dual;
-- 3.基于AWR快照：
SELECT 'The Length of the Longest Query During This AWR snaps Range is ' ||dbms_undo_adv.longest_query(449, 451) LONGEST_QUERY FROM dual;

输出示例:
LONGEST_QUERY
-------------------------------------------------------------------------------------
The Length of the Longest Query in Memory is 74639

满足最长查询所需的undo_retention

-- 1.基于内存：
SELECT 'The Required undo_retention using Statistics In Memory is ' || dbms_undo_adv.required_retention required_retention FROM dual;
-- 2.基于时间
SELECT 'The Required undo_retention During This Time Range is ' ||dbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual;
-- 3.基于快照
SELECT 'The Required undo_retention During This AWR snaps Range is ' ||dbms_undo_adv.longest_query(449, 452) LONGEST_QUERY FROM dual;

输出示例:
The best possible value for undo_retention the current undo tablespace can satisfy is 8208238

检查当前undo空间可以满足的最佳undo_retention

注意：如果有关给定时间段的信息不可用，则将返回零。
如果当前undo表空间是可自动扩展的，则返回的最佳可能保留值基于undo表空间可以增长到的最大大小。您可能不希望undo表空间增长到该大小。

-- 1.基于内存：
SELECT 'The best possible value for undo_retention the current undo tablespace can satisfy is ' || dbms_undo_adv.best_possible_retention best_retention FROM dual;
-- 2.基于时间
SELECT 'The best possible value for undo_retention the current undo tablespace can satisfy is ' ||dbms_undo_adv.best_possible_retention(SYSDATE-1/24, SYSDATE) best_retention FROM dual;
-- 3.基于快照
SELECT 'The best possible value for undo_retention the current undo tablespace can satisfy is ' ||dbms_undo_adv.best_possible_retention(578, 600) best_retention FROM dual;

输出示例:
The best possible value for undo_retention the current undo tablespace can satisfy is 8208238

满足特定undo保留期所需的undo空间大小

注意：如果有关给定时间段的信息不可用，则将返回零。
以 undo_retention=900,即15分钟为例：

-- 1.基于内存：
SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;
-- 2.基于时间
SELECT 'The Required undo tablespace size During This Time Range is ' || dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) || ' MB' required_undo_size FROM dual;
-- 3.基于快照
SELECT 'The Required Undo tablespace size During This AWR snaps Range is ' || dbms_undo_adv.required_undo_size(900,SYSDATE-1/24, SYSDATE) || ' MB' required_undo_size FROM dual;

检查当前undo_retention和undo表空间大小是否最佳

如果返回值为 0，则未发现问题。

基于内存

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('=====================================================================');
If retv=0 Then dbms_output.put_line('Problem: ' || prob || ' The undo tablespace is OK');
ELSIF retv=2 Then dbms_output.put_line('Long running queries may fail , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('retention: ' || TO_CHAR(retn));

ELSIF retv=3 Then dbms_output.put_line('The Undo tablespace cannot satisfy the longest query , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
dbms_output.put_line('retention: ' || TO_CHAR(retn));

ELSIF retv=4 Then dbms_output.put_line('The System does not have an online undo tablespace , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));

ELSIF retv=1 Then dbms_output.put_line('The Undo tablespace cannot satisfy the specified undo_retention or The Undo tablespace cannot satisfy auto tuning undo retention , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
end if;
dbms_output.put_line('=====================================================================');

END;
/

输出示例:
=====================================================================
The Undo tablespace cannot satisfy the specified undo_retention or The Undo tablespace cannot satisfy auto tuning undo retention , The recommendation is : Size
undo tablespace to 73987 MB
rationale: Increase undo tablespace size so that long running queries will not fail
undo tablespace size in MB : 73987
=====================================================================

2.基于时间

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('=====================================================================');
If retv=0 Then dbms_output.put_line('Problem: ' || prob || ' The undo tablespace is OK');
ELSIF retv=2 Then dbms_output.put_line('Long running queries may fail , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('retention: ' || TO_CHAR(retn));

ELSIF retv=3 Then dbms_output.put_line('The Undo tablespace cannot satisfy the longest query , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
dbms_output.put_line('retention: ' || TO_CHAR(retn));

ELSIF retv=4 Then dbms_output.put_line('The System does not have an online undo tablespace , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));

ELSIF retv=1 Then dbms_output.put_line('The Undo tablespace cannot satisfy the specified undo_retention or The Undo tablespace cannot satisfy auto tuning undo retention , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
end if;
dbms_output.put_line('=====================================================================');

END;
/

3.基于快照

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('=====================================================================');
If retv=0 Then dbms_output.put_line('Problem: ' || prob || ' The undo tablespace is OK');
ELSIF retv=2 Then dbms_output.put_line('Long running queries may fail , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('retention: ' || TO_CHAR(retn));

ELSIF retv=3 Then dbms_output.put_line('The Undo tablespace cannot satisfy the longest query , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
dbms_output.put_line('retention: ' || TO_CHAR(retn));

ELSIF retv=4 Then dbms_output.put_line('The System does not have an online undo tablespace , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));

ELSIF retv=1 Then dbms_output.put_line('The Undo tablespace cannot satisfy the specified undo_retention or The Undo tablespace cannot satisfy auto tuning undo retention , The recommendation is : ' || reco);
dbms_output.put_line('rationale: ' || rtnl);
dbms_output.put_line('undo tablespace size in MB : ' || TO_CHAR(utbs));
end if;
dbms_output.put_line('=====================================================================');

END;
/

检查当前实例是否存在任何问题并提供建议

当undo_management=AUTO时，可以使用此功能。
1.基于内存

set serveroutput on
-- 1 为实例id
DECLARE
reco VARCHAR2(300);
BEGIN
dbms_output.put_line('==========================================================');
reco := dbms_undo_adv.undo_advisor(1);
dbms_output.put_line(reco);
dbms_output.put_line('==========================================================');
END;
/