Re: Asynchronous Commit in Oracle Database 10g R2

Noons wrote:
> Mladen Gogala wrote:
> > It is my own experience. I created a table A(c1 number), executed
> > the loop from post above and counted "io_submit" calls.
> > Eventually, I counted the following:
> >
> > grep io_submit /tmp/lgwr.out|wc -l
> > 4408
> > And user commits (v$sesstat) was 5002. The obvious conclusion is
> > that not every commit has caused write request to be generated.
>
> But it doesn't have a regular pattern, does it? I mean: it's not
...
> I'm wondering if this is not a simple manifestation of the
> commit-piggyback that has been around since v6. Ie: if PL/SQL

I don't have 10gR2 installed. Instead of grep io_submit at the OS level which might include I/O submissions for other unrelated reasons, how much do you see 'log file sync' wait increments in v$session_event for the session? In 8i, it should increment by 1 for the entire loop regardless the number of times commit is executed (execution of all the commits can be verified by 'user commits' statistic). In 9i, 'log file sync' wait should increment by 1 or 2 depending on how the PL/SQL block is with the loop is written.

Can you do me a favor? What is _wait_for_sync set to when you set asynchronous commit to wait, batch and nowait?

By the way, this has nothing to do with group commits (commit-piggyback).

Yong Huang Received on Mon Aug 29 2005 - 16:29:09 CDT