Check Current Undo Configuration and Advise Recommended Setup

SET SERVEROUTPUT ON
SET LINES 600
ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS';

DECLARE
v_begin_time           varchar2(30);
v_end_time             varchar2(30);
v_max_qry_len          number;
v_unto_tbs             varchar2(100);
v_cur_undo_ret         number;
v_cur_undo_mb          number;
v_undo_autoext         varchar2(5);
v_cur_dt               date;
v_recommended_undo_mb  number;
v_analyse_start_time   date;
v_analyse_end_time     date;
BEGIN
select sysdate
into v_cur_dt
from dual;

select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('- Undo Analysis started at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('--------------------------------------------------');

SELECT begin_time,end_time into v_analyse_start_time, v_analyse_end_time
FROM v$undostat WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat);

DBMS_OUTPUT.PUT_LINE('NOTE:The following analysis is based upon the peak 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);


select min(BEGIN_TIME), max(end_time) into v_begin_time, v_end_time from v$undostat;
select max(MAXQUERYLEN) into v_max_qry_len from v$undostat;
select value into v_cur_undo_ret from v$parameter where name = 'undo_retention';
select value into v_unto_tbs from v$parameter where name = 'undo_tablespace';

SELECT ROUND((UR * (UPS * DBS))/1024/1024)
INTO v_recommended_undo_mb
FROM
 ( select max(MAXQUERYLEN) UR
from v$undostat) ,
 (SELECT undoblks/((end_time-begin_time)*86400) AS UPS
  FROM v$undostat
  WHERE undoblks =
  (SELECT MAX(undoblks)
FROM v$undostat)),
 (SELECT block_size AS DBS
  FROM dba_tablespaces
  WHERE tablespace_name =
  (SELECT UPPER(value)
FROM v$parameter
WHERE name = 'undo_tablespace'));


select sum(bytes)/1024/1024 into v_cur_undo_mb from dba_data_files where tablespace_name = v_unto_tbs;

SELECT CASE(select count(*) from dba_data_files
where tablespace_name = v_unto_tbs
and autoextensible = 'YES')
WHEN 0 THEN 'OFF'
ELSE 'ON' END
into v_undo_autoext
from dual;

DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('----');
DBMS_OUTPUT.PUT_LINE(RPAD('Longest running query ran for : ',40) || ' : ' || v_max_qry_len || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo retention is ',40) || ' : ' || v_cur_undo_ret || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('Current undo tablespace size is ',40) || ' : ' || v_cur_undo_mb || 'M');
DBMS_OUTPUT.PUT_LINE(RPAD('AUTOEXTEND for undo tablespace size is ',40) || ' : ' || v_undo_autoext);
DBMS_OUTPUT.PUT_LINE('----');
DBMS_OUTPUT.PUT_LINE(RPAD('Recommended undo retention is ',40) || ' : ' || TO_CHAR(v_max_qry_len+1) || ' Seconds');
DBMS_OUTPUT.PUT_LINE(RPAD('Recommended undo tablespace size is ',40) || ' : ' || v_recommended_undo_mb || 'M');

select sysdate into v_cur_dt from dual;
DBMS_OUTPUT.PUT_LINE(CHR(10));
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');
DBMS_OUTPUT.PUT_LINE('- Undo Analysis completed at : ' || v_cur_dt || ' -');
DBMS_OUTPUT.PUT_LINE('----------------------------------------------------');

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.