Re: Asynchronous Commit in Oracle Database 10g R2

On Sun, 28 Aug 2005 23:38:22 -0700, Noons wrote:

> But it doesn't have a regular pattern, does it? I mean: it's not
> like Oracle is optimizing *every* single turn through the loop.
> If there truly is an optimization in 9i then it should be
> a regular, repeatable event, no?

Well, I don't know much about that optimization, but a "commit" should send a signal to the LGWR and wait until it completes the I/O operation. In particular, for each commit at least one I/O should happen. The fact that there are significantly less io_submit calls then user commits tells me that Oracle is "short-circuiting" something. You are right, I was unable to discover a pattern.

>
> I'm wondering if this is not a simple manifestation of the
> commit-piggyback that has been around since v6. Ie: if PL/SQL
> session somehow gets interrupted and there is a context switch,
> particularly in a multi-cpu environment, there is the possibility
> that logwriter would wake up, see "two" requests to commit from
> two sessions in two cpus and say: hang-on, I only need to commit
> once.

No, because that was the only active session in the database. It is my very own personal toy which I use on Sundays, when I have nothing better to do.

-- 
http://www.mgogala.com