SQL QUERY TO CALCULATE OPTIMAL UNDO RETENTION
Use this undo advisor query to calculate optimal undo retention time to hold the long running queries and for read consistency.
with UNDOSZE as
(select sum(bytes) UNDOMB from dba_data_files where FILE_NAME like '%undo%'),
UNDOBPS as
(select round(max(undoblks/((end_time-begin_time)*3600*24)),3) UNDOBPS from v$undostat),
DBBS as
(select value DBBS from v$parameter where name='db_block_size')
select round(UNDOMB / (UNDOBPS*DBBS)) as OPTIUNDORET from
undosze,
undobps,
dbbs;
SAMPLE OUTPUT:
kish@exdbx<>show parameter undo_retent
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_retention integer 900
kish@exdbx<>with UNDOSZE as
(select sum(bytes) UNDOMB from dba_data_files where FILE_NAME like '%undo%'),
UNDOBPS as
(select round(max(undoblks/((end_time-begin_time)*3600*24)),3) UNDOBPS from v$undostat),
DBBS as
(select value DBBS from v$parameter where name='db_block_size')
select round(UNDOMB / (UNDOBPS*DBBS)) as OPTIUNDORET from
undosze,
undobps,
dbbs; 2 3 4 5 6 7 8 9 10
OPTIUNDORET
----------------------------------------
35046