read by other session 等待事件处理

2013-04-26 号 Indonesia jakarta 运营商X

OS:suse 10
DBMS:ORACLE11.1.0.7
4 rac 节点

例行检查发现一业务模块运行非常慢

查询等待事件及awr 报告

发现有 read by other session等待事件

awr 报告

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
DB CPU   8,349   47.67 
db file sequential read 1,160,341 5,164 4 29.48 User I/O
read by other session 119,364 541 5 3.09 User I/O
gc buffer busy acquire 90,531 399 4 2.28 Cluster
db file scattered read 25,374 138 5 0.79 User I/O

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.
 Confio concludes with a summary that “read by other session waits” are very similar to buffer busy waits
 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.
 
总结：两个或者多个会话同时需要把硬盘中的对象装载到data buffer中，当其中一个会话把对象装入后，
其他会话就处于read by other session等待状态；这个是oracle 10g 从oracle 9i的buffer busy waits中分离出来的，
也是需要一种热块现象
从执行时间过长的

原来25号大量数据补采开成一个数据入库的高峰，对一些业务表的大规模写入更新造成此问题

定位执块的方法

select p1,p2,p3 from v$session_wait where event='read by other session';

select relative_fnno,owner,segment_name from dba_extents where fileid=&file
and &block between block_id and block_id +blocks-1
也可以通过awr 报告的 buffer busy segment 来定位

优化方法
1. 调整pctused /pctfree 减少执块的争用,减少单块数量的行数
2. 可以使用hash 分区，减少热块的争用机率
3. 优化index
4. 减少blocksize 的大小