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 来获取

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

官方文档描述如下：

·          P1 = file# Absolute File# (AFN)

·          P2 = block#

·          P3 = class# Block class

·          file# Absolute File Number (AFN)

        This is the file number of the data file that contains the block that the waiting session wants.

·          block#

        This is the block number in the above file# that the waiting session wants access to. See Note:181306.1 to determine the tablespace, filename and object for this file#,block# pair.

·          class# Block class#

        This is the class of block being waited on. In particular:

        class 1 indicates a "data block", which could be table or index

        class 4 indicates a "segment header"

        class >=15 indicate undo blocks

另外，下面一些 SQL 来自惜分飞的 “ Read by other session 等待事件 ” ，非常有用。

根据 FILE# ， BLOCK# 查询热块对象

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME

FROM DBA_EXTENTS A

WHERE FILE_ID = &FILE_ID

AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS – 1;

直接查找热点块对象语句

SELECT *
  FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME
          FROM X$BH B, DBA_OBJECTS O
         WHERE B.OBJ = O.DATA_OBJECT_ID
           AND B.TS# > 0
         GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
         ORDER BY SUM(TCH) DESC)
 WHERE ROWNUM <= 10
--或者
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

SELECT /*+rule*/
 HASH_VALUE, SQL_TEXT
  FROM V$SQLTEXT
 WHERE (HASH_VALUE, ADDRESS) IN
       (SELECT A.HASH_VALUE, A.ADDRESS
          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 HASH_VALUE, ADDRESS, PIECE;

其它一些官方或英文资料：

Solutions

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.

1. Tune the SQL statement so that it reads fewer blocks. If the top objects listed in SolarWinds DPA are indexes, determine if there is a more efficient index that can be used more efficiently. If the top objects are tables, a full table scan or index randge scan is being performed. Look for efficient indexing opporunties.

2. Increase the buffer cache so that more blocks are already in memory rather having to be read from disk. The query will still need to read the same number of blocks so tuning is the first recommendation, but if you cannot tune the statement, a query reading blocks from memory is much faster than from disk.

3. Increase the PCTUSED / PCTFREE for the table storage parameters via ALTER TABLE or rebuild. This will result in less rows per block and possibly reduce contention.

WAITEVENT: "read by other session" Reference Note (文档 ID 732891.1)

Reducing Waits / Wait times:

Reducing waits typically involves application tuning and/or IO tuning.

Contention does not mean that there is necessarily a problem, but it is more likely that selects against the objects involved are reading more blocks than they have to. These unnecessary reads can then contend. To find such selects, look for the queries that are waiting frequently for 'read by other session'. Active Session History (ASH) reports during the period where contention is seen are a useful source of this sort of information. Alternatively look for those queries that read a lot of buffers when querying these tables; it is possible that these queries are poorly optimized and perhaps a different access path may read fewer buffers and cause less contention.

eg: if lots of sessions scan the same unselective index this can show as "read by other session" waits for "data blocks":

·          the first session processes the blocks that are in the buffer cache quickly but then a block has to be read from disk

·          the other sessions (scanning the same index) quickly 'catch up' and want the block which is currently being read from disk - they wait for the buffer as someone is already reading the block in.

Since the 'read by other session' wait event is an indicator that the buffers being waited for are popular (and are being "read by another session"), if queries are properly optimized, then an undersized buffer cache may mean that there is insufficient space to retain all the buffers required by queries. Make sure that the buffer cache is adequately sized to keep the buffers required by the current SQL statements from being aged out.

Resolving Issues Where 'read by other session' Waits When I/O is Slow (文档 ID 1477229.1)

Reducing Number of Waits:

1. If you are seeing long delays taken to service this wait event then check the amount of I/O being performed on the device identified by the P1 argument of this wait event.

   The device and / or the controller may be overloaded. If this is the case then take the standard steps of spreading the file across further devices etc.

2. Check that the real problem isn't the amount of time that the operating system is taking to service the system call.
3. Find out which file numbers are causing the highest average waits and then determine which filesystem contains the file

4. Determine why the filesystems are performing poorly. Some common causes are:

   • "hot filesystems" - too many active files on the same filesystem exhausting the I/O bandwidth
   • hardware problem
   • In Parallel Execution (PX) is being used, determine if the I/O subsystem is saturated by having too many slaves in use.

参考资料：

http://www.xifenfei.com/2011/07/read-by-other-session%E7%AD%89%E5%BE%85%E4%BA%8B%E4%BB%B6.html

https://docs.oracle.com/database/121/REFRN/GUID-DCEB3FA4-57A9-4EBE-A349-BBCA1BA49281.htm#REFRN00610

http://www.dbdream.com.cn/2015/01/%E5%85%B3%E4%BA%8Eread-by-other-session%EF%BC%8Cdb-file-scattered-read%EF%BC%8Cdb-file-sequential-read%E7%AD%89%E5%BE%85%E6%97%B6%E9%97%B4%E7%9A%84%E4%BC%98%E5%8C%96/