calculate size of Undo tablespace and Undo Retention !

Formula:

Undo Size = Optimal Undo Retention × DB_BLOCK_SIZE × UNDO_BLOCK_REP_ESC


set linesize 200
col "OPTIMAL UNDO RETENTION [Sec]" format 99999999999
col "UNDO RETENTION [Sec]" format a20

SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
( SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/



SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) * g.undo_block_per_sec) / (1024*1024) "NEEDED UNDO SIZE [MByte]"
FROM ( SELECT SUM(a.bytes) undo_size
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#
) d,
v$parameter e,
v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec FROM v$undostat
) g
WHERE e.name = 'undo_retention'
AND f.name = 'db_block_size'
/


====

from Web



set serveroutput on
set feedback off
set heading off
set lines 132

declare
cursor get_undo_stat is
select d.undo_size/(1024*1024) "C1",
substr(e.value,1,25) "C2",
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) "C3",
round((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "C4"
from (select sum(a.bytes) undo_size
from v$datafile a,
v$tablespace b,
dba_tablespaces c
where c.contents = 'UNDO'
and c.status = 'ONLINE'
and b.name = c.tablespace_name
and a.ts# = b.ts#) d,
v$parameter e,
v$parameter f,
(select max(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec from v$undostat) g
where e.name = 'undo_retention'
and f.name = 'db_block_size';

begin

dbms_output.put_line(chr(10)||chr(10)||chr(10)||chr(10) || 'To optimize UNDO you have two choices :');
dbms_output.put_line('====================================================' || chr(10));

for rec1 in get_undo_stat loop
dbms_output.put_line('A)
Adjust UNDO tablespace size according to UNDO_RETENTION :'|| chr(10));

dbms_output.put_line(rpad('ACTUAL UNDO SIZE ',65,'.')|| ' : ' ||TO_CHAR(rec1.c1,'999999') || ' MEGS');

dbms_output.put_line(rpad('OPTIMAL UNDO SIZE WITH ACTUAL UNDO_RETENTION (' || ltrim(TO_CHAR(rec1.c2/60,'999999')) || ' MINUTES) ',65,'.') || ' : ' || TO_CHAR(rec1.c3,'999999') || ' MEGS');

dbms_output.put_line(chr(10));
dbms_output.put_line('B) Adjust UNDO_RETENTION according to UNDO tablespace size :' || chr(10));
dbms_output.put_line(rpad('ACTUAL UNDO RETENTION ',65,'.') ||' : ' || TO_CHAR(rec1.c2/60,'999999') || ' MINUTES');

dbms_output.put_line(rpad('OPTIMAL UNDO RETENTION WITH ACTUAL UNDO SIZE (' || ltrim(TO_CHAR(rec1.c1,'999999')) || ' MEGS)',65,'.') || ' : ' || TO_CHAR(rec1.c4/60,'999999') || ' MINUTES');

end loop;
dbms_output.put_line(chr(10)||chr(10));
end;
/




select 'Number of "ORA-01555 --Snapshot too old " encountered since the last startup of the instance : ' || sum(ssolderrcnt) from v$undostat;




set linesize 200
col "ACTUAL UNDO SIZE [MByte]" format 99999999
col "UNDO RETENTION [Sec]" format a25


apt-rdbms-01.us.co.uk:PTDB\sys> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
3 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
4 g.undo_block_per_sec) / (1024*1024)
5 "NEEDED UNDO SIZE [MByte]"
6 FROM (
7 SELECT SUM(a.bytes) undo_size
8 FROM v$datafile a,
9 v$tablespace b,
10 dba_tablespaces c
11 WHERE c.contents = 'UNDO'
12 AND c.status = 'ONLINE'
13 AND b.name = c.tablespace_name
14 AND a.ts# = b.ts#
15 ) d,
16 v$parameter e,
17 v$parameter f,
18 (
19 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
20 undo_block_per_sec
21 FROM v$undostat
22 ) g
23 WHERE e.name = 'undo_retention'
24 AND f.name = 'db_block_size'
25 /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
------------------------ ------------------------- ------------------------
800 900 34.2421875

apt-rdbms-01.tus.co.uk:APTDB\sys> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
3 ROUND((d.undo_size / (to_number(f.value) *
4 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
5 FROM (
6 SELECT SUM(a.bytes) undo_size
7 FROM v$datafile a,
8 v$tablespace b,
9 dba_tablespaces c
10 WHERE c.contents = 'UNDO'
11 AND c.status = 'ONLINE'
12 AND b.name = c.tablespace_name
13 AND a.ts# = b.ts#
14 ) d,
15 v$parameter e,
16 v$parameter f,
17 (
18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
19 undo_block_per_sec
20 FROM v$undostat
21 ) g
22 WHERE e.name = 'undo_retention'
23 AND f.name = 'db_block_size'
24 /


ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ ------------------------- ----------------------------
800 900 21027

we can increase the size of UNDO RETENTION to >> OPTIMAL UNDO RETENTION [Sec]



apt-rdbms-01.us.co.uk:TDB\sys> apt-rdbms-01.us.co.uk:TDB\sys> SELECT SUM(a.bytes)/1024/1024 "UNDO_SIZE MB"
FROM v$datafile a,
v$tablespace b,
dba_tablespaces c
WHERE c.contents = 'UNDO'
AND c.status = 'ONLINE'
AND b.name = c.tablespace_name
AND a.ts# = b.ts#;

UNDO_SIZE MB
------------
800



-- Switch back to the default mode.
ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;


apt-rdbms-01.tus.co.uk:APTDB\sys> SELECT tablespace_name, retention FROM dba_tablespaces;

TABLESPACE_NAME RETENTION
------------------------------ -----------
SYSTEM NOT APPLY
SYSAUX NOT APPLY
UNDOTBS1 NOGUARANTEE
USERS NOT APPLY
JTIME79 NOT APPLY
TEMP1 NOT APPLY
APTUS_OBJ NOT APPLY

7 rows selected.


DBA_ADVISOR_RECOMMENDATIONS
DBA_ADVISOR_FINDINGS
DBA_ADVISOR_ACTIONS
DBA_ADVISOR_OBJECTS


==

-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('970_1032_AWR_SNAPSHOT') AS report
FROM dual;
SET PAGESIZE 24




Select a.task_id, a.task_name, a.type, a.impact_type, a.impact,
b.benefit_type, c.message
from dba_advisor_findings a, dba_advisor_recommendations b,
dba_advisor_rationale c
where a.task_id=b.task_id
and a.finding_id=b.finding_id
and a.task_id=c.task_id
and a.task_name not like 'SYS_AUTO_SQL_TUNING%'
or a.task_name not like 'ADDM%'
or a.task_name not like '%ADDM%'
or a.task_name not like '%AWR_SNAPSHO%'
-- order by a.task_id, a.task_name, a.finding_id;

Declare
ret_val varchar2(2000);
PROCEDURE Show_Message(p_Msg_in IN CLOB)
IS
BEGIN
IF LENGTH(p_Msg_in) > 255
THEN
DBMS_OUTPUT.Put_Line(SUBSTR(p_Msg_in,1,255));
Show_Message(SUBSTR(p_Msg_in,256,LENGTH(p_Msg_in)));
ELSE
DBMS_OUTPUT.Put_Line(p_Msg_in);
END IF;
END;
begin
dbms_output.enable(1000000);
ret_val:=dbms_sqltune.report_tuning_task('TASK_00004');
show_message(ret_val);
end;
/




-- Display the report.
SET LONG 100000
SET PAGESIZE 50000
SELECT DBMS_ADVISOR.get_task_report('937_1135_AWR_SNAPSHOT_UNDO1') AS report FROM dual;
SET PAGESIZE 24




DECLARE
tid NUMBER;
tname VARCHAR2(30);
oid NUMBER;
BEGIN
DBMS_ADVISOR.CREATE_TASK('Undo Advisor', tid, tname, 'Undo Advisor Task');
DBMS_ADVISOR.CREATE_OBJECT(tname, 'UNDO_TBS', null, null, null, 'null', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'TARGET_OBJECTS', oid);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'START_SNAPSHOT', 937);
DBMS_ADVISOR.SET_TASK_PARAMETER(tname, 'END_SNAPSHOT', 1135);
DBMS_ADVISOR.execute_task(tname);
end;





SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name IN ('937_1135_AWR_SNAPSHOT_UNDO1')
ORDER BY f.task_name, f.impact DESC;



SET LINESIZE 250
COLUMN task_name FORMAT A20
COLUMN object_type FORMAT A20
COLUMN schema FORMAT A20
COLUMN object_name FORMAT A30
COLUMN object_name FORMAT A30
COLUMN message FORMAT A40
COLUMN more_info FORMAT A40

SELECT f.task_name,
f.impact,
o.type AS object_type,
o.attr1 AS schema,
o.attr2 AS object_name,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id
AND f.task_name = o.task_name
WHERE f.task_name IN ('Undo Advisor')
ORDER BY f.task_name, f.impact DESC;