What happens during a commit?

From: Hans de Git <hansdegit_at_hotmail.com>
Date: Wed, 09 Apr 2003 00:43:41 -0800
Message-ID: <F001.0057DAB5.20030409004341@fatcity.com>

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. vmstat doesn't report IO waits, too.

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:

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.

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). Received on Wed Apr 09 2003 - 03:43:41 CDT