ORACLE等待事件：read by other session

read by other session簡介

官方關於read by other session的介紹如下：

When information is requested from the database, Oracle will first read the data from disk into the database buffer cache. If two or more sessions request the same information, the first session will read the data into the buffer cache while other sessions wait. In previous versions this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher this 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.

當從資料庫請求信息時，Oracle將首先將數據從磁碟讀入資料庫緩衝區緩存。如果兩個或多個會話請求相同的信息時，則第一個會話將數據讀入buffer cache的過程中，而其他會話出現等待。在之前的資料庫版本中，此等待事件被歸類為“buffer busy waits”等待事件。 但是，在Oracle 10.1及更高版本中，此等待時間現在劃分為“read by other session”等待事件。 該等待事件的大量等待通常是由於一些進程重覆讀取相同的數據塊，例如， 許多會話掃描同一索引或在同一個表上執行全表掃描。 調優此問題是找到並消除這種競爭。

C.3.114 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 waits' event

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

read by other session的分析

    read by other session等待的出現也說明資料庫存在讀的競爭，等待事件read by other session 通常與等待事件db file scattered read 和db file sequential read同時出現。有時候甚至與等待事件enq: TX - row lock contention同時出現（特殊情況，一個特殊案例中遇到的，等待read by other session的會話阻塞其它會話），如下截圖所示。

db file scattered read通常顯示與全表掃描相關的等待。當資料庫進行全表掃時，基於性能的考慮，數據會分散(scattered)讀入Buffer Cache。如果這個等待事件比較顯著，可能說明對於某些全表掃描的表，沒有創建索引或者沒有創建合適的索引。

db file sequential read通常顯示與單個數據塊相關的讀取操作(如索引讀取)。如果這個等待事件比較顯著，可能表示在多表連接中，表的連接順序存在問題，可能沒有正確的使用驅動表;或者可能說明不加選擇地進行索引。

read by other session解決 

如何查看當前會話處於等待read by other session：

使用下麵SQL找到當前處於read by other session等待的SQL語句，然後分析SQL，優化SQL

SELECT s.username, 

       s.sid, 

       s.serial#, 

       s.osuser, 

       s.machine, 

       s.terminal, 

       s.program, 

       s.last_call_et, 

       s.event, 

       s.seconds_in_wait, 

       s.blocking_session, 

       t.sql_text 

       --,t.SQL_FULLTEXT 

FROM   v$session s, 

       v$sqlarea t 

WHERE  s.sql_address = t.address 

       AND S.sid IN (SELECT sid                    

                     FROM   v$session_wait                   

                     WHERE  event IN ( 'read by other session' ));

或

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

也可以通過下麵SQL，獲取產生read by other session等待事件的SQL的實際執行計劃，研究執行計劃後，對相關SQL進行調優，例如，對於全表掃描的添加合適索引。

SELECT DISTINCT SQL_ID

FROM V$SESSION

WHERE EVENT IN('read by other session', 'db file sequential read');

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('xxxxx'));

對於非當前會話的read by other session等待事件，我們可以通過AWR報告和ASH結合，找到發生read by other session等待的SQL語句。

1：首先分析Top 5 Timed Events，分析read by other session、db file scattered read、db file sequential read等待事件

2：AWR報告中分析Segments by Buffer Busy Waits部分內容

    如下截圖所示，基本上可以判斷第一個表xxx就是出現

3：首先使用下麵腳本找到產生了'read by other session'等待事件的SQL，然後生成指定SQL語句的統計報表（awrsqrpt.sql）以及接近採樣點附近的ASH報告

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';

AWR報告裡面的SQL ordered by Reads 或SQL ordered by Gets中的TOP SQL找到涉及Segments by Buffer Busy Waits中對象的SQL ，然後結合ASH（細粒度的報告）來判斷、分析！。

另外，如果需要查看涉及對象信息，可以通過等待事件的欄位p1,p2,p3來獲取