Re: log_buffer sizing

What exactly are you seeing in the database that makes you say "it just seems too low"?

Bear in mind that the size of the online redo log files has no causal relation to the size of the log buffer, and vice-versa. What matters is the rate with which redo is written, and the frequency of COMMIT commands by the application.

For the first bit of information, try the following SQL*Plus script...

clear breaks
break on day skip 1 on hr on report
compute sum of gb on day
compute sum of gb on report
col sort0 noprint
col cnt format 999,990
col gb format 999,990.00
select trunc(completion_time,'HH24') sort0,  to_char(trunc(completion_time,'HH24'),'DD-MON-YYYY') day,  to_char(trunc(completion_time,'HH24'),'HH24')||':00' hr,  inst_id,
 count(*) nbr_switches,
 sum(blocks*block_size)/(1048576*1024) gb from gv$archived_log

group by trunc(completion_time,'HH24'),
 to_char(trunc(completion_time,'HH24'),'DD-MON-YYYY'),
 to_char(trunc(completion_time,'HH24'),'HH24')||':00',

That will give you some idea of the rate and volume. Of course, this query may end up double-counting redo volumes if you have additional archive destinations set up, so be aware of that and adjust accordingly.

Hope this helps...

Thanks!

-Tim

-----Original Message-----

From: K R [mailto:kp0773_at_gmail.com]
Sent: Monday, August 15, 2011 01:22 PM
To: Oracle-L_at_freelists.org
Subject: log_buffer sizing

All ,

my online redo logs are 3G . This is a batch intensive datawarehouse application. What should i be keeping my log_buffer .

currently it is set at 29425664 and it just seems too low .

thanks
Kart

--

http://www.freelists.org/webpage/oracle-l Received on Mon Aug 15 2011 - 14:52:27 CDT