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.
Note: To make the undo tablespace fixed-size, Oracle suggests that you first allow enough time after database creation to run a full workload, thus allowing the undo tablespace to grow to its minimum required size to handle the workload. Then, you can use the Undo Advisor to determine, if desired, how much larger to set the size of the undo tablespace to allow for long-running queries and Oracle Flashback operations.
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 :
set serveroutput on
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 :
set serveroutput on
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.
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;
/
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.
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;
/
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).
Note : zero will be returned if the information about the given period is not available.
To Get The Output using the historical information in memory :
SELECT 'The Length of the Longest Query in Memory is ' || dbms_undo_adv.longest_query LONGEST_QUERY FROM dual;
To Get The Output using Start/End time :
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;
To Get The Output using Begin/End AWR snapshot id :
SELECT 'The Length of the Longest Query During This AWR snaps Range is ' ||dbms_undo_adv.longest_query(449, 451) LONGEST_QUERY FROM dual;
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 :
SELECT 'The Required undo_retention using Statistics In Memory is ' || dbms_undo_adv.required_retention required_retention FROM dual;
sample output :
The Required undo_retention using Statistics In Memory is 600
To Get The Output using Start/End time :
SELECT 'The Required undo_retention During This Time Range is ' ||dbms_undo_adv.required_retention(SYSDATE-1/24, SYSDATE) required_retention FROM dual;
To Get The Output using Begin/End AWR snapshot id :
SELECT 'The Required undo_retention During This AWR snaps Range is ' ||dbms_undo_adv.longest_query(449, 452) LONGEST_QUERY FROM dual;
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 : zero will be returned if the information about the given period is not available.
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 :
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;
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 :
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;
To Get The Output using Begin/End AWR snapshot id :
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 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)
Note : zero will be returned if the information about the given period is not available.
To Get The Output using the historical information in memory :
SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;
To Get The Output using Start/End time :
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;
sample output :
The Required undo tablespace size During This Time Range is 10 MB
To Get The Output using Begin/End AWR snapshot id :
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;
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 :
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;
/
To Get The Output using Begin/End AWR snapshot 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('=====================================================================');
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 :
set serveroutput on
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 :
set serveroutput on
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 :
set serveroutput on
DECLARE
reco VARCHAR2(300);
BEGIN
reco := dbms_undo_adv.undo_advisor(398, 409, 1);
dbms_output.put_line(reco);
END;
/
