Oracle Logbook

June 30, 2013

Query to find out the ideal size for redo log

Filed under: Uncategorized — Helio Dias @ 4:06 am

It´s well know among DBAs that the redo log must few times  as possible, some say  that is more than 4 times per hour others says 2.

Now one must do is run the below query, adjusting the interval of switch (the 15 number) and voila, you will get a projection

of redo size per instance and group.


select Instancia “Instance”,g1 Group ,trunc(bytes/1024/1024/round((tempo2-tempo1)*60*24)*15,2) “Redo MB Projected for 15 switch”,bytes/1024/1024 “Actual Redo MB” from ( select t1.thread# Instancia, g1,t1.first_time tempo1,min(t2.first_time) tempo2 from v$log t1,v$log t2 where t1.thread#=t2.thread# and<> and t2.first_time>t1.first_time group by t1.thread#,,t1.first_time ) t1,v$log t2 where Instancia=thread# and g1=group# Order by 1,3;

If you want to improve this query, change the v$log to archives view, than you will have a wide view.

Hope it help.


Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

You are commenting using your 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

Create a free website or blog at

%d bloggers like this: