Script - Check Current Undo Configuration and Advise Recommended Setup (Doc ID 1579035.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.7.0 and laterInformation in this document applies to any platform.
GOAL
This document is intended to check the current undo configuration and provides recommendation based on the previous workload.AUTION
This sample code is provided for diagnosis and troubleshooting purposes only, and is NOT supported by Oracle Support. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.
There are different scripts for database versions post 10g and prior to 10g. Ensure to execute the correct one to get the proper recommendation. Ensure to execute the script as SYS user.
SOLUTION
For database version 10g and above, use the following script. Ensure to execute the same as SYSDBA.
RDBMS version 10g and above:
SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';
DECLARE
v_analyse_start_time DATE := SYSDATE - 7;
v_analyse_end_time DATE := SYSDATE;
v_cur_dt DATE;
v_undo_info_ret BOOLEAN;
v_cur_undo_mb NUMBER;
v_undo_tbs_name VARCHAR2(100);
v_undo_tbs_size NUMBER;
v_undo_autoext BOOLEAN;
v_undo_retention NUMBER(5);
v_undo_guarantee BOOLEAN;
v_instance_number NUMBER;
v_undo_advisor_advice VARCHAR2(100);
v_undo_health_ret NUMBER;
v_problem VARCHAR2(1000);
v_recommendation VARCHAR2(1000);
v_rationale VARCHAR2(1000);
v_retention NUMBER;
v_utbsize NUMBER;
v_best_retention NUMBER;
v_longest_query NUMBER;
v_required_retention NUMBER;
BEGIN
select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
v_undo_info_ret := DBMS_UNDO_ADV.UNDO_INFO(v_undo_tbs_name, v_undo_tbs_size, v_undo_autoext, v_undo_retention, v_undo_guarantee);
select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_undo_tbs_name;
DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the database workload during the period -');
DBMS_OUTPUT.PUT_LINE('Begin Time : ' || v_analyse_start_time);
DBMS_OUTPUT.PUT_LINE('End Time : ' || v_analyse_end_time);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Current Undo Configuration');
DBMS_OUTPUT.PUT_LINE('--------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace',55) || ' : ' || v_undo_tbs_name);
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (datafile size now) ',55) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size (consider autoextend) ',55) || ' : ' || v_undo_tbs_size || 'M');
IF V_UNDO_AUTOEXT THEN
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : ON');
ELSE
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace is',55) || ' : OFF');
END IF;
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention',55) || ' : ' || v_undo_retention);
IF v_undo_guarantee THEN
DBMS_OUTPUT.PUT_LINE(RPAD('UNDO GUARANTEE is set to',55) || ' : TRUE');
ELSE
dbms_output.put_line(RPAD('UNDO GUARANTEE is set to',55) || ' : FALSE');
END IF;
DBMS_OUTPUT.PUT_LINE(CHR(9));
SELECT instance_number INTO v_instance_number FROM V$INSTANCE;
DBMS_OUTPUT.PUT_LINE('Undo Advisor Summary');
DBMS_OUTPUT.PUT_LINE('---------------------------');
v_undo_advisor_advice := dbms_undo_adv.undo_advisor(v_analyse_start_time, v_analyse_end_time, v_instance_number);
DBMS_OUTPUT.PUT_LINE(v_undo_advisor_advice);
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Undo Space Recommendation');
DBMS_OUTPUT.PUT_LINE('-------------------------');
v_undo_health_ret := dbms_undo_adv.undo_health(v_analyse_start_time, v_analyse_end_time, v_problem, v_recommendation, v_rationale, v_retention, v_utbsize);
IF v_undo_health_ret > 0 THEN
DBMS_OUTPUT.PUT_LINE('Minimum Recommendation : ' || v_recommendation);
DBMS_OUTPUT.PUT_LINE('Rationale : ' || v_rationale);
DBMS_OUTPUT.PUT_LINE('Recommended Undo Tablespace Size : ' || v_utbsize || 'M');
ELSE
DBMS_OUTPUT.PUT_LINE('Allocated undo space is sufficient for the current workload.');
END IF;
SELECT dbms_undo_adv.best_possible_retention(v_analyse_start_time, v_analyse_end_time) into v_best_retention FROM dual;
SELECT dbms_undo_adv.longest_query(v_analyse_start_time, v_analyse_end_time) into v_longest_query FROM dual;
SELECT dbms_undo_adv.required_retention(v_analyse_start_time, v_analyse_end_time) into v_required_retention FROM dual;
DBMS_OUTPUT.PUT_LINE(CHR(9));
DBMS_OUTPUT.PUT_LINE('Retention Recommendation');
DBMS_OUTPUT.PUT_LINE('------------------------');
DBMS_OUTPUT.PUT_LINE(RPAD('The best possible retention with current configuration is ',60) || ' : ' || v_best_retention || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The longest running query ran for ',60) || ' : ' || v_longest_query || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('The undo retention required to avoid errors is ',60) || ' : ' || v_required_retention || ' Seconds');
END;
/
Sample Output
- Undo Analysis started at : 30/08/2013 11:08:40 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 23/08/2013 11:08:40
End Time : 30/08/2013 11:08:40
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS2
Current undo tablespace size (datafile size now) : 20M
Current undo tablespace size (consider autoextend) : 20M
AUTOEXTEND for undo tablespace is : ON
Current undo retention : 900
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 26 MB
Undo Space Recommendation
-------------------------
Minimum Recommendation : Size undo tablespace to 26 MB
Rationale : Increase undo tablespace size so that long running queries will not fail
Recommended Undo Tablespace Size : 26M
Retention Recommendation
------------------------
The best possible retention with current configuration is : 9171 Seconds
The longest running query ran for : 2543 Seconds
The undo retention required to avoid errors is : 2543 Seconds
PL/SQL procedure successfully completed.
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 23/08/2013 11:08:40
End Time : 30/08/2013 11:08:40
Current Undo Configuration
--------------------------
Current undo tablespace : UNDOTBS2
Current undo tablespace size (datafile size now) : 20M
Current undo tablespace size (consider autoextend) : 20M
AUTOEXTEND for undo tablespace is : ON
Current undo retention : 900
UNDO GUARANTEE is set to : FALSE
Undo Advisor Summary
---------------------------
Finding 1:Undo Tablespace is under pressure. Recommendation 1:Size undo tablespace to 26 MB
Undo Space Recommendation
-------------------------
Minimum Recommendation : Size undo tablespace to 26 MB
Rationale : Increase undo tablespace size so that long running queries will not fail
Recommended Undo Tablespace Size : 26M
Retention Recommendation
------------------------
The best possible retention with current configuration is : 9171 Seconds
The longest running query ran for : 2543 Seconds
The undo retention required to avoid errors is : 2543 Seconds
PL/SQL procedure successfully completed.
RDBMS version 9i and below
Script
For 9i and below, please download the script.
Sample Output
--------------------------------------------------
- Undo Analysis started at : 28/08/2013 11:03:23 -
--------------------------------------------------
NOTE:The following analysis is based upon the peak database workload during the period -
Begin Time : 27/08/2013 21:56:36
End Time : 27/08/2013 22:06:36
----
Longest running query ran for : : 1368 Seconds
Current undo retention is : 900 Seconds
Current undo tablespace size is : 10M
AUTOEXTEND for undo tablespace size is : ON
----
Recommended undo retention is : 1369 Seconds
Recommended undo tablespace size is : 43M
----------------------------------------------------
- Undo Analysis completed at : 28/08/2013 11:03:23 -
----------------------------------------------------
PL/SQL procedure successfully completed.
- Undo Analysis started at : 28/08/2013 11:03:23 -
--------------------------------------------------
NOTE:The following analysis is based upon the peak database workload during the period -
Begin Time : 27/08/2013 21:56:36
End Time : 27/08/2013 22:06:36
----
Longest running query ran for : : 1368 Seconds
Current undo retention is : 900 Seconds
Current undo tablespace size is : 10M
AUTOEXTEND for undo tablespace size is : ON
----
Recommended undo retention is : 1369 Seconds
Recommended undo tablespace size is : 43M
----------------------------------------------------
- Undo Analysis completed at : 28/08/2013 11:03:23 -
----------------------------------------------------
PL/SQL procedure successfully completed.
REFERENCES
NOTE:877613.1 - AUM Common Analysis/Diagnostic ScriptsNOTE:1580225.1 - What is the Undo Advisor and how to Use it through the DBMS_UNDO_ADV package
=====================================================================
What is the Undo Advisor and how to Use it through the DBMS_UNDO_ADV package
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.4 [Release 10.2 to 11.2]Information in this document applies to any platform.
PURPOSE
Explain Undo Advisor and the options available
Automatic tuning of undo retention typically achieves better results with a fixed-size undo tablespace. If you decide to use a fixed-size undo tablespace, the Undo Advisor can help you estimate needed capacity.
You can access the Undo Advisor through Oracle Enterprise Manager (EM) or through the DBMS_ADVISOR PL/SQL package or through the DBMS_UNDO_ADV PL/SQL package.
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.
You can access the Undo Advisor through Oracle Enterprise Manager (EM) or through the DBMS_ADVISOR PL/SQL package or through the DBMS_UNDO_ADV PL/SQL package.
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.
An adjustment to the collection interval and retention period for AWR statistics can affect the precision and the type of recommendations that the advisor produces. See Oracle Database Performance Tuning Guide for more information.
Please refer the following document on how to modify the collection interval and retention period for the AWR :
How to Modify the statistics collection by MMON for AWR repository (Doc ID 308450.1)
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;
- 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;
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 .
Function rbu_migration is used to estimate the Undo Tablespace needed for migration from Manual To Automatic Undo management,
To migrate to automatic undo management, while using UNDO_MANAGEMENT=MANUAL (rollback segments).
If you are currently using manual undo management (rollback segments) to manage undo space, then Oracle recommends that you migrate to use automatic undo management. This change requires that you first create an undo tablespace before opening a newly upgraded database to use automatic undo management. The required size of undo tablespace depends upon the system workload and Flashback requirements.
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 migrate to automatic undo management, while using UNDO_MANAGEMENT=MANUAL (rollback segments).
If you are currently using manual undo management (rollback segments) to manage undo space, then Oracle recommends that you migrate to use automatic undo management. This change requires that you first create an undo tablespace before opening a newly upgraded database to use automatic undo management. The required size of undo tablespace depends upon the system workload and Flashback requirements.
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
=============================================================
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;
/
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
=====================================================================
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
=========================================================================
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
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.
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
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
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.
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
=====================================================================
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;
/
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;
/
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.
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;
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;
/
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;
/
DECLARE
reco VARCHAR2(300);
BEGIN
reco := dbms_undo_adv.undo_advisor(398, 409, 1);
dbms_output.put_line(reco);
END;
/
REFERENCES
NOTE:1579035.1 - Script - Check Current Undo Configuration and Advise Recommended SetupNOTE:308450.1 - How to Modify Statistics collection by MMON for AWR repository
=====================================================================
Comments
Post a Comment
Oracle DBA Information