Re: Slow LGWR performance with heavy redo log activity

"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..

Johnathan points out the logic as to why the INSERT is generating UNDO/REDO data. Another angle on it is to run iostat -xtc to see the log get hammered. Your best bet in this particular case is if possible reduce the data being inserted or use something like RAID with the cache dedicated 100% to write.

Also, if performance is paramount to you, ensure that you're using raw devices.

Thx!

--
Pablo Sanchez, High-Performance Database Engineering
mailto:pablo_at_hpdbe.com
http://www.hpdbe.com
Available for short-term and long-term contracts