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

Leave a Reply