Re: What happens during a commit?

Hans de Git wrote:
>
> Hello,
>
> I'm trying to relieve the log file sync wait event in an Oracle database.
> We're on EMC and AIX, so we suspect no IO problems.

Interesting .....

>
> This is the situation:
> Many users issue many, many commits. My question is, does Oracle do a group
> commit, or is the sequence of events as follows:

a group commit, implies that multiple transactions can be written out. If there is only one TX, then group commit doesn't exists

> 1) session A issues a commit and goes to sleep.
> 2) LGWR wakes up.
> 3) LGWR acquires the redo allocation latch and redo copy latch.
> 4) LGWR flushes the redologbuffer to the logfiles (2 memebrs in parallel)
> 5) LGWR releases the redo latches
> 6) LGWR posts session A
>
> BTW: the transactions are very small (few bytes each, the redo wastage is
> 125M per day on a 500M redolog volume).
>
> If this is the sequence, then session B has to wait for all these steps to
> complete before its commit is handled by Oracle. If so, then with a little
> traffic analysis, it is fairly easy to calculate the maximum number of
> commits that Oracle can handle.
>
> Please explain.
>

Unless you increase the size of TX (do more work per commit), you will not see any improvement.  

> Thank you in anticipation.
>
> Regards,
>
> Hans de Git
>
> _________________________________________________________________
> MSN Zoeken, voor duidelijke zoekresultaten! http://search.msn.nl
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Hans de Git
> INET: hansdegit_at_hotmail.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Anjo Kolk
  INET: anjo_at_oraperf.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).