Re: log_buffer sizing
- From: K R <kp0773@xxxxxxxxx>
- To: tim@xxxxxxxxx
- Date: Mon, 15 Aug 2011 16:13:08 -0700
Tim,
once in a while we see wait on the toad ( log file waiting to be archived )
and as the log buffer is only 20MB and the online logs are 3GB so we were
discussing if we can do as per any recommended sizing .
Thanks
Kart
On Mon, Aug 15, 2011 at 1:12 PM, Tim Gorman <tim@xxxxxxxxx> wrote:
> Kart,
>
> There is no magic bullet in that query, it was just for your information,
> so you can provide something more useful than the size of your online redo
> log files.
>
> So, asking again: what are you seeing that made you say, "the log buffer
> seems too small". From this query, you can see when redo generation gets
> high. During those times, are you seeing anything that indicates any kind
> of "bottleneck" in redo processing?
>
> Thanks!
>
> -Tim
>
> -----Original Message-----
> *From:* K R [mailto:kp0773@xxxxxxxxx]
> *Sent:* Monday, August 15, 2011 02:01 PM
> *To:* tim@xxxxxxxxx
> *Cc:* Oracle-L@xxxxxxxxxxxxx
> *Subject:* Re: log_buffer sizing
>
> Tim,
> this is a typical workload when the batch is running on the database.
>
> DAY HR NBR_SWITCHES GB
> ----------- ----- ------------ -----------
> 13-AUG-2011 00:00 12 3.84
> 01:00 12 8.46
> 02:00 30 51.52
> 03:00 38 60.86
> 04:00 36 61.56
> 05:00 43 72.49
> 06:00 43 55.48
> 07:00 12 0.45
> 08:00 12 0.03
> 09:00 12 0.26
> 10:00 12 6.42
> 11:00 12 3.89
> 12:00 12 0.25
> 13:00 12 2.35
> 14:00 12 0.04
> 15:00 13 1.90
> 16:00 41 72.73
> 17:00 32 55.27
> 18:00 37 44.95
> 19:00 12 0.10
> 20:00 12 0.05
> 21:00 12 0.05
> 22:00 12 3.65
> 23:00 14 16.64
> *********** ***** -----------
> sum 523.25
>
>
>
> On Mon, Aug 15, 2011 at 12:52 PM, Tim Gorman <tim@xxxxxxxxx> wrote:
>
>> Kart,
>>
>> 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',
>> inst_id
>> order by 1, 2, 3, 4;
>>
>>
>> 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@xxxxxxxxx]
>> *Sent:* Monday, August 15, 2011 01:22 PM
>> *To:* Oracle-L@xxxxxxxxxxxxx
>> *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
>>
>>
>
Other related posts:
