Re: Poor LGWR (redo log) performance

My first guess would be that there is a problem with your disk system that REDO is on . The REDO is the hottest file in Oracle and can definitely impact insert performance if on a slow disk system.

Although I haven't worked with 9i yet, I have never seen a REDO log larger than 100Meg. It is possible this is a change in 9i that I am not aware of ? Although I would never take the default (using dbassistant for example) for REDO size, I think in 8i the default was something like 1/2 (.5) Meg.

So, I would also try reducing the REDO log to something like 50Meg.

The trade-off (before 9i anyway) was that larger REDO logs meant less ARCH activity, but possibly longer recovery for things like after a shutdown abort or turning off machine. After a shutdown abort, Oracle has to recover all activity in the current REDO and then rollback activity never committed. So, the worst case is that the REDO is almost full and 500Meg could take a while to recover .

"Xiao Feng QIAN" <steve_qian_at_sympatico.ca> wrote in message news:uvTN8.11364$Ju2.1681310_at_news20.bellglobal.com...
> 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 Fri Jun 14 2002 - 06:53:52 CEST