Poor LGWR (redo log) performance

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 Thu Jun 13 2002 - 04:27:59 CEST