Re: Asynchronous Commit in Oracle Database 10g R2

"AnySQL (d.c.b.a)" <anysql_at_gmail.com> wrote in message news:1125978551.139964.163860_at_o13g2000cwo.googlegroups.com...
> Asynchronous may not be so effeciency as the log format changes, I have
> heard that oracle 10g can reduce the log generation in bulk
> update/delete, in earlier version, each updated/deleted row will have
> an log entry, as you know, in the log file, more than half of the bytes
> are the control information, by packing then togather just as bulk
> insert, it will greatly improve the bulk update/delte performance.
>
> http://www.anysql.net/en/
>

That's an interesting extra to throw into the discussion. 10.1 allows 'private redo threads', and bundles all the redo changes for a single "small" transaction into a single record which gets written from the private redo thread into the public redo thread on the commit.

(There are all sorts of extra details - like what constitutes a "small" transaction and what happens if a transaction gets too big, and how many private redo threads there
are and how things work if you can't acquire one).

The basic point, though is that for highly concurrent OLTP systems (lots of small
simultaneous transactions) 10.1 already
takes a lot of pressure off the redo allocation latch, the redo copy latch, and the redo buffer.

So you have to ask how much extra benefit does the async commit give you - and under what circumstances ?

The answer, I think, is probably in the area where you have a non-pl/sql routine that is doing the equivalent of the pl/sql loop with commits. I.e. the 3rd-party batch
process that isn't using bulk processing.

-- 
Regards

Jonathan Lewis

Now waiting on the publishers:    Cost Based Oracle - Volume 1

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

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 8th July 2005