read by other session 等待事件。

   今天是2014-01-06，從今天開始，打算春節之前每天學習一個等待事件，今天就記錄一下read by other session這個等待事件筆記。

什麼是read by other session？

This wait event occurs when we are trying to access a buffer in the buffer cache but we find that the buffer is currently being read from disk by another user so we need to wait for that to complete before we can access it.  In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher, the wait time is now broken out into the "read by other session" wait event.

Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. Tuning this issue is a matter of finding and eliminating this contention.

參考文檔：

 

官方介紹：

read by other session

This event occurs when a session requests a buffer that is currently being read into the buffer cache by another session. Prior to release 10.1, waits for this event were grouped with the other reasons for waiting for buffers under the 'buffer busy wait' event

Wait Time: Time waited for the buffer to be read by the other session (in microseconds)

Parameter Description
file# See "file#"
block# See "block#"
class# See "class"

 注意有p1，p2，p3,。

當出現該問題如何解決？

一般出現該問題是由於sql導致的，或者是由於磁碟裝置可能導致。

當出現該問題的時候，首先需要定位sql。

方法一：通過ash獲得細粒度的報告，查看top sql statement 獲得sql。

方法二：通過sql語句直接獲得：

1、當前正在發生的問題：

select sql_fulltext from v$sql a,v$session b where a.sql_id=b.sql_id and b.event='read by other session';

2、曆史曾經發生的

select a.sql_id,sql_fulltext from v$sql a,dba_hist_active_sess_history b where a.sql_id=b.sql_id and b.event='read by other session';

往往read by other session伴隨著db file sequential read事件的出現。

另外可以查看涉及對象資訊，此處就是p1,p2,p3

SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait WHERE event = 'read by other session';

通過p1,p2,p3獲得熱點對象：

SELECT relative_fno, owner, segment_name, segment_type FROM dba_extents
WHERE file_id = &file
AND &block BETWEEN block_id AND block_id + blocks - 1;

另外，也可以 直接查看熱點塊的資訊，如查看熱點塊導致的sql語句：

select sql_text
  from v$sqltext a,
       (select distinct a.owner, a.segment_name, a.segment_type
          from dba_extents a,
               (select dbarfil, dbablk
                  from (select dbarfil, dbablk from x$bh order by tch desc)
                 where rownum < 11) b
         where a.RELATIVE_FNO = b.dbarfil
           and a.BLOCK_ID <= b.dbablk
           and a.block_id + a.blocks > b.dbablk) b
 where a.sql_text like '%' || b.segment_name || '%'
   and b.segment_type = 'TABLE'
 order by a.hash_value, a.address, a.piece;

查看熱點塊對象：

SELECT E.OWNER, E.SEGMENT_NAME, E.SEGMENT_TYPE
  FROM DBA_EXTENTS E,
       (SELECT *
          FROM (SELECT ADDR, TS#, FILE#, DBARFIL, DBABLK, TCH
                  FROM X$BH
                 ORDER BY TCH DESC)
         WHERE ROWNUM < 11) B
 WHERE E.RELATIVE_FNO = B.DBARFIL
   AND E.BLOCK_ID <= B.DBABLK
   AND E.BLOCK_ID + E.BLOCKS > B.DBABLK; 找到sql之後需要做的就是查看執行計畫，判斷問題所在,並進行最佳化。 1、對於在shared pool存在的cursor可以通過如下命令查看執行計畫 select * from table(dbms_xplan.display_cursor('sql_id',null,'allstats')); 2、對於曆史可以通過查看awr資訊獲得： select * from table(dbms_xplan.display_awr('sql_id')); 另外對於裝置引起的需要查看磁碟讀寫資訊，可以 通過vmstat 2 200進行判斷。

Reducing Number of Waits:

參考文檔：   ——————————————————————————————————————————————————————————————————————————————————————————————————Rhys——————————————