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 <[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
