Re: Slow LGWR performance with heavy redo log activity

Create Table As Select will run in nologging mode if the database is set to NOARCHIVELOG, so the total volume written to the database would be the pure size of the table in database blocks.

When doing the insert as select (without the /*+ append */ hint and on a table that is not declared as NOLOGGING, you will be generating UNDO and REDO. The total volume that will end up on disc is likely to be AROUND three times as much as it is for the CTAS - and your figures tend to fall in line with this.

--
Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminars
        UK            June / July
        Australia      July / August
http://www.jlcomp.demon.co.uk/seminar.html

Xiao Feng QIAN wrote in message ...


>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

>

>