What is the Undo Advisor and how to Use it through the DBMS_UNDO_ADV package
(Doc ID 1580225.1)
Oracle Undo tablespaces were introduced in Oracle Database 9i, but not until Oracle Database 11g was the undo management mode set to
AUTO by default. Automatic undo management means that Oracle DBAs no longer need to manage rollback segments manually because the Oracle database itself will manage rollback segments in a single tablespace. UNDO_TABLESPACE parameter to determine which undo tablespace should be used; if it does not find the specified undo tablespace, the database will not start and an error will be generated in the database’s alert log. If the UNDO_TABLESPACE parameter is not being used, the database will try to find any undo tablespace in the database, and if it cannot find one, it will start to use rollback segments stored in the SYSTEM tablespace. This is not an optimal situation for database performance and is definitely not recommended. Again, in this situation, an alert message will be written to the database instance’s alert log.Tuning UNDO_RETENTION
The DBMS_UNDO_ADV package provides some excellent feedback to help determine the best value for the UNDO_RETENTION parameter:
SELECT DBMS_UNDO_ADV.LONGEST_QUERY(SYSDATE-1/24, SYSDATE)AS best_undo_time FROM dual;
BEST_U N DO_TIME
845
This output indicates that the longest-running query
on this instance took 845 seconds. Based on this output, then, 845 seconds is a good starting point for the undo retention parameter. Another query that can help identify an appropriate value for UNDO_RETENTION follows:
SELECT
DBMS_UNDO_ADV.REQUIRED_RETENTION(SYSDATE-30, SYSDATE) AS reqd_retn FROM dual;
REQD_RETN
1699
This query leverages the REQUIRED_RETENTION procedure of the DBMS_UNDO_ADV package to capture the execution time of the longest-running query in the last 30 days. If you use the same input parameter in the LONGEST_QUERY procedure of the first example, you will see that the same value will be returned.
Note
DBMS_UNDO_ADVISOR is an undocumented procedure. For more information about using it, see MOS Note 1580225.1, “What is the Undo Advisor and how to use it through the DBMS_UNDO_ADV package.” Much of this information is also available via the Undo Advisor in Enterprise Manager Database Console, Grid Control, and Cloud Control.
In This Document we will explain how to use the DBMS_UNDO_ADV PL/SQL package.
The package DBMS_UNDO_ADV is undocumented , and it is used internally by the Undo Advisor .
The Undo Advisor assists in correctly sizing the undo tablespace and to set the low threshold value of the undo retention period for any Oracle Flashback requirements.
The Undo Advisor can also be used to estimate the Undo Tablespace needed for migration from Manual To Automatic Undo management, before actually creating the new undo tablespace which will use automatic undo management.
The Undo Advisor relies for its analysis on data collected in the Automatic Workload Repository (AWR). It is therefore important that the AWR have adequate workload statistics available so that the Undo Advisor can make accurate recommendations. For newly created databases, adequate statistics may not be available immediately. In such cases, continue to use the default auto-extending undo tablespace until at least one workload cycle completes.
Running the Undo Advisor does not alter the size of the undo tablespace. The advisor just returns a recommendation. You must use ALTER DATABASE statements to change the tablespace data files to fixed sizes.
DETAILS
All the subprograms of package DBMS_UNDO_ADV have three versions , Except the undo_info , undo_autotune and rbu_migration Functions :
- Version 1: subprogram is based on historical information in memory or in AWR from start time to end time.
example : select dbms_undo_adv.required_undo_size(retention , start time , end time ) from dual;
- Version 2: subprogram is based on historical information in memory or in AWR from sysdate -7 to sysdate.
example : select dbms_undo_adv.required_undo_size(retention ) from dual;
- Version 3: subprogram is based on historical information in AWR from Begin/End snapshot id.
example : select dbms_undo_adv.required_undo_size(retention , snap_shot1 , snap_shot2 ) from dual;
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,'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;
The subprograms of package DBMS_UNDO_ADV and its Usage :
Estimate the Undo Tablespace Size needed for migration from Manual To Automatic Undo management .
Note : This function should be called only when undo_management = manual , however if undo_management = auto the function will not raise an error but the output is useless .
perform the following steps:
Start the instance and run through a standard business cycle to obtain a representative workload. Doing this to assess the workload and compute the size of the undo tablespace required for automatic undo management.
After the standard business cycle completes, run the following function to collect the undo tablespace size and help with the sizing of the undo tablespace (DBA privileges are required to run this function) .
Create an undo tablespace of the required size and turn on the automatic undo management by setting UNDO_MANAGEMENT=AUTO or by removing the parameter.
Note : For Oracle RAC configurations, repeat these steps on all instances.
To Get The Output using the historical information in memory :
DECLARE
utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION;
dbms_output.put_line('=================================================================');
dbms_output.put_line('The Minimum size of the undo tablespace required is : '||utbsiz_in_MB||' MB');
dbms_output.put_line('=================================================================');
end;
/
sample output :
=============================================================
The Minimum size of the undo tablespace required is : 13 MB
=============================================================
To Get The Output using Start/End time :
DECLARE
utbsiz_in_MB NUMBER;
BEGIN
utbsiz_in_MB := DBMS_UNDO_ADV.RBU_MIGRATION(SYSDATE-1/24, SYSDATE);
dbms_output.put_line('=================================================================');
dbms_output.put_line('The Minimum size of the undo tablespace During This Time Range is required is : '||utbsiz_in_MB||' MB');
dbms_output.put_line('=================================================================');
end;
/
Provide information about undo tablespace of the current instance .
Function undo_info is used to get information about undo tablespace of the current instance.
it returns undo tablespace name, max possible size, if it is auto extensible, current undo retention value and if the undo tablespace has guaranteed undo retention.
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;
/
sample output :
=====================================================================
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
=====================================================================
Determine if auto tuning of undo retention is enabled or not.
Function undo_autotune is used to find out if auto tuning of undo retention is enabled for the current undo tablespace or not.
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;
/
sample output :
=========================================================================
Auto Tuning of Undo Retention for the current Undo Tablespace is Enabled
=========================================================================
Check the length of the longest query for a given period .
Function longest_query returns the length of the longest query for a given period (Start/End time or Begin/End AWR snapshot id).
To Get The Output using the historical information in memory :
To Get The Output using Start/End time :
To Get The Output using Begin/End AWR snapshot id :
The required undo_retention to satisfy longest query .
Function required_retention returns the required value for parameter undo_retention to satisfy longest query based on undo statistics available for a given period (Start/End time or Begin/End AWR snapshot id)
To Get The Output using the historical information in memory :
sample output :
The Required undo_retention using Statistics In Memory is 600
To Get The Output using Start/End time :
To Get The Output using Begin/End AWR snapshot id :
Check best possible undo_retention the current undo tablespace can satisfy .
Function best_possible_retention returns best possible value for parameter undo_retention that the current undo tablespace can satisfy in order to maximize the usage of current undo tablespace based on the historical information of given period.
Note: If The current undo tablespace is auto-extensiable , the best possible retention value returned is based on the max size the undo tablespace can grow to. You may not want your undo tablespace to grow to that size to really satisfy this undo_retention value.
To Get The Output using the historical information in memory :
Sample Output :
The best possible value for undo_retention the current undo tablespace can satisfy is 8208238
To Get The Output using Start/End time :
To Get The Output using Begin/End AWR snapshot id :
The required undo tablespace size to satisfy certain undo retention value .
Function required_undo_size returns The required undo tablespace size in MB to satisfy certain undo retention value based on undo statistics available for a given period (Start/End time or Begin/End AWR snapshot id)
To Get The Output using the historical information in memory :
To Get The Output using Start/End time :
sample output :
The Required undo tablespace size During This Time Range is 10 MB
To Get The Output using Begin/End AWR snapshot id :
Verify current undo_retention and undo tablespace size (check whether its optimal).
Function undo_health is used to check if there is any problem with the current setting of undo_retention and undo tablespace size based on the historical information of given period , and provide recommendation to fix the problem.
If the return value is 0, no problem is found. Otherwise, parameter "problem" and "recommendation" are the problem and recommendation on fixing the problem.
The Output Parameters are :
problem: problem of the system. It can be for example : "long running query may fail" or "undo tablespace cannot satisfy undo_retention".
recommendation: recommendation on fixing the problem found.
rationale: rationale for the recommendation.
retention: numerical value of retention if recommendation is to change retention.
utbsize: numberical value of undo tablespace size in MB if recommendation is to change undo tablespace size.
To Get The Output using the historical information in memory :
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;
/
sample output :
=====================================================================
Problem: No problem found The undo tablespace is OK
=====================================================================
To Get The Output using Start/End time :
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;
/
To Get The Output using Begin/End AWR snapshot id :
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('=====================================================================');
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;
/
Check if there is any problem with the current instance and provide recommendations.
Function undo_advisor uses the advisor frame work to check if there is any problem with the current instance and provide recommendations.
This function should be used when undo_management is auto.
To Get The Output using the historical information in memory :
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;
/
Note : where 1 is the Isnatce ID which can be obtained from :
For RAC :
=========
SQL> select * from V$ACTIVE_INSTANCES;
For Non RAC :
==============
SQL> select INSTANCE_NUMBER ,INSTANCE_NAME from V$INSTANCE;
To Get The Output using Start/End time :
DECLARE
reco VARCHAR2(300);
BEGIN
reco := dbms_undo_adv.undo_advisor(SYSDATE-1/24, SYSDATE, 1);
dbms_output.put_line(reco);
END;
/
To Get The Output using Begin/End AWR snapshot id :
DECLARE
reco VARCHAR2(300);
BEGIN
reco := dbms_undo_adv.undo_advisor(398, 409, 1);
dbms_output.put_line(reco);
END;
/
No comments:
Post a Comment