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.
