Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Slow LGWR performance with heavy redo log activity
Archive log Mode ?
in any event the
"Xiao Feng QIAN" <steve_qian_at_sympatico.ca> wrote in message
news:muTN8.11363$Ju2.1680719_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 Thu Jun 13 2002 - 05:19:47 CDT