Re: Asynchronous Commit in Oracle Database 10g R2

Noons wrote:
> yong321_at_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/oranotes/CursorImplicitExplicitCommit.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 Received on Tue Aug 30 2005 - 09:12:09 CDT