RAC上 read by other session 解决一例

今天在crm库中发现大量read by other session，cr request retry等待，找到read by other session 的SQL，SQL的plan为表扫描, 询问之后，应用也说这个等待的语句查了1小时怎么都查不出来结果，查了一下表只有200M，就算是全表扫描也应该很快，查了一下cr request retry等待的SQL，发现这个语句也有对read by other session的SQL中的表, topas 查了一下网络的流量，发现rac的interconnect 网卡流量达到50-60M每秒，感觉这个问题应该是由两个节点之间传数据造成的。

    再看read by other session的语句都是在instance1是执行，而cr request retry都在实例2上执行，感觉问题就出在这里，于是让read by other session的应用改了tnsname,也改成在实例2上执行，过了一会再去查看read by other session，cr request retry都已经消失了，topas查看interconnect的流量也恢复为原来的1M左右.

另外经常碰到一种情况为有一个会话在读文件的一个块时，长时间不动，会话却处于active状态,通过SQL:

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

找到p1,p2  再找到有哪些会话在读这个文件的这个块，

SELECT p1 "file#", p2 "block#", p3 "class#" ,event
FROM v$session_wait  where p1=&p1 and p2=&p2 ;

通常可以看到会一个db file scattered read或db file sequential read一块在读这个块不动，导致后面的很多会话都在等它完成而产生read by other session 事件，将这个会话KILL之后，其它会话可以读这个块后，这些read by other session 就消失了.

下为网上找的read by other session的相关等待：

"read by other session Definition: 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."

 

Applies to:

Oracle Server - Enterprise Edition - Version: 10.1.0.2 to 10.2.0.1
Information in this document applies to any platform.

Goal

What does "Read By Other Session" wait event mean ?

Solution

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.

When a session is waiting on this event, an entry will be seen in the v$session_wait system view giving more information on the blocks being waited for:

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

If information collected from the above query repeatedly shows that the same block (or range of blocks) is experiencing waits, this indicates a "hot" block or object.

The following query will give the name and type of the object:

SQL> 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