Re: log file sync

This is because there is an undocumented feature of PL/SQL that it does not do a
log file sync on commit - it simply posts lgwr to write and continues - moreover, if your process is the only active one so that it has cycled round the loop before lgwr has responded to the post, your process will not even send a new write message on the grounds that the latest
redo/commit will be caught when lgwr finally catches up with the previous post.
Not only will your 'redo sync writes' be one per pl/sql call, your 'redo writes' will be less than your commits.

This is (I presume) to protect databases from programmers who produce fast loops with
single row inserts and commits.

You cannot approximate high-rate processes by using pl/sql loops - you may need to write a simple Pro*C/OCI program to do it.

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

Ethan Post wrote in message ...


>I always thought log file sync waits primary cause was commiting too often.

>We have a few jobs that run during the day and this is the primary wait

>event.  However during some of my testing regarding potential transaction

>rates for our server (see recent post) I wrote a script that inserts a

>record, commit's, deletes the record, commits for a large table.  I am

>getting 140 MB of redo per minute and over 50,000 commits per minute but no

>log file sync's.  Scratching my head on this one.  The file layout is

>optimal. - E

>

>