Optimal undo sizing

/*
Note: Only run this query after the database has
been running for a significant/representative
period of time.

Note2: This script is for when space is no issue and
you want to determine the optimal size based
on database activity and your desired
undo retention time.

Note3: If “NEEDED UNDO SIZE” is less than “ACTUAL UNDO SIZE”
then this is generally and indication of wasted space.
*/

set linesize 150
column “UNDO RETENTION (Secs)” format a25

select
d.undo_size/(1024*1024) “ACTUAL UNDO SIZE (MEGS)”,
substr(e.value,1,25) “UNDO RETENTION (Secs)”,
(to_number(e.value) * to_number(f.value) * g.undo_block_per_sec) / (1024*1024) “NEEDED UNDO SIZE (MEGS)”
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’;

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: