內容大綱：

1.分別用表和索引上數據的訪問來產生db file scattered read等待事件，等待事件需要在v$session_wait和10046 trace文件中顯示出來，貼出整個演示過程。<br>

2.構造一個DB File Sequential Read等待事件，等待事件需要在v$session_wait和10046 trace文件中顯示出來，貼出整個演示過程。<br>
3.構造一個Direct Path Read等待事件，等待事件需要在v$session_wait和10046 trace文件中顯示出來，貼出整個演示過程。<br>
4.構造一個Direct Path write等待事件，等待事件需要在v$session_wait和10046 trace文件中顯示出來，貼出整個演示過程。<br>
5.構造一個Log File Sync等待事件，并從相關視圖中找到等待事件的信息，貼出整個演示過程。<br>



############################################################################################
1.分別用表和索引上數據的訪問來產生db file scattered read等待事件，
等待事件需要在v$session_wait和10046 trace文件中顯示出來，貼出整個演示過程。<br>


答：

?? ?Db file scattered read
??? 這個等待事件在實際生產庫中經常可以看到，這是一個用戶操作引起的等待事件，
?? ?當用戶發出每次I/O需要讀取多個數據塊這樣的SQL 操作時，會產生這個等待事件，
?? ?最常見的兩種情況是全表掃描（FTS： Full Table Scan）
?? ?和索引快速掃描（IFFS： index fast full scan）。
?? ?下面我們從兩種情況進行測試


1.1設置跟蹤環境

SQL> alter session set sql_trace=true;

SQL> alter session set tracefile_identifier=TEST_TRACE;

SQL> alter session set events '10046 trace name context forever,level 12';

1.1 從表進行數據的訪問來產生db file scattered read等待事件

?? ?1.1.1建立測試數據，
?? ?create table t as select * from dba_objects;

?? ?1.1.2建立一個存儲過程，方便后面的測試
?? ??? ?create or replace procedure p1
?? ??? ?as
?? ??? ?v_a pls_integer;
?? ??? ?begin
?? ??? ?? for i in 1..8000
?? ??? ?? loop
?? ??? ??? ?select count(*) into v_a from t;
?? ??? ?? end loop;
?? ??? ?end;

?? ?1.1.2 打開一個窗口，執行前面建立的存儲過程，
?? ??? ?SQL> select distinct? sid from v$mystat;
?? ??? ??? ??? SID
?? ??? ?----------
?? ??? ??? ??? 103

?? ??? ?SQL> execute p1;

?? ?1.1.3 執行存儲過程的同時，查詢表v$session_wait ，可以看到，這時已看到等待事件了。

?? ??? ?SQL> select sid,event,wait_class,p1,p1text,p2,p2text,p3,p3text
?? ??? ? from v$session_wait where event like '%scattered%';
?? ??? ?
?? ??? ?---------------------------------------------------------------------
?? ??? ??? ?SID?? ?EVENT?? ?WAIT_CLASS?? ?P1?? ?P1TEXT?? ?P2?? ?P2TEXT?? ?P3?? ?P3TEXT
?? ??? ?1?? ?103?? ?db file scattered read?? ?User I/O?? ?4?? ?file#?? ?2495618?? ?block#?? ?83?? ?blocks

?? ??? ?
?? ??? ?跟蹤文件內容：
?? ??? ??? ??? ?
?? ??? ?PARSING IN CURSOR #380900088 len=15 dep=0 uid=84 oct=47 lid=84 tim=5892009907467 hv=541141916 ad='255cf19a8' sqlid='dbmqp4wh42aww'
?? ??? ?begin p1; end;
?? ??? ?END OF STMT
?? ??? ?PARSE #380900088:c=0,e=50,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=5892009907465
?? ??? ?EXEC #380896232:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=2966233522,tim=5892009907613
?? ??? ?WAIT #380896232: nam='db file sequential read' ela= 6672 file#=4 block#=2494362 blocks=1 obj#=91329 tim=5892009914356
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 4060 file#=4 block#=2494363 blocks=5 obj#=91329 tim=5892009918610
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 6280 file#=4 block#=2494368 blocks=8 obj#=91329 tim=5892009925054
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 198 file#=4 block#=2494377 blocks=7 obj#=91329 tim=5892009925453
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 160 file#=4 block#=2494384 blocks=8 obj#=91329 tim=5892009925785
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 158 file#=4 block#=2494393 blocks=7 obj#=91329 tim=5892009926121
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 142 file#=4 block#=2494400 blocks=8 obj#=91329 tim=5892009926430
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 162 file#=4 block#=2494409 blocks=7 obj#=91329 tim=5892009926770
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 154 file#=4 block#=2494416 blocks=8 obj#=91329 tim=5892009927089
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 186 file#=4 block#=2494425 blocks=7 obj#=91329 tim=5892009927457
?? ??? ?WAIT #380896232: nam='db file scattered read' ela= 127 file#=4 block#=2494432 blocks=8 obj#=91329 tim=5892009927754

?? ?我們也可以查看一下? select count(*) into v_a from t; 的執行計劃，
?? ?從下面的造勢計劃中可以看到，是進行的 全表搜索 TABLE ACCESS FULL

?? ?SQL> explain plan for select count(*)? from t;
?? ?Explained

?? ?SQL> select * from table(dbms_xplan.display(null,null,'typical'));
?? ?PLAN_TABLE_OUTPUT
?? ?--------------------------------------------------------------------------------
?? ?Plan hash value: 2966233522
?? ?-------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name | Rows? | Cost (%CPU)| Time???? |
?? ?-------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |????? |???? 1 |?? 303?? (1)| 00:00:04 |
?? ?|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??????????? |????????? |
?? ?|?? 2 |?? TABLE ACCESS FULL| T??? | 75994 |?? 303?? (1)| 00:00:04 |
?? ?-------------------------------------------------------------------
?? ?9 rows selected

?? ?SQL>



1.2 從索引進行數據的訪問來產生db file scattered read等待事件

?? ?
?? ?1.2.1建立索引及一個存儲過程，方便后面的測試

?? ??? ?create index idx_t_id on t(object_id)



?? ??? ?create or replace procedure p1
?? ??? ?as
?? ??? ?v_a pls_integer;
?? ??? ?begin
?? ??? ?? for i in 1..8000
?? ??? ?? loop
?? ??? ??? ?select count(object_id) into v_a from t;
?? ??? ?? end loop;
?? ??? ?end;


?? ?1.2.2 打開一個窗口，執行前面建立的存儲過程，
?? ??? ?SQL> select distinct? sid from v$mystat;
?? ??? ??? ??? SID
?? ??? ?----------
?? ??? ??? ??? 134

?? ??? ?SQL> execute p1;

?? ?1.2.3 執行存儲過程的同時，查詢表v$session_wait
?? ??? ?SQL> select sid,event,wait_class,p1,p1text,p2,p2text,p3,p3text
?? ??? ? from v$session_wait where event like '%scattered%';
?? ??? ?

?? ??? ??? ?SID?? ?EVENT?? ?WAIT_CLASS?? ?P1?? ?P1TEXT?? ?P2?? ?P2TEXT?? ?P3?? ?P3TEXT
?? ??? ?1?? ?134?? ?db file scattered read?? ?User I/O?? ?4?? ?file#?? ?2495874?? ?block#?? ?51?? ?blocks



?? ?1.2.4 從以下表中，查到到trace file 的目錄地址。
?? ??? ?select * from v$diag_info

?? ??? ??? ?INST_ID?? ?NAME?? ?VALUE
?? ??? ?4?? ?1?? ?Diag Trace?? ?D:\APP\ORACLE\diag\rdbms\orcl\orcl\trace


?? ?1.2.5 查看跟蹤文件，下面是跟蹤文件的一部分。


?? ??? ?orcl_ora_800_TEST_TRACE.trc
?? ??? ?-----------------------------------------------------
?? ??? ?PARSING IN CURSOR #441530520 len=31 dep=0 uid=84 oct=3 lid=84 tim=5889139614070 hv=750120931 ad='2af48f480' sqlid='75j4ttcqbbvz3'
?? ??? ?select count(object_id) from t
?? ??? ?END OF STMT
?? ??? ?PARSE #441530520:c=0,e=983,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=3570898368,tim=5889139614069
?? ??? ?EXEC #441530520:c=0,e=30,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3570898368,tim=5889139614151
?? ??? ?WAIT #441530520: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5889139614195
?? ??? ?WAIT #441530520: nam='SQL*Net message from client' ela= 413 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=5889139614647
?? ??? ?WAIT #441530520: nam='Disk file operations I/O' ela= 97 FileOperation=2 fileno=4 filetype=2 obj#=91333 tim=5889139614861
?? ??? ?WAIT #441530520: nam='db file sequential read'ela= 6755 file#=4 block#=2494626 blocks=1 obj#=91333 tim=5889139621640
?? ??? ?WAIT #441530520: nam='db file scattered read'ela= 3937 file#=4 block#=2494629 blocks=3 obj#=91333 tim=5889139625747
?? ??? ?WAIT #441530520: nam='db file scattered read' ela= 5895 file#=4 block#=2494632 blocks=8 obj#=91333 tim=5889139631961
?? ??? ?WAIT #441530520: nam='db file scattered read' ela= 328 file#=4 block#=2494641 blocks=7 obj#=91333 tim=5889139632962
?? ??? ?WAIT #441530520: nam='db file scattered read' ela= 667 file#=4 block#=2494648 blocks=8 obj#=91333 tim=5889139634222
?? ??? ?WAIT #441530520: nam='db file scattered read' ela= 163 file#=4 block#=2494657 blocks=7 obj#=91333 tim=5889139635013
?? ??? ?WAIT #441530520: nam='db file scattered read' ela= 197 file#=4 block#=2494664 blocks=8 obj#=91333 tim=5889139635762


**************************************************************************************************************************
2.構造一個DB File Sequential Read等待事件，等待事件需要在v$session_wait和10046 trace文件中顯示出來，貼出整個演示過程。<br>
答：

?? ?這個等待事件在實際生產庫也很常見，當Oracle 需要每次I/O只讀取單個數據塊這樣的操作時，會產生這個等待事件。
?? ?最常見的情況有索引的訪問（除IFFS外的方式），回滾操作，以ROWID的方式訪問表中的數據，重建控制文件，對文件頭做DUMP等。

?? ?是由于數據塊順序讀產生的，當數據塊(索引塊)從磁盤一個一個讀到內存中時，在這個過程中oracle會發生“db file sequential read” 等待事件。

?? ?塊順序讀場景：索引塊順序讀
?? ??? ??? ??? ?? 數據塊順序讀
?? ??? ??? ??? ?? undo回滾構造一致性讀
?? ??? ??? ??? ?? 磁盤I/O瓶頸

?? ?一般來講如果檢索數據時走索引范圍掃描INDEX RANGE SCAN 就會發生數據塊順序讀的現象，先讀取一個索引塊，根據索引鍵值對應ROWID信息在去讀ROWID所在的數據塊，接下來繼續找下一個索引塊，在讀對應的數據塊，就這樣一個一個把數據塊讀取到內存中，這個過程中就會產生“db file sequential read” 等待事件。


?? ?這里的sequential也并非指的是Oracle 按順序的方式來訪問數據，和db file scattered read一樣，
?? ?它指的是讀取的數據塊在內存中是以連續的方式存放的

?? ?2.1 演示

?? ?2.1.1 使用上面示例中的表及索引，修改存儲過程
?? ?加大循環次數及數據搜索范圍，可以更加容易的從表v$session_wait 中查詢到要的等待事件。
?? ?create or replace procedure p1
?? ?as
?? ?v_num pls_integer;
?? ?begin
?? ?? for i in 1..8000000
?? ?? loop
?? ??? ?select count(*) into v_num from t where object_id between 100 and 5000;
?? ??? ?
?? ?? end loop;
?? ?end;


?? ?2.1.2 查看執行計劃


?? ??? ?SQL> explain plan for select? count(*) from t where object_id between 100 and 5000;
?? ??? ?Explained

?? ??? ?SQL> select * from table(dbms_xplan.display(null,null,'typical'));
?? ??? ?PLAN_TABLE_OUTPUT
?? ??? ?--------------------------------------------------------------------------------
?? ??? ?Plan hash value: 1700799834
?? ??? ?------------------------------------------------------------------------------
?? ??? ?| Id? | Operation???????? | Name???? | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ??? ?------------------------------------------------------------------------------
?? ??? ?|?? 0 | SELECT STATEMENT? |????????? |???? 1 |???? 5 |??? 11?? (0)| 00:00:01 |
?? ??? ?|?? 1 |? SORT AGGREGATE?? |????????? |???? 1 |???? 5 |??????????? |????????? |
?? ??? ?|*? 2 |?? INDEX RANGE SCAN| IDX_T_ID |? 4079 | 20395 |??? 11?? (0)| 00:00:01 |
?? ??? ?------------------------------------------------------------------------------
?? ??? ?Predicate Information (identified by operation id):
?? ??? ?---------------------------------------------------
?? ??? ??? 2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=5000)
?? ??? ?14 rows selected

?? ?SQL> ?? ?

?? ?
?? ?2.1.4 執行存儲過程

?? ?SQL> alter session set tracefile_identifier=att_;
?? ?Session altered

?? ?SQL> alter system flush buffer_cache;
?? ?System altered

?? ?SQL> execute p1;


?? ?2.1.5 查詢v$session_wait


?? ? select sid,event,wait_class,p1,p1text,p2,p2text,p3,p3text
?? ? from v$session_wait where event like '%sequential%';
?? ?----------------------------------------------------------------------------------
?? ??? ?SID?? ?EVENT?? ?WAIT_CLASS?? ?P1?? ?P1TEXT?? ?P2?? ?P2TEXT?? ?P3?? ?P3TEXT
?? ?1?? ?103?? ?db file sequential read?? ?User I/O?? ?4?? ?file#?? ?2494638?? ?block#?? ?1?? ?blocks


?? ?2.1.6 打開對應的跟蹤文件，可以看到數據讀取的方法

?? ?PARSING IN CURSOR #380999792 len=15 dep=0 uid=84 oct=47 lid=84 tim=5893932794402 hv=541141916 ad='255cf19a8' sqlid='dbmqp4wh42aww'
?? ?begin p1; end;
?? ?END OF STMT
?? ?PARSE #380999792:c=0,e=106,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=5893932794401
?? ?EXEC #381158104:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=1700799834,tim=5893932794555
?? ?WAIT #381158104: nam='db file sequential read' ela= 118 file#=4 block#=2494627 blocks=1 obj#=93207 tim=5893932794737
?? ?WAIT #381158104: nam='db file sequential read' ela= 107 file#=4 block#=2494628 blocks=1 obj#=93207 tim=5893932794901
?? ?WAIT #381158104: nam='db file sequential read' ela= 82 file#=4 block#=2494629 blocks=1 obj#=93207 tim=5893932795236
?? ?WAIT #381158104: nam='db file sequential read' ela= 157 file#=4 block#=2494630 blocks=1 obj#=93207 tim=5893932795524
?? ?WAIT #381158104: nam='db file sequential read' ela= 115 file#=4 block#=2494631 blocks=1 obj#=93207 tim=5893932795772
?? ?WAIT #381158104: nam='db file sequential read' ela= 105 file#=4 block#=2494632 blocks=1 obj#=93207 tim=5893932796007
?? ?WAIT #381158104: nam='db file sequential read' ela= 81 file#=4 block#=2494633 blocks=1 obj#=93207 tim=5893932796216
?? ?WAIT #381158104: nam='db file sequential read' ela= 51 file#=4 block#=2494634 blocks=1 obj#=93207 tim=5893932796397
?? ?WAIT #381158104: nam='db file sequential read' ela= 48 file#=4 block#=2494635 blocks=1 obj#=93207 tim=5893932796565
?? ?WAIT #381158104: nam='db file sequential read' ela= 47 file#=4 block#=2494636 blocks=1 obj#=93207 tim=5893932796731
?? ?WAIT #381158104: nam='db file sequential read' ela= 47 file#=4 block#=2494637 blocks=1 obj#=93207 tim=5893932796894
?? ?WAIT #381158104: nam='db file sequential read' ela= 62 file#=4 block#=2494638 blocks=1 obj#=93207 tim=5893932797074
?? ?FETCH #381158104:c=15600,e=3182,p=12,cr=12,cu=0,mis=0,r=1,dep=1,og=1,plh=1700799834,tim=5893932797773


**************************************************************************************************************************
3.構造一個Direct Path Read等待事件，等待事件需要在v$session_wait和10046 trace文件中顯示出來，貼出整個演示過程。<br>
?? ?答：

?? ?Direct path read 等待事件：發生在“重新讀取”的時候，因為排好序數據是一種中間狀態，作為計算的中間值存在，不會放在SGA中共享，直接讀取到會話私有PGA中，一般是PGA的sort area區

?? ?場景：排好序數據會優先放在PGA中，當PGA裝滿時，就會被寫入到磁盤的temp表空間里，當發生“重新讀取”的時候，磁盤上的數據不會經過SGA的內存區，而是直接讀取到PGA內存區里，在這個過程中將會發生“direct path read”等待事件
?? ?
?? ?下面我們就在查詢中添加order by 來產生? Direct Path Read等待事件。


?? ?3.1 先查看一下執行計劃，是否會有order by
?? ?因為此等待事件只有在PGA不夠用時，才會寫到臨時表空間中，也只有重新讀取時，才會發生“direct path read”等待事件
?? ?所以這里我用了一個特大表做排序查詢，并以此來做測試
?? ?

?? ?SQL> explain plan for select * from company c order by c.company_name;
?? ?Explained

?? ?SQL> select * from table(dbms_xplan.display(null,null,'typical'));
?? ?PLAN_TABLE_OUTPUT
?? ?--------------------------------------------------------------------------------
?? ?Plan hash value: 779810067
?? ?--------------------------------------------------------------------------------
?? ?| Id? | Operation????????? | Name????????? | Rows? | Bytes | Cost (%CPU)| Time
?? ?--------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT?? |?????????????? |???? 1 | 15814 |?? 171K? (1)| 00:34:
?? ?|?? 1 |? SORT ORDER BY???? |?????????????? |???? 1 | 15814 |?? 171K? (1)| 00:34:
?? ?|?? 2 |?? TABLE ACCESS FULL|??? COMPANY??? |???? 1 | 15814 |?? 171K? (1)| 00:34:
?? ?--------------------------------------------------------------------------------
?? ?9 rows selected

?? ?SQL> explain plan for select * from tang.mt_company_bg c order by c.company_name;
?? ?Explained


?? ?3.2 執行查詢
?? ?SQL> alter system flush buffer_cache;
?? ?System altered

?? ?SQL> alter session set tracefile_identifier=p3_test;
?? ?Session altered

?? ?SQL> select * from tang.mt_company_bg c order by c.company_name;
?? ?select * from tang.mt_company_bg c order by c.company_name


?? ?3.3 查詢等待事件表：v$session_wait

?? ??? ?select sid,event,wait_class,p1,p1text,p2,p2text,p3,p3text
?? ?from v$session_wait where event like '%direct%';
?? ?
?? ?---------------------------------------------------------------------------------------
?? ??? ?SID?? ?EVENT?? ?WAIT_CLASS?? ?P1?? ?P1TEXT?? ?P2?? ?P2TEXT?? ?P3?? ?P3TEXT
?? ?1?? ?15?? ?direct path read?? ?User I/O?? ?4?? ?file number?? ?2526592?? ?first dba?? ?128?? ?block cnt


?? ?3.4 查看跟蹤文件
?? ?從跟蹤文件中可以看出，系統是讀一些數據，再把這些已讀出的數據保存到臨時表空間中。

?? ?PARSING IN CURSOR #479568264 len=59 dep=0 uid=84 oct=3 lid=84 tim=5895595649849 hv=3518044383 ad='2afaa79d0' sqlid='cfmzuar8v266z'
?? ?select * from tang.mt_company_bg c order by c.company_name
?? ?END OF STMT
?? ?PARSE #479568264:c=0,e=1660,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=779810067,tim=5895595649847
?? ?EXEC #479568264:c=0,e=34,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=779810067,tim=5895595649936
?? ?WAIT #479568264: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=5895595649997
?? ?WAIT #479568264: nam='SQL*Net message from client' ela= 1743 driver id=1413697536 #bytes=1 p3=0 obj#=0 tim=5895595651814
?? ?WAIT #479568264: nam='db file sequential read' ela= 5222 file#=4 block#=2501786 blocks=1 obj#=93074 tim=5895595657218
?? ?WAIT #479568264: nam='direct path read' ela= 283 file number=4 first dba=2501787 block cnt=5 obj#=93074 tim=5895595657739
?? ?WAIT #479568264: nam='direct path read' ela= 3285 file number=4 first dba=2502016 block cnt=8 obj#=93074 tim=5895595661414
?? ?WAIT #479568264: nam='direct path read' ela= 1674 file number=4 first dba=2502025 block cnt=15 obj#=93074 tim=5895595663640

?? ?*** 2013-11-07 17:07:24.062
?? ?WAIT #479568264: nam='direct path read' ela= 1020 file number=4 first dba=2506498 block cnt=126 obj#=93074 tim=5895596006835

?? ?*** 2013-11-07 17:07:25.263
?? ?WAIT #479568264: nam='direct path write temp' ela= 180 file number=201 first dba=414854 block cnt=31 obj#=93074 tim=5895597207754
?? ?WAIT #479568264: nam='direct path write temp' ela= 233 file number=201 first dba=415226 block cnt=31 obj#=93074 tim=5895597210304
?? ?WAIT #479568264: nam='direct path read' ela= 32578 file number=4 first dba=2519040 block cnt=128 obj#=93074 tim=5895597252651
?? ?WAIT #479568264: nam='direct path read' ela= 2316 file number=4 first dba=2519296 block cnt=128 obj#=93074 tim=5895597263932

**************************************************************************************************************************
4.構造一個Direct Path write等待事件，等待事件需要在v$session_wait和10046 trace文件中顯示出來，貼出整個演示過程。<br>

?? ?Direct path write 等待事件：發生在“寫入磁盤”的時候，因為寫也是一種中間狀態，只要是中間狀態數據就沒有必要共享給其他用戶，所以這些數據也不會放在SGA中共享，從PGA中 -> 直接寫入 -> 磁盤。

?? ?場景： append方式插入數據，這種方式插入數據的時候會跳過SGA的data_buffer_cache，直接插入數據文件，并且還不會掃描數據文件中的空閑空間直接插入到文件尾所以效率較高

?? ??? ??? 當數據排序時候，如果PGA被裝滿，就會被寫入到磁盤的temp表空間里，因為排序的數據是中間狀態也不會經過SGA，所以會產生“direct path write”等待事件


?? ?延用上面的測試環境，刷新查詢 等待事件表，可以看到，“direct path write temp”，“direct path read”交替出現。

?? ?select sid,event,wait_class,p1,p1text,p2,p2text,p3,p3text
?? ?from v$session_wait where event like '%direct%';
?? ?--------------------------------------------------------------------------------------------
?? ??? ?SID?? ?EVENT?? ?WAIT_CLASS?? ?P1?? ?P1TEXT?? ?P2?? ?P2TEXT?? ?P3?? ?P3TEXT
?? ?1?? ?103?? ?direct path write temp?? ?User I/O?? ?201?? ?file number?? ?608287?? ?first dba?? ?31?? ?block cnt


?? ?跟蹤文件內容，可以截取另外一個時間段，如下：

?? ?*** 2013-11-07 17:07:26.075
?? ?WAIT #479568264: nam='direct path write temp' ela= 147 file number=201 first dba=425270 block cnt=31 obj#=93074 tim=5895598014898
?? ?WAIT #479568264: nam='direct path write temp' ela= 41 file number=201 first dba=425642 block cnt=31 obj#=93074 tim=5895598017466
?? ?WAIT #479568264: nam='direct path read' ela= 38126 file number=4 first dba=2526592 block cnt=128 obj#=93074 tim=5895598069903
?? ?WAIT #479568264: nam='direct path read' ela= 3877 file number=4 first dba=2526976 block cnt=128 obj#=93074 tim=5895598087960
?? ?WAIT #479568264: nam='direct path read' ela= 3096 file number=4 first dba=2527232 block cnt=128 obj#=93074 tim=5895598106142
?? ?WAIT #479568264: nam='direct path write temp' ela= 80 file number=201 first dba=426417 block cnt=31 obj#=93074 tim=5895598128771
?? ?WAIT #479568264: nam='direct path read' ela= 35742 file number=4 first dba=2527744 block cnt=128 obj#=93074 tim=5895598182055
?? ?WAIT #479568264: nam='direct path read' ela= 963 file number=4 first dba=2528000 block cnt=128 obj#=93074 tim=5895598197900



**************************************************************************************************************
5.構造一個Log File Sync等待事件，并從相關視圖中找到等待事件的信息，貼出整個演示過程。<br> 邊城日志20131107

?? ?答：
?? ?這是一個用戶會話行為導致的等待事件，當一個會話發出一個commit命令時，
??? LGWR進程會將這個事務產生的redo log從log buffer里面寫到磁盤上，以確保用戶提交的信息被安全地記錄到數據庫中。
?? ?
??? 會話發出的commit指令后，需要等待LGWR將這個事務產生的redo 成功寫入到磁盤之后，才可以繼續進行后續的操作，
??? 這個等待事件就叫作log file sync。
?? ?
??? 當系統中出現大量的log file sync等待事件時，應該檢查數據庫中是否有用戶在做頻繁的提交操作。

??? 這種等待事件通常發生在OLTP系統上。 OLTP 系統中存在很多小的事務，如果這些事務頻繁被提交，可能引起大量的log file sync的等待事件。

?? ?演示：
?? ?5.1 直接對表進行插入，提交，并查看 等待事件的產生結果：


?? ?select event,total_waits ,e.AVERAGE_WAIT
?? ? from? v$session_event e where sid=103 and event like 'log file%';


?? ??? ??? ?EVENT?? ?TOTAL_WAITS?? ?AVERAGE_WAIT
?? ??? ??? ?-------------------------------------------------------------
?? ??? ?1?? ?log file switch (checkpoint incomplete)?? ?106?? ?36.29
?? ??? ?2?? ?log file switch (private strand flush incomplete)?? ?2?? ?1.19
?? ??? ?3?? ?log file switch completion?? ?68?? ?6.58
?? ??? ?4?? ?log file sync?? ?8?? ?0.04



?? ??? ?SQL> insert? into t select * from t where object_id=200;
?? ??? ?256 rows inserted

?? ??? ?SQL> commit;
?? ??? ?Commit complete

?? ?再次查詢，發現“log file sync” 已增加了一次
?? ?select event,total_waits ,e.AVERAGE_WAIT
?? ? from? v$session_event e where sid=103 and event like 'log file%';

?? ??? ?EVENT?? ?TOTAL_WAITS?? ?AVERAGE_WAIT
?? ??? ?-------------------------------------------------------------
?? ?1?? ?log file switch (checkpoint incomplete)?? ?106?? ?36.29
?? ?2?? ?log file switch (private strand flush incomplete)?? ?2?? ?1.19
?? ?3?? ?log file switch completion?? ?68?? ?6.58
?? ?4?? ?log file sync?? ?9?? ?0.03


?? ?再測試rollback ;

?? ?SQL> insert? into t select * from t where object_id=200;
?? ?512 rows inserted

?? ?SQL> rollback;
?? ?Rollback complete

?? ??? ?EVENT?? ?TOTAL_WAITS?? ?AVERAGE_WAIT
?? ??? ?-------------------------------------------------------------
?? ?1?? ?log file switch (checkpoint incomplete)?? ?106?? ?36.29
?? ?2?? ?log file switch (private strand flush incomplete)?? ?2?? ?1.19
?? ?3?? ?log file switch completion?? ?68?? ?6.58
?? ?4?? ?log file sync?? ?10?? ?0.03

?? ?說明rollback,commit,都會產生一次等待事件。因為都要對log file進行同步。所以如果commit,rollback過于頻繁,
?? ?LGWR進程處理不過來，等待事件就會經常發生。

總結

以上是生活随笔為你收集整理的【性能优化】 之 几种常见的等待事件的演示示例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。