第一篇《oracle等待事件1分別用表和索引上資料的訪問來產生db file scattered read等待事件》http://space.itpub.net/26686207/viewspace-751965
第二篇《oracle等待事件2構造一個DB File Sequential Read等待事件和構造一個Direct Path Read等待事件》http://space.itpub.net/26686207/viewspace-751968
第三篇《oracle等待事件3構造一個Direct Path write等待事件和構造一個Log File Sync等待事件》http://space.itpub.net/26686207/viewspace-751969

三 構造一個DB File Sequential Read等待事件，等待事件需要在v$session_wait和10046 trace檔案中顯示出來，貼出整個演示過程。

db file sequential read 等待事件：是由於資料塊順序讀產生的，當資料塊(索引塊)從磁碟一個一個讀到記憶體中時，在這個過程中oracle會發生“db file sequential read” 等待事件。

塊順序讀場景：索引塊順序讀

資料塊順序讀

undo回滾構造一致性讀

磁碟I/O瓶頸

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

下面我們來使用索引塊順序讀來產生“db file sequential read”

準備環境，原本想利用上面的建立好的表和索引來做的，但一想為了給筒子們一個完整的，詳細的，瑣碎的，容易驗的實驗例子，我還是當作重新開始一步一步來搭建環境演示給大家。前提是不是要清理一下環境

LEO1@LEO1> drop table leo1 purge; 清理上次的環境

Table dropped.

LEO1@LEO1> create table leo1 as select * from dba_objects; 建立表

Table created.

LEO1@LEO1> create index idx_leo1 on leo1(object_id); object_id欄位上建立索引，我們要利用索引塊順序讀

Index created.

LEO1@LEO1> select table_name,index_name from user_indexes where table_name='LEO1';

TABLE_NAME INDEX_NAME

------------------------------ ------------------------------

LEO1 IDX_LEO1

LEO1@LEO1> execute dbms_stats.gather_table_stats(ownname=>'LEO1',tabname=>'LEO1',cascade=>TRUE);

PL/SQL procedure successfully completed. 為啥每次都需要分析表呢，收集表資料分佈情況，看看有沒有資料傾斜，在看看索引鍵值重複率，表和索引存放的位置和大小等，有了一些資訊方便CBO更加客觀評價執行計劃，不做可以嘛？yes -> oracle在必要情況下會進行動態採集操作，顯然動態採集只是儘可能抓取一部分樣本資料來評估，沒有前面一種客觀和科學，它的好處在於自動化和方便，無需人工干預。

LEO1@LEO1> set autotrace trace exp 啟動執行計劃

LEO1@LEO1> select owner,object_id,object_name,object_type,status from leo1 where object_id>=100 and object_id<=200; oracle進行IRS掃描

Execution Plan

----------------------------------------------------------

Plan hash value: 1434365503

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 100 | 5200 | 4 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID | LEO1 | 100 | 5200 | 4 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_LEO1 | 100 | | 2 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=200) 謂詞條件是對*操作的說明

