Re: Building Slow Development Systems (On Purpose)

From: Yong Huang <yong321_at_yahoo.com>
Date: Thu, 28 May 2009 09:15:19 -0700 (PDT)
Message-ID: <297227.42619.qm_at_web80607.mail.mud.yahoo.com>

• Stephane Faroult <sfaroult_at_roughsea.com> 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

Received on Thu May 28 2009 - 11:15:19 CDT