read by other session

Top 5 Timed Events

Event	Waits	Time(s)	Avg Wait(ms)	% Total Call Time	Wait Class
read by other session	3,732,391	7,897	2	75.2	User I/O
db file sequential read	640,549	2,323	4	22.1	User I/O
CPU time		296		2.8
latch: cache buffers chains	28,414	5	0	.1	Concurrency
control file sequential read	7,318	3	0	.0	System I/O

 

 

READ BY OTHERS  SESSIONS 的根本原因就是因为你某条SQL做了大量block的扫描, 我猜想那条SQL至少要50万个逻辑读.
除了解决SQL问题,基本没有别的办法

我的理解是read by other session最重要的原因还是由于io的能力太差，一个io读所耗费的时间太长造成的，可能是两种情况，io负载过重，或者io配置太低，当然调整语句减少io读也是一种办法。
buffer busy wait剔除了read by other session原因之后的另外一种情况，the buffer is the buffer cache, but in a incompatible mode (that is, some other session is changing the buffer)，这种情况则更应该归结到系统设计上的原因，多个语句同时操作同样的块

from:http://www.itpub.net/thread-1015649-1-1.html

read by other session是９i buffer busy wait的一部分，一个session读取一个数据块的时候，而另一个session正在从数据文件中读取这个数据块

全表扫描等大的读操作，其他操作等待读取同样的block。

出现这个等待事件，说明肯定有个session正在从数据文件中读取块，自然和db file sequential read 或db file scattered read 有关

记得好像是大家都要用到一个BLOCK，而这个BLOCK呢，还不再BUFFER CACHE，所以俩人同时要把这个BLOCK读到CACHE中去，所以一个人再读进去的时候，另一个人就在等待READ BY OTHER SESSION

对于解决这个事件有如下建议
1.增大buffer cache,使得data buffer中能够容下更多的数据块,但好像会存在cache buffer chains的隐患.
2.像这一事件必然有db file sequential read和db file scattered read,找出sql语句优化,减少执行时间.
以上不对之处,请指点!

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.

from:http://www.itpub.net/thread-888384-1-1.html

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

from:http://space.itpub.net/10834762/viewspace-592112

有关read by other session 的等待事件

今天在做STATSPACK报告的时候，发现TOP 5中有一个以前没见过的等待事件read by other session。
 
Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
read by other session                       11,555,207     102,695      9   58.3
db file scattered read                      10,009,339      54,630      5   31.0
db file sequential read                      1,041,653       8,832      8    5.0
CPU time                                                     6,941           3.9
log file parallel write                        689,758       1,475      2     .8
 
 
网上查了一篇关于这个等待事件的文章：
http://www.confio.com/English/Tips/Read_By_Other_Session.php
 
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.

Finding the 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:

  
 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;
 
  

Eliminating contention
Depending on the database environment and specific performance situation the following variety of methods can be used to eliminate contention:

Tune inefficient queries - This is one of those events you need to "catch in the act" through the v$session_wait view as prescribed above. Then, since this is a disk operating system issue, take the associated system process identifier (c.spid) and see what information we can obtain from the operating system.

Redistribute data from the hot blocks –deleting and reinserting the hot rows will often move them to a new data block. This will help decrease contention for the hot block and increase performance. More information about the data residing within the hot blocks can be retrieved with queries similar to the following:

  
 SELECT data_object_id
FROM dba_objects
WHERE owner='&owner' AND object_name='&object';

SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,0) start_rowid
FROM dual;
--rowid for the first row in the block

SELECT dbms_rowid.rowid_create(1,<data_object_id>,<relative_fno>,<block>,500) end_rowid
FROM dual; 
--rowid for the 500th row in the block

SELECT <column_list>
FROM <owner>.<segment_name>
WHERE rowid BETWEEN <start_rowid> AND <end_rowid>
 
  

Adjust PCTFREE and PCTUSED – adjusting the PCTFREE value downward for an object will reduce the number of rows physically stored in a block. Adjusting the PCTUSED value for an object keeps that object from getting prematurely put back on the freelist.

Depending on the type of contention, adjusting these values could help distribute data among more blocks and reduce the hot block problem. Be careful to optimize these parameters so blocks do move in and out of the freelist too frequently.

Reduce the Block Size – this is very similar to adjusting the PCTFREE and PCTUSED parameters in that the goal is to reduce the amount of data stored within one block. In Oracle 9i and higher this can be achieved by storing the hot object in a tablespace with a smaller block size. In databases prior to Oracle 9i the entire database must be rebuilt with a smaller block size.

Optimize indexes – a low cardinality index has a relatively small number of unique values, e.g. a column containing state data with only 50 values. Similar to inefficient queries, the use of a low cardinality index could cause excessive number of blocks to be read into the buffer cache and cause premature aging out of "good" blocks.

Conclusion
When a session waits on the "read by other session" event, it indicates a wait for another session to read the data from disk into the Oracle buffer cache. If this happens too often the performance of the query or the entire database can suffer. Typically this is caused by contention for "hot" blocks or objects so it is imperative to find out which data is being contended for. Once that is known this document lists several alternative methods for solving the issue.

from:http://blogold.chinaunix.net/u3/107027/showart_2190272.html

10g wait event: read by other session
http://www.dbafan.com/blog/?p=132