我們只看[100，之間的資料，走的是索引範圍掃描，就是先讀索引塊，透過索引鍵值找到ROWID，在讀資料塊，這樣一個一個讀到記憶體中，就是資料塊順序讀型別

LEO1@LEO1> set autotrace off

LEO1@LEO1> select sid,event,total_waits,time_waited from v$session_event;

SID EVENT TOTAL_WAITS TIME_WAITED

---------- --------------------------------------------------------------------------------

133 db file sequential read 470 122

我們先標記一下等待的次數，如果一會次數增加那麼說明成功觸發了這個等待事件

LEO1@LEO1> set serveroutput on

LEO1@LEO1> create or replace procedure p2

as

leo number;

begin

for i in 1..8000

loop

select count(*) into leo from leo1 where object_id>=100 and object_id<=200;

end loop;

dbms_output.put_line('successfully');

end;

/

2 3 4 5 6 7 8 9 10 11

Procedure created.

建立一個儲存過程p2迴圈8000次（迴圈的越多執行的時間越長），在迴圈的過程中我們可以在v$session_wait檢視上捕捉到“db file sequential read”select count(*) into leo from leo1 where object_id>=100 and object_id<=200 與上面的sql執行過程類似只是多了一個彙總過程，也走的是索引範圍掃描。

LEO1@LEO1> alter system flush buffer_cache; 同樣也需要先清空data_buffer_cache，原理同上

System altered.

LEO1@LEO1> execute p2; 當看到“successfully”表明這個p2執行完畢

successfully

PL/SQL procedure successfully completed.

會話157

LEO1@LEO1> 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

---------- ---------------------------------------------------------------- ----------------- ---------- ---------- ---------------

133 db file scattered read User I/O 5 file# 2852 block# 1 blocks

在157會話中當對133的儲存過程p2執行過程中，馬上檢視v$session_wait檢視，才能捕捉到“db file sequential read”，如果執行完畢了就看不到這個等待事件了，切記切記切記！

引數含義都是一樣的，只是p3指單資料塊讀取，只能顯示p3=1，這也符合資料塊順序讀的機制

LEO1@LEO1> alter system flush buffer_cache; 再次清空data_buffer_cache

System altered.

10046 trace檔案中顯示出來

小提示：當你做實驗次數多了時候，會發現要訪問的trace檔案非常的大在裡面找到自己測試sql語句是一件很麻煩的事，這時我們可以選擇先清空它在進行測試。

[oracle@leonarding1 trace]$ cd /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/

[oracle@leonarding1 trace]$ ll | grep 6327 找到6327號檔案

-rw-r----- 1 oracle oinstall 187300 Dec 29 01:49 LEO1_ora_6327.trc

-rw-r----- 1 oracle oinstall 1231 Dec 29 01:49 LEO1_ora_6327.trm

[oracle@leonarding1 trace]$ > LEO1_ora_6327.trc 清空它既可

[oracle@leonarding1 trace]$ ll | grep 6327 現在是一個空檔案了

-rw-r----- 1 oracle oinstall 0 Dec 29 09:28 LEO1_ora_6327.trc

LEO1@LEO1> alter session set events '10046 trace name context forever,level 12'; 啟動10046事件

Session altered.

LEO1@LEO1> select count(*) from leo1 where object_id>=100 and object_id<=200; 索引範圍掃描

COUNT(*)

------------------

94

LEO1@LEO1> alter system flush buffer_cache; 清空重新抽取資料

System altered.

LEO1@LEO1>select owner,object_id,object_name,object_type,status from leo1 where object_id>=100 and object_id<=200;

LEO1@LEO1> alter system flush buffer_cache; 再清空

System altered.

LEO1@LEO1> insert into leo1 select * from leo1; 插入資料也會產生順序讀，我們一會看10046事件就知道了

71959 rows created.

LEO1@LEO1> insert into leo1 select * from leo1;

143918 rows created.

LEO1@LEO1> alter session set events '10046 trace name context off'; 關閉10046事件

Session altered.

LEO1@LEO1> select name,value from v$diag_info where name='Default Trace File'; 當前會話寫入trace檔名

NAME VALUE

--------------------------------------------------------------------------------

Default Trace File /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_19807.trc

LEO1@LEO1> !vim /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_19807.trc 開啟trace檔案

====================================================================================================

由於讀取的資料量較少，發生的“db file sequential read”也不是很多，你的是不是也這樣呢：）

select count(*) from leo1 where object_id>=100 and object_id<=200

END OF STMT

PARSE #2:c=0,e=42,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=6466795,tim=1356745819699276

EXEC #2:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=6466795,tim=1356745819699412

WAIT #2: nam='SQL*Net message to client' ela= 11 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1356745819699497

WAIT #2: nam='db file sequential read' ela= 26 file#=5 block#=2851 blocks=1 obj#=73718 tim=1356745819699622

WAIT #2: nam='db file sequential read' ela= 16 file#=5 block#=3364 blocks=1 obj#=73718 tim=1356745819699706

====================================================================================================

select owner,object_id,object_name,object_type,status from leo1 where object_id>=100 and object_id<=200

END OF STMT

PARSE #4:c=0,e=108,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1434365503,tim=1356745833882227

EXEC #4:c=0,e=63,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1434365503,tim=1356745833882367

WAIT #4: nam='SQL*Net message to client' ela= 11 driver id=1650815232 #bytes=1 p3=0 obj#=0 tim=1356745833882463

WAIT #4: nam='db file sequential read' ela= 22 file#=5 block#=2851 blocks=1 obj#=73718 tim=1356745833882638

WAIT #4: nam='db file sequential read' ela= 15 file#=5 block#=3364 blocks=1 obj#=73718 tim=1356745833882720

WAIT #4: nam='db file sequential read' ela= 18 file#=5 block#=180 blocks=1 obj#=73717 tim=1356745833883105

====================================================================================================

插入資料操作中，95%會發生大量的“db file sequential read”等待事件，偶爾也能看到“db file scattered read”

因為什麼呢？我們想一想插入流程是不是

第一先從資料檔案中把資料塊讀到記憶體裡在，這時會發生大量的“db file sequential read” 等待事件

第二再把記憶體中資料插入到資料檔案裡

insert into leo1 select * from leo1

END OF STMT

PARSE #3:c=3000,e=2992,p=0,cr=5,cu=0,mis=1,r=0,dep=0,og=1,plh=2716644435,tim=1356745204775114

WAIT #3: nam='db file sequential read' ela= 34 file#=5 block#=182 blocks=1 obj#=73717 tim=1356745204776532

WAIT #3: nam='Disk file operations I/O' ela= 186 FileOperation=2 fileno=3 filetype=2 obj#=73717 tim=1356745204776928

WAIT #3: nam='db file sequential read' ela= 17 file#=5 block#=184 blocks=1 obj#=73717 tim=1356745204777246

WAIT #3: nam='db file sequential read' ela= 25 file#=5 block#=186 blocks=1 obj#=73717 tim=1356745204777889

WAIT #3: nam='db file sequential read' ela= 15 file#=5 block#=188 blocks=1 obj#=73717 tim=1356745204778474

WAIT #3: nam='db file sequential read' ela= 25 file#=5 block#=190 blocks=1 obj#=73717 tim=1356745204779100

。。。。。。。。。。。還有很多不在貼了，節約空間

====================================================================================================

第二次插入為什麼，只發生了2次“db file sequential read”，是不是第一次已經把大部分資料載入到記憶體裡啦，第二次的時候就可以直接從記憶體裡往磁碟上插資料了吧，省略了在從磁碟讀這步了。

insert into leo1 select * from leo1

END OF STMT

PARSE #4:c=0,e=110,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2716644435,tim=1356745852945838

WAIT #4: nam='db file sequential read' ela= 32 file#=5 block#=4352 blocks=1 obj#=73717 tim=1356745852961373

WAIT #4: nam='db file sequential read' ela= 37 file#=5 block#=4353 blocks=1 obj#=73717 tim=1356745852984158

四 構造一個Direct Path Read等待事件，等待事件需要在v$session_wait和10046 trace檔案中顯示出來，貼出整個演示過程。

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

場景：排好序資料會優先放在PGA中，當PGA裝滿時，就會被寫入到磁碟的temp表空間裡，當發生“重新讀取”的時候，磁碟上的資料不會經過SGA的記憶體區，而是直接讀取到PGA記憶體區裡，在這個過程中將會發生“direct path read”等待事件

LEO1@LEO1> show parameter target

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target integer 0

db_flashback_retention_target integer 1440

fast_start_io_target integer 0

fast_start_mttr_target integer 0

memory_max_target big integer 652M

memory_target big integer 652M

parallel_servers_target integer 8

pga_aggregate_target big integer 0

sga_target big integer 0

LEO1@LEO1> set serveroutput on

LEO1@LEO1> select table_name,index_name from user_indexes where table_name='LEO1';

TABLE_NAME INDEX_NAME

------------------------------ ------------------------------

LEO1 IDX_LEO1

我們這次利用一下之前的表leo1，我們已經重做了2次，凡是跟著做的筒子們已經由魚得水耶，我們們就加快點進度吧。

v$session_wait檢視中顯示出來

LEO1@LEO1> create or replace procedure p3

as

leo number;

begin

for i in 1..1000

loop

select count(*) into leo from leo1 order by object_name;

end loop;

dbms_output.put_line('successfully');

end;

/

2 3 4 5 6 7 8 9 10 11

Procedure created.

建立儲存過程p3迴圈1000次，既然我們要捕捉排好序的資料來產生“direct path read”等待事件，那麼我們就要對記錄進行order by 操作，這樣我們在檢索的時候才能看到我們想要的等待事件，其實order by 哪列都可以的。

LEO1@LEO1> execute p3; 當看到“successfully”表明這個p3執行完畢

successfully

PL/SQL procedure successfully completed.

會話157

LEO1@LEO1> 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

---------- ---------------------------------------------------------------- ---------- ---------- ---------- ---------- --------------- ---------- ---------------

133 direct path read User I/O 5 file numbe 1922 first dba 62 block cnt

133 direct path read User I/O 5 file numbe 3648 first dba 64 block cnt

133 direct path read User I/O 5 file numbe 4226 first dba 62 block cnt

我們看到“direct path read”事件發生在5號資料檔案的1922 3648 4226號的資料塊上，分別從這三個資料塊起始開始讀取62 64 62 個資料塊。

10046 trace檔案中顯示出來

LEO1@LEO1> alter session set events '10046 trace name context forever,level 12'; 啟動10046事件

Session altered.

LEO1@LEO1> select count(*) from leo1 order by object_name; 資料排序

COUNT(*)

----------------

71959

LEO1@LEO1> alter session set events '10046 trace name context off'; 關閉10046事件

Session altered.

LEO1@LEO1> select name,value from v$diag_info where name='Default Trace File'; 當前會話寫入trace檔名

NAME VALUE

--------------------------------------------------------------------------------

Default Trace File /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_19807.trc

LEO1@LEO1> !vim /u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_19807.trc

====================================================================================================

select count(*) from leo1 order by object_name

END OF STMT

PARSE #6:c=1000,e=1320,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=4191104944,tim=1356754783973531

EXEC #6:c=0,e=56,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4191104944,tim=1356754783973706

WAIT #6: nam='SQL*Net message to client' ela= 12 driver id=1650815232 #bytes=1 p3=0 obj#=7 tim=1356754783973862

WAIT #6: nam='direct path read' ela= 101 file number=5 first dba=179 block cnt=13 obj#=73717 tim=1356754783974411

WAIT #6: nam='direct path read' ela= 103 file number=5 first dba=1457 block cnt=15 obj#=73717 tim=1356754783974600

WAIT #6: nam='direct path read' ela= 103 file number=5 first dba=1473 block cnt=15 obj#=73717 tim=1356754783975005

小結：看只要經過order by排序的檢索就會產生“direct path read”等待事件，此時資料是直接讀取到PGA記憶體區的。如果你不先進行排序也可能會出現“direct path read”，但還伴隨著db file sequential read和db file scattered read，如果進行排序了那麼99%都是direct path read等待事件了。

2012.12.28
天津&winter
分享技術~成就夢想
Blog：

oracle等待事件2構造一個DB File Sequential Read等待事件和構造一個Direct Path Read

相關文章