read by other session等待事件概述

刘泽宇

当从数据库请求信息时，Oracle将首先将数据从磁盘读入数据库缓冲区缓存。如果两个或多个会话请求相同的信息时，则第一个会话将数据读入buffer cache的过程中，而其他会话出现等待。在之前的数据库版本中，此等待事件被归类为“buffer busy waits”等待事件。 但是，在Oracle 10.1及更高版本中，此等待时间现在划分为“read by other session”等待事件，是oracle 10g 从oracle 9i的buffer busy waits中分离出来的，也是一种热块现象。

该等待事件的大量等待通常是由于一些进程重复读取相同的数据块，例如， 许多会话扫描同一索引或在同一个表上执行全表扫描。 调优此问题是找到并消除这种竞争。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通常显示与单个数据块相关的读取操作(如索引读取)。如果这个等待事件比较显著，可能表示在多表连接中，表的连接顺序存在问题，可能没有正确的使用驱动表;或者可能说明不加选择地进行索引。

当出现该问题如何解决？

一般出现该问题是由于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';

另外可以查看涉及对象信息，此处就是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';

根据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;

找到sql之后需要做的就是查看执行计划，判断问题所在,并进行优化。