'user commits' statistic truthfully reflects user session commits even in a PL/SQL block. But log file sync waits occur only once (or twice) in PL/SQL. The following test is done in Oracle9.2.0.1 on XP SP1, but reproduced in 9.2.0.5 on XP, and in 8.1.7.0 on Linux, unless otherwise indicated. create table t (num number) nologging; create table junk nologging as select rownum rm from dba_objects; SQL> select count(*) from junk; COUNT(*) ----------- 28869 create or replace procedure expcurtst as cursor c is select rownum rn from junk; r c%rowtype; begin open c; loop fetch c into r; exit when c%notfound; insert into t values (r.rn); commit; end loop; end; / create or replace procedure impcurtst as begin for i in (select rownum rn from junk) loop insert into t values (i.rn); commit; end loop; end; / -- statistic# 4 is 'user commits' SQL> select * from v$mystat where statistic# = 4; SID STATISTIC# VALUE ----------- ----------- ----------- 10 4 3 SQL> exec impcurtst PL/SQL procedure successfully completed. SQL> select * from v$mystat where statistic# = 4; SID STATISTIC# VALUE ----------- ----------- ----------- 10 4 28872 <-- 28872-3=28869 (number of rows in junk) SQL> exec expcurtst PL/SQL procedure successfully completed. SQL> select * from v$mystat where statistic# = 4; SID STATISTIC# VALUE ----------- ----------- ----------- 10 4 57741 <-- 57741-28872=28869 again But log file sync wait only increments by 1 or 2 in a PL/SQL block regardless number of commits. SQL> conn yong/yong Connected. SQL> select total_waits, total_timeouts, time_waited, time_waited_micro from v$session_event where event = 'log file sync'; TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ----------- -------------- ----------- ----------------- 1 0 0 1231 ^TOTAL_WAITS is 1. Should add "and sid=" to where clause if multiple rows shown. SQL> exec impcurtst PL/SQL procedure successfully completed. SQL> select total_waits, total_timeouts, time_waited, time_waited_micro from v$session_event where event = 'log file sync'; TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ----------- -------------- ----------- ----------------- 3 1 103 1030953 ^TOTAL_WAITS for some reason increments by 2 in 9.2.0.1 and 9.2.0.5 on Windows XP, but remains unchanged in 8.1.7.0 on Linux! SQL> exec expcurtst PL/SQL procedure successfully completed. SQL> select total_waits, total_timeouts, time_waited, time_waited_micro from v$session_event where event = 'log file sync'; TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ----------- -------------- ----------- ----------------- 4 1 129 1286037 ^TOTAL_WAITS increments by 1 SQL> commit; <-- Commit without a transaction does not incur log file sync wait. Commit complete. SQL> select total_waits, total_timeouts, time_waited, time_waited_micro from v$session_event where event = 'log file sync'; TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ----------- -------------- ----------- ----------------- 4 1 129 1286037 SQL> insert into t values (1); 1 row created. SQL> select total_waits, total_timeouts, time_waited, time_waited_micro from v$session_event where event = 'log file sync'; TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ----------- -------------- ----------- ----------------- 4 1 129 1286037 SQL> commit; <-- SQL commit incurs one wait Commit complete. SQL> select total_waits, total_timeouts, time_waited, time_waited_micro from v$session_event where event = 'log file sync'; TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ----------- -------------- ----------- ----------------- 5 1 129 1287034 SQL> insert into t values (1); 1 row created. SQL> commit; Commit complete. SQL> select total_waits, total_timeouts, time_waited, time_waited_micro from v$session_event where event = 'log file sync'; TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ----------- -------------- ----------- ----------------- 6 1 129 1288021 SQL> begin insert into t values (1); commit; insert into t values (1); commit; end; <-- PL/SQL without loop 2 / PL/SQL procedure successfully completed. SQL> select total_waits, total_timeouts, time_waited, time_waited_micro from v$session_event where event = 'log file sync'; TOTAL_WAITS TOTAL_TIMEOUTS TIME_WAITED TIME_WAITED_MICRO ----------- -------------- ----------- ----------------- 7 1 129 1288897 Appendix: Session/instance abort, redo synch writes, commits, etc. (Reference: http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/ac0a0c68936ee5d6/a734c15e489286de?lnk=st&q=yong321&rnum=2&hl=en#a734c15e489286de) 'Redo synch writes' statistic increments by 1 regardless whether the cursor is implicit or explicit. Note:33622.1 says this statistic "should be close to user_commits. It may be less due to 'piggybacking' of commits". In fact, redo synch writes in our test is close to log file sync waits, and group commits (piggyback commits) are not involved in our test. Although log file sync wait and redo synch writes statistics increment by 1 (or 2) for the entire loop, each individual commit in the loop is successfully processed. Suppose session A runs: begin for i in 1..100 loop insert into t(a) values (i); dbms_lock.sleep(1); commit; end loop; end; / and session B kills session A before A finishes all 100 inserts. Table T will contain all the rows inserted up to the point A was killed. If you abort the instance, or simply kill the oracle process from OS, before A finishes, T still has all the rows already inserted. In both cases, v$sesstat and v$session_event show that 'user commits' keeps incrementing, but 'log file sync' and 'redo synch writes' remain unchanged (if there's none, it remains none). Therefore, although commits in a PL/SQL loop don't increment log file sync and redo synch writes,[note1] they're properly processed from the perspective of relational database transactions. ______________ [note1] They don't even increment by 1 because the session is killed before the loop is finished. It's also impossible for the increment to be done as late as at the time of session killing, because even killing the Windows oracle process (oracle.exe) at OS level makes no difference. We know that killing oracle.exe abruptly shuts down the database, not leaving a tiny bit of opportunity for Oracle to do any cleanup.