Re: Lots and lots of redo logs

I guess this test was in response to a claim that

  'you have to put your log files on the   inner/outer/middle section of the disc   for best response time.'

I think this demonstrates that there are never any easy answers - and rarely any conclusive tests other than real-life.

One thing you might want to check is whether the average 'redo write size' increases as the writes move through the redo logs (in the second case). You MIGHT be demonstrating that the original hypothesis is true, you MIGHT be demonstrating that in this case LGWR becomes self-balancing - as the disc access speed increases because it buffers more data and does bigger writes, so that the slower seek time is hidden by a side-effect of a larger write.

BTW - this is not a fair test of normal OLTP activity - some experiments I've been doing recently suggest that inside a PL/SQL loop the COMMIT does not cause a log file sync - if you check your

    redo synch writes
and

    redo writes
statistics, you may find that redo synch writes is extremely small (possibly 1, possibly 1 per log file) and you redo writes will almost certainly be less than the 15,000,000 commits you issued.

I'm still working on the 'commit is not a sync' thing, but it really does seem that in pl/sql a 'commit is complete' BEFORE lgwr has written to disc.

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

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research.

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 18 September 2001 23:11

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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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