Oracle常見等待事件之direct path read/write

1. 與直接讀取相關聯的等待事件。當ORACLE將資料塊直接讀入會話的PGA（程式全域性區）中，同時繞過SGA（系統全域性區）。PGA中的資料並不和其他的會話共享。即表明，讀入的這部分資料該會話獨自使用，不放於共享的SGA中。
2. 在 排序操作(order by/group by/union/distinct/rollup/合併連線)時，由於PGA中的SORT_AREA_SIZE空間不足，造成需要使用臨時表空間來儲存 中間結果，當從臨時表空間讀入排序結果時，產生direct path read等待事件。
3. 使用HASH連線的SQL語句，將不適合位於記憶體中的雜湊分割槽重新整理到臨時表空間中。為了查明匹配SQL謂詞的行，臨時表空間中的雜湊分割槽被讀回到記憶體中(目的是為了查明匹配SQL謂詞的行)，ORALCE會話在direct path read等待事件上等待。
4. 使 用並行掃描的SQL語句也會影響系統範圍的direct path read等待事件。在並行執行過程中，direct path read等待事件與從屬查詢有關，而與父查詢無關，執行父查詢的會話基本上會在PX Deq:Execute Reply上等待，從屬查詢會產生direct path read等待事件。
5. 直接讀取可能按照同步或非同步的方式執行，取決於平臺和初始化引數disk_asynch_io引數的值。使用非同步I/O時，系統範圍的等待的事件的統計可能不準確，會造成誤導作用。
6. 該 事件一般不可能顯示為主要的瓶頸，但它實際上也許是就是禍首。由於ORACLE統計等待時間的方式會造成統計的時間量不準確(如：從屬查詢產生的時間無法 進行統計)，所以對該事件不應該使用v$session_event檢視中的total_wait或time_waited進行估計，應該使用 v$sesstat檢視中的直接讀取操作次數(physical reads direct)進行判斷：

select a.NAME,
       b.SID,
       b.VALUE,
       round((sysdate - c.LOGON_TIME) * 24) hours_connected
  from v$statname a, v$sesstat b, v$session c
 where b.SID = c.SID
   and a.STATISTIC# = b.STATISTIC#
   and b.VALUE > 0
   and a.NAME = 'physical reads direct'
 order by b.VALUE

7. 由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或PAG_AGGREGATE_TARGET的設定是不是偏小。

8. 當direct path read等待事件是由於並行查詢造成的(讀取的是一般的資料檔案而非臨時檔案)，父SQL語句的HASHVALUE與子SQL語句的HASHVALUE不同，可以透過以下SQL查詢產生子SQL語句的父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

9. 初始化引數db_file_direct_io_count用來設定直接讀出和寫入操作設定最大的IO緩衝區大小，因此能影響direct path read的效能，該引數在9i中被隱蔽，並改以位元組數而不是塊數為單位。

>> 使用10046第8層跟蹤直接讀取操作的ORACLE會話，其中P3參數列明塊讀取的數量。

>> 也可使用strace,truss追蹤直接讀取或直接寫入操作的UNIX程式，從生成的TRACE檔案可獲得相應的直接IO大小。

>> 在第1層使用追蹤事件10357，啟動執行直接IO操作的會話的除錯資訊。

10. 大量的direct path read等待事件最可能是一個應用程式的問題。
11. 引數說明：

事件號：212

事件名：direct path read        

引數一：讀取資料檔案的絕對檔案號碼file number

引數二：起始塊號first dba

引數三：要讀取的塊數block cnt

由引數P1與P2推得訪問的資料物件：

select s.segment_name, s.partition_name 
  from dba_extents s 
 where  between s.block_id and (s.block_id + s.blocks -1) and s.file_id = 

注：

>> 1. 如果是Temp檔案，則表示該會話正在讀取它先前用direct path write操作所建立的臨時段，查明使用的是什麼型別的臨時段，有助於瞭解會話所做的事情。

SELECT DISTINCT decode(t.ktssosegt,
                        1,'SORT',
                        2,'HASH',
                        3,'DATA',
                        4,'INDEX',
                        5,'LOB_DATA',
                        6,'LOB_INDEX',
                        'UNDEFINED')
  FROM sys.x$ktsso t
 WHERE t.inst_id = userenv('instance') AND
       t.kssoses =  AND
       t.ktssosno =

>> 2. 如果是資料檔案，則可能是並行查詢從屬操作在工作，透過P1值確定資料檔案的名稱：

select s.NAME from v$datafile s where s.FILE# = 
union all
select a.name 
from v$tempfile a, v$parameter b
where b.NAME = 'db_files'
      and a.FILE# + b.VALUE = 

12. 等待時間：無超時