//////////////////////maximum time take to execute query in sec
SQL>select max(maxquerylen) from v$undostat;
//////////////////////////Needed UNDO Size in MB
SQL>SELECT
ROUND(d.undo_size/(1024*1024),2) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
ROUND((TO_NUMBER(e.value)*TO_NUMBER(f.value)*g.undo_block_per_sec)/(1024*1024),2) "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';
///////////////////////Optimal UNDO Retention in seconds
SQL>SELECT
ROUND(d.undo_size/(1024*1024),2) "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';
/////////////////////for change undo retention time
SQL>ALTER SYSTEM SET UNDO_RETENTION = [values based on your calculation];
/////////////////////////////number of error ora-01555
SQL>select 'Number of "ORA-01555 (Snapshot too old)" encountered since
the last startup of the instance : ' || sum(ssolderrcnt)
from v$undostat;
/////////////////////Actual undo tablespace size
SQL>SELECT SUM(a.bytes/1024/1024) 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#;
