Re: Lots and lots of redo logs

Bill Buchan wrote:
>
> Out of curiosity and as a test I scrubbed my 16Mb RAID1 array and set it up
> again with 5 x 50Mb redo groups. I ran the following script to generate
> lots of redo:
>
> begin
> for i in 1..15000000 loop
> insert into test_table values (mod(i,10));
> delete from test_table where anumber = (mod(i+1,10));
> commit;
> end loop;
> end;
>
> ARCHIVELOG mode was on and the archive destination (on another disk) was
> initially empty.
> There were 239 log switches and the test completed in 2 hours 56 minutes.
>
> I then added a further 250 50Mb redo groups, cleared the archive
> destination and ran the test again. Again it completed in exactly 2 hours
> 56 minutes, this time 240 log switches (difference of 1, I guess due to
> whereabouts in the first log it was when the test started).
>
> v$session_wait shows permanent "log file parallel write" for LGWR while the
> test runs.
>
> Any suggestions why I failed to see any slow down when I (almost) filled
> the disk with redo groups? (This server wasn't doing anything else and the
> time of the test). The RAID1 array had a Linux ext2 filesystem on it and I
> had disabled the write cache.
>
> Thanks again,
> - Bill.
>

Bill,

Is there any chance that you had the following:

• dictionary-managed USERS tablespace with small extent size (lots of recursize SQL)
• RBS, SYSTEM, USERS datafiles on same storage volume
• indexes on test_table, with INDEXES on same storage
• log_buffer at default value of 32K (or some other small value)

It seems likely that the bottleneck was not in the LGWR process. As others here would likely indicate, what were the other I/O-related v$system_event waits?

Since I don't see version info, is it possible that you do not have a dedicated CKPT process?

Paul

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Paul Drake
  INET: paled_at_home.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).