On Sat, 27 Aug 2005 23:13:57 -0700, KDB wrote:

>
> Is it really true and oracle never told us about this ? Could you
> please share your 9i test case with me or point me to some URL?

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.
What that means is that Oracle optimizes 'COMMIT' statements in
the loop like above and simply doesn't generate signals to LGWR.
The whole thing was instigated by Jonathan, who had one day training
session in Hartford, CT in March this year. My testing only supports
Jonathan's statement.



--
http://www.mgogala.com

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

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.

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

I also tested this on oracle 9.2, 10g R1 and R2 but did not get any set
pattern. But definitely the result in all the cases was much lesser
io_submit calls than expected. I conducted my tests on a single CPU
linux box.
May be I am missing something here !

- KDB
http://itzkdb.blogspot.com

Mladen Gogala schrieb:

> 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

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

yong321@yahoo.com apparently said,on my timestamp of 30/08/2005 7:29 AM:


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

I dont'get this one. "depending" on what? There is no such thing
as a status of pl/sql block.

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

did you confirm that with the source code?


--
Cheers
Nuno Souto
in sunny Sydney, Australia
wizofoz2k@yahoo.com.au.nospam

Noons wrote:
> yong321@yahoo.com apparently said,on my timestamp of 30/08/2005 7:29 AM:
>
> > In 9i, 'log file
> > sync' wait should increment by 1 or 2 depending on how the PL/SQL block
> > with the loop is written.
>
> I dont'get this one. "depending" on what? There is no such thing
> as a status of pl/sql block.

I don't get it either!, because I'm not sure about the exact condition.
But it looks like using an implicit vs. explicit cursor influences the
number of log file sync waits. My test case is at
http://rootshell.be/~yong321/oranote...icitCommit.txt
(Any critique is very welcome.)

> > By the way, this has nothing to do with group commits
> > (commit-piggyback).
>
> did you confirm that with the source code?

I wish I had that luxury! But you can write a loop that sleeps in
between, which makes group commits impossible. If the log file sync
wait is still 1, you know that all but one commit triggers the wait:

select total_waits from v$session_event where event = 'log file sync'
and sid = <yoursid>;

create or replace procedure tstlogfilesync as
begin
for i in 1..10 loop
dbms_lock.sleep(1); --commenting out this line or not makes no
difference
insert into t values (i);
commit;
end loop;
end;
/

exec tstlogfilesync
select total_waits from v$session_event where event = 'log file sync'
and sid = <yoursid>;

My result on 9.2.0.5.0 is that log file sync wait increments by 1.

Yong Huang

yong321@yahoo.com wrote:
> I don't get it either!, because I'm not sure about the exact condition.
> But it looks like using an implicit vs. explicit cursor influences the
> number of log file sync waits. My test case is at
> http://rootshell.be/~yong321/oranote...icitCommit.txt

Thanks, will run it on my systems as well to see what gives.
The thing that worries me a bit is we may be classing this as
an undocumented optimization when it might well be something
else! The lack of any pattern to how it increments seems to
point to an "undocumented feature" anyway.
Not that it hasn't ever happened...

"Noons" <wizofoz2k@yahoo.com.au> wrote in message
news:1125456312.637905.86520@g43g2000cwa.googlegro ups.com...
> yong321@yahoo.com wrote:
>> I don't get it either!, because I'm not sure about the exact condition.
>> But it looks like using an implicit vs. explicit cursor influences the
>> number of log file sync waits. My test case is at
>> http://rootshell.be/~yong321/oranote...icitCommit.txt
>
> Thanks, will run it on my systems as well to see what gives.
> The thing that worries me a bit is we may be classing this as
> an undocumented optimization when it might well be something
> else! The lack of any pattern to how it increments seems to
> point to an "undocumented feature" anyway.
> Not that it hasn't ever happened...
>


Sorry to delay posting on this - I've been away from
home for a while, and only been able to look at Google
for news from time to time.

This is an undocumented optimization.
You need only look at the sessions 'redo synch writes'.

This counts the calls made to the log writer to
flush the log buffer and wait. It's always one -
which is the one call at the end of the database
call that contains the series of commits.

The randomness of the redo writes (which
probably matches the messages sent and
received) is likely to be a random timing
thing - apparently a session CAN send an
'unsynched' message to the log writer - and
various things might cause this to happen.


If you want to see the session forced to
send a 'redo synch write' on every commit
in the loop, then make the DML reference
the table through a loopback link - a distributed
transaction has to commit 'properly'.


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

Hi,
"Jonathan Lewis" <jonathan@jlcomp.demon.co.uk> wrote in message
news:df9tol$oli$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
>
> Sorry to delay posting on this - I've been away from
> home for a while, and only been able to look at Google
> for news from time to time.
>
> This is an undocumented optimization.
> You need only look at the sessions 'redo synch writes'.
>
> This counts the calls made to the log writer to
> flush the log buffer and wait. It's always one -
> which is the one call at the end of the database
> call that contains the series of commits.
>
> The randomness of the redo writes (which
> probably matches the messages sent and
> received) is likely to be a random timing
> thing - apparently a session CAN send an
> 'unsynched' message to the log writer - and
> various things might cause this to happen.

Could you please comment on how this optimization does not create a
possibility for data loss ? (An application thinks that a transaction has
committed while in fact it did not, and a crash occurs ...)

Thanks.
>
>
> If you want to see the session forced to
> send a 'redo synch write' on every commit
> in the loop, then make the DML reference
> the table through a loopback link - a distributed
> transaction has to commit 'properly'.
>
>
> --
> 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
>
>
>
>
>