Re: What happens during a commit?

As far as I know, a "redo sync write" is recorded when a session sends a message to the log writer to write.

The relationship between "redo sync writes" and "user commits" is not trivial - in theory (or 'in the good old days') every commit would result in a "redo synch write"; however with the advent of pl/sql, you can have multiple commits within a pl/sql block and a single "redo synch write" sent by your session only when the pl/sql ends and control returns to the client.

On the other hand, a "redo sync write" need NOT be produced by a "user commit" - for example a recursive space transaction will commit but not do a "user commit", and will cause a "redo synch write". Also, if you are looking at the system level stats rather than session level, DBWR may not write a dirty block unless the redo that effected the change has been written, so DBWR may issue a "redo synch write" to LGWR, but of course DBWR does not do commits.

Many people will tell you that 500MB per day is not a lot - and that any performance problem is unlikely to be affected by the usual raw / fs argument. However, it sounds from your previous comments as if you could be writing very small chunks of redo, which probably means less than a filesystem block - which can lead to undue write overheads. (The first question in this case is to ask if you are committing much too frequently, as this would be the root cause of any redo-related performance issue you might be seeing).

In this case, unless you have the facility for using directio at the filesystem level (i.e. bypassing the file system buffer) you may get some benefit from going to raw log files.

(I got the quote about questions and answers from Fabian Pascal's website www.dbdebunk.com )

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: 10 April 2003 18:29

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

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