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,