【效能調整】等待事件(六) direct path read&write

Direct path read/write相關的等待事件：

版本：10.2.0.4

SQL> select a.EVENT#,

2 a.NAME name,

3 a.PARAMETER1 p1,

4 a.PARAMETER2 p2,

5 a.PARAMETER3 p3

6 from v$event_name a

7 where a.NAME like '%direct path%';

EVENT# NAME P1 P2 P3

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

163 direct path read file number first dba block cnt

164 direct path read temp file number first dba block cnt

165 direct path write file number first dba block cnt

166 direct path write temp file number first dba block cnt

4 rows selected.

SQL>

版本9.2.0.8：

SQL> select a.EVENT#,

2 a.NAME name,

3 a.PARAMETER1 p1,

4 a.PARAMETER2 p2,

5 a.PARAMETER3 p3

6 from v$event_name a

7 where a.NAME like '%direct path%';

EVENT# NAME P1 P2 P3

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

235 direct path read file number first dba block cnt

236 direct path write file number first dba block cnt

237 direct path read (lob) file number first dba block cnt

238 direct path write (lob) file number first dba block cnt

direct path read

當oracle將資料塊直接讀入session的PGA中，而不是讀入SGA的buffer cache中時發生direct path read。直接讀可以在同步io或者非同步io模式發生，這取決於硬體平臺和DISK_ASYNCH_IO初始化引數的值。直接讀io通常用在訪問磁碟上的臨時段時發生，這些操作包括排序，並行查詢，hash joins：

排序操作（order by，group by，union，distinct，rollup，merge sort join等）時，由於pga中的SORT_AREA_SIZE空間不足，需要使用臨時表空間來儲存中間結果，當從臨時表空間讀入排序結果時，產生direct path read等待。

使用hash join的語句，臨時表存放的hash分割槽，當需要匹配的時候，直接讀入pga中來匹配行，oracle會話在direct path read等待。

使用並行掃描的SQL語句也會影響系統範圍的direct path read等待事件。在並行執行過程中，direct path read等待事件與從屬查詢有關，而與父查詢無關，執行父查詢的會話基本上會在PX Deq:Execute Reply上等待，從屬查詢會產生direct path read等待事件。

對於不同型別的原因可以使用下列語句查詢：

SELECT a.event,

a.sid,

c.sql_hash_value hash_vale,

decode(d.ktssosegt,

1,

'SORT',

2,

'HASH',

3,

'DATA',

4,

'INDEX',

5,

'LOB_DATA',

6,

'LOB_INDEX',

NULL) AS segment_type,

b.tablespace_name,

b.file_name

FROM v$session_wait a, dba_data_files b, v$session c, x$ktsso d

WHERE c.saddr = d.ktssoses(+)

AND c.serial# = d.ktssosno(+)

AND d.inst_id(+) = userenv('instance')

AND a.sid = c.sid

AND a.p1 = b.file_id

AND a.event = 'direct path read'

UNION ALL

SELECT a.event,

a.sid,

d.sql_hash_value hash_value,

decode(e.ktssosegt,

1,

'SORT',

2,

'HASH',

3,

'DATA',

4,

'INDEX',

5,

'LOB_DATA',

6,

'LOB_INDEX',

NULL) AS segment_type,

b.tablespace_name,

b.file_name

FROM v$session_wait a,

dba_temp_files b,

v$parameter c,

v$session d,

x$ktsso e

WHERE d.saddr = e.ktssoses(+)

AND d.serial# = e.ktssosno(+)

AND e.inst_id(+) = userenv('instance')

AND a.sid = d.sid

AND b.file_id = a.p1 - c.VALUE

AND c.NAME = 'db_files'

AND a.event = 'direct path read';

如果是從臨時檔案讀取排序短，則看看SORT_AREA_SIZE或者PGA_AGGREGATE_TARGET的設定是不是偏小，如果是讀取HASH段，則看看HASH_AREA_SIZE或者PGA_AGGREGATE_TARGET的設定是不是偏小。

下面的語句用來查詢由並行導致direct path read的sql:

SELECT decode(a.qcserial#, NULL, 'PARENT', 'CHILD') stmt_level,

a.sid,

a.serial#,

b.username,

b.osuser,

b.sql_hash_value,

b.sql_address,

a.degree,

a.req_degree

FROM v$px_session a, v$session b

WHERE a.sid = b.sid

ORDER BY a.qcsid, stmt_level DESC;

引數說明：

SQL> select a.PARAMETER1 p1, a.PARAMETER2 p2, a.PARAMETER3 p3

2 from v$event_name a

3 where a.name = 'direct path read';

P1 P2 P3

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

file number first dba block cnt

1 row selected.

SQL>

P1: 檔案號

P2: 讀取的起始block號

P3: 讀取的block數量

等待時間：無超時，直到io請求完成

direct path write

這個事件剛好和前面的direct path read相反，除了上面的情況以外，在使用直接路徑裝載資料或者使用insert append的時候發生。

當使用非同步io的時候，direct path write事件的等待和等待的時間可能會不準確。

從8.1.7開始direct path write(lob)為獨立的等待事件。

引數與等待時間和direct path read的引數也類似。

可以大致看看這個現象（如果有需要可以做個10046追蹤）：

在session 130：

SQL> create table t_test_direct as

2 select * from dba_objects union all

3 select * from dba_objects union all

4 select * from dba_objects;

Table created

SQL> create table t_test_direct1 as select * from t_test_direct a where 1=2;

Table created

SQL> insert /*+ append */ into t_test_direct1 select * from t_test_direct;

165897 rows inserted

SQL>

在另外一個session，在session 130執行過程中大致檢視session執行過程中的wait：

SQL> select a.EVENT,a.P1,a.p2,a.p3 from v$session_wait a where a.SID = 130;

EVENT P1 P2 P3

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

db file scattered read 1 71658 16

SQL> select a.EVENT,a.P1,a.p2,a.p3 from v$session_wait a where a.SID = 130;

EVENT P1 P2 P3

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

Data file init write 4294967295 32 2147483647

SQL> select a.EVENT,a.P1,a.p2,a.p3 from v$session_wait a where a.SID = 130;

EVENT P1 P2 P3

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

direct path write 1 74976 7

這樣看起來就比較明顯了。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16179598/viewspace-675816/，如需轉載，請註明出處，否則將追究法律責任。

【效能調整】等待事件(六) direct path read&write

相關文章