oracle等待事件实验--log file

      测试插入数据单条循环提交和批量提交对日志等待事件的差异。

select event, total_waits, time_waited, average_wait  from v$system_eventwhere event like '%log file%';

create table TX_LOCK_TAB(  A NUMBER,  B CHAR(1),  C VARCHAR2(20));

--单条循环提交：declare i number;begin  for i in 1..2000000 loop   insert into tx_lock_tab(a,b,c) values(i,'c','aaa');   commit;  end loop;end;--批量提交：declare i number;begin  for i in 1..2000000 loop   insert into tx_lock_tab(a,b,c) values(i,'c','aaa');  end loop;  commit;end;

提交方式eventtotal_waitstime_waitedaverage_wait　log file parallel write728541159530.02批量提交log file parallel write729107174180.02　　56614650循环提交log file parallel write1377283260520.02　　64817686340　log file sequential read2661690.63批量提交log file sequential read2902000.69　　24310.06循环提交log file sequential read3502310.66　　6031-0.03　log file single write266500.19批量提交log file single write290700.24　　24200.05循环提交log file single write350750.21　　605-0.03　log file switch (checkpoint incomplete)2601188045.69批量提交log file switch (checkpoint incomplete)2981372846.07　　3818480.38循环提交log file switch (checkpoint incomplete)3021396246.23　　42340.16　log file switch completion271860631.76批量提交log file switch completion291974833.5　　2011421.74循环提交log file switch completion4111042225.36　　120674-8.14　log file sync7965540.7批量提交log file sync8055590.69　　95-0.01循环提交log file sync8435760.68　　3817-0.01
         log file parallel write、log file sync区别：前台进程a通知LGWR要刷log buffer了，这时候a就开始等待log file sync，LGWR接到a的请求后开始等待OS把log buffer写回redo log，这时候LGWR就开始等待log file parallel write，OS写完了通知LGWR，中止log file parallel write等待，接着LGWR通知a，中止log file sync等待。

         log file switch (checkpoint incomplete)：日志切换时产生的检查点事需要等待的，这个日志文件所对应脏块全部写完后，检查点进程更新控制文件和数据头，然后这个检查点才能完成。

         log file sequential read：发生在对redo log信息进行读取时，比如在线redo的归档操作，ARCH进程需要读取redo log的信息是顺序写入的，所以在读取时也是按照顺序的方式读取。

         log file single write：发生在更新redo log文件的文件头时，当为日志组增加新的日志成员时或redo log的sequence号改变时，LGWR都会更新redo log 文件头信息。

         log file switch completion：等待日志切换完成。