Re: Poor LGWR (redo log) performance

You log_buffer is too small and logs are sized too big..

Make your redo logs to something like 50m or 100m

Xiao Feng QIAN wrote:

> Hi, there:
>
> We are runing Oracle9i (9.0.1) on Sun OS 5.8, and we have set log_buffer to
> 1M,
> and redo log file size to 500M (3 redo log groups, 2 members each) for our
> batch jobs.
>
> And we noticed the POOR performance for a simple sql insert statement:
>
> insert into T2 (pkey) select pkey from T1 -- with 2.5 million rows
>
> will take over 7 minutes, which was bottlenecked at redo log (LGWR), i.e.
> log write slow, etc..
>
> Here are the stats (V$sysstat):
> redo synch writes 205
> redo synch time 27916
> redo entries 2519548
> redo size 993708316
> redo buffer allocation retries 5455
> redo wastage 744276
> redo writer latching time 0
> redo writes 3310
> redo blocks written 2003549
> redo write time 407539
> redo log space requests 13
> redo log space wait time 1286
> redo log switch interrupts 0
> redo ordering marks 2
>
> By comparison, it only took < 2 minute to do this by create table as select.
>
> We would appreciate any insight on why we have such bottleneck and how we
> could work around.
>
> Thanks
>
> Steve
Received on Sat Jun 15 2002 - 23:51:51 CEST