log_file_sync io wait question

From: Pat Howe <PHowe_at_verisign.com>
Date: Wed, 18 Sep 2002 09:08:32 -0800
Message-ID: <F001.004D276F.20020918090832@fatcity.com>

We have a canned package that we use to insert approx 100 records/second into one table (oltp environment).
Each record is just under 1K (datatypes = number and varchar). There are several indexes that are build on the table as the records are inserted.
Each and every record is committed.
Therefore we are flushing the 'redo log buffer' to disk (online redo logfiles) 100 times per second (once per commit).

Not surprisingly we have noticed IO waits which we believe are associated with our 'Redo Log buffer'. Namely ;

log_file_sync = 180 waits/sec
log_file_parallel_write = 180 waits/sec

We tried resizing the 'redo log buffer' from 16K to 256K - but we did not notice any improvements.
Neither Log switching or archiving seem to be excessive. 100 records per second seems to be our maximum speed without the application queuing up and Oracle showing very high waits on log_file_sync and log_file_parallel_writes.

Does anyone know how we might be able to minimize the IO waits?

Thanks in advance.

ENVIRONMENT

oracle : Oracle 8.1.7.4
os     : Sun Sparc Solaris 8
box    : 8x8 E10K
IO     : Hitachi SANS unit through fiber and Brocade switch


_________________________

Patrick J. Howe

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Pat Howe
INET: PHowe_at_verisign.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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). Received on Wed Sep 18 2002 - 12:08:32 CDT