Re: Building Slow Development Systems (On Purpose)

------- Stephane Faroult <sfaroult@xxxxxxxxxxxx> wrote ----------
> 
> The sad thing is that I don't see how to make PL/SQL cursor loops 
> slower than they are ...
> 
> HTH
> 
> SF

Hi, Stephane,

If you need each log file sync for each commit inside PL/SQL, you can try 
commit write immediate wait if you're on 10g and up. The test below is on 
10.2.0.1 (my session is 92).

SQL> select total_waits, time_waited_micro from v$session_event where event = 
'log file sync' and sid = 92;

no rows selected

SQL> begin
  2    for i in 1..100 loop
  3      insert into t values (1,1,1);
  4      commit;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select total_waits, time_waited_micro from v$session_event where event = 
'log file sync' and sid = 92;

TOTAL_WAITS TIME_WAITED_MICRO
----------- -----------------
          1               132

SQL> begin
  2    for i in 1..100 loop
  3      insert into t values (1,1,1);
  4      commit write immediate wait;
  5    end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select total_waits, time_waited_micro from v$session_event where event = 
'log file sync' and sid = 92;

TOTAL_WAITS TIME_WAITED_MICRO
----------- -----------------
        101             30332

See the number of log file sync waits in the second case, and the 
corresponding increase in wait time.

Yong Huang


      
--
http://www.freelists.org/webpage/oracle-l

Other related posts: