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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s