Re: What happens during a commit?

Thank you Jonathan.

So if I understand correctly, the 19 sessions from your example are serviced by one group commit. If that is the case in our production environment, then I would expect to see less redo sync writes than user commits. However, that is not the case. Au contraire: redo sync writes outnumber the user commits (how is that possible? is there a specific reason to do a sync write, other than a commit?).

So the wait time for log file sync is summed for all concurrent sessions. Given the fact that the redolog volume is about 500m a day, would raw volumes for our redologs provide us with a big performance gain?

hth,
Hans de Git

p.s. Nice quote, about questioning the answers...

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 09 Apr 2003 02:38:40 -0800

The best place to look for the details is probably Steve Adams' site www.ixora.com.au. There are various articles, notes and answers about the evolution of the redo write mechanism there.

In short, though, if you have 20 sessions that do a near simultaneous commit, you may find that LGWR wakes up in time to catch just the first one - checks how far up the buffer it can go in flushing the buffer (which has at least to get past the log for the session that got its commit call in), writes the buffer, and posts the session that it has committed.

At this point the lucky session commits. LGWR can then discover that there are
lots more commits in the message queue,
collect all the information for all the pending commits, and write a mass of log buffer that covers all those commits in one shot, then post all the current waiters.

Consequently, the system-level timing for 'log file sync' waits can be grossly misleading. In the above example, it is theoretically possible for the log writer to take 1/100 second to write and return from the first commit - leaving 19 sessions waiting for that commit; then the next write (which covers all 19, plus a few new sessions) could take another 1/100 second.

In just 2/100 elapsed, your system level wait time could be reported as 0.38 seconds.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

   The educated person is not the person    who can answer the questions, but the    person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: 09 April 2003 09:43

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

--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Lewis
   INET: jonathan_at_jlcomp.demon.co.uk

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





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