« IBM AIX Read-only file system案例一则 | Blog首页 | Oracle Weblogic License 策略的变更 »
Oracle Wait Events:read by other session
作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】链接：http://www.eygle.com/archives/2009/02/read_by_other_session.html
read by other session是在Oracle Database 10g中，引入的一个新事件，此前版本，这个事件在Buffer Busy Wait中记录。
根据文档，这个事件的解释如下：
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 wait' event
这个等待也就是说明数据库存在读的竞争，所以该等待事件通常和db file sequential read或db file scattered read 同时出现。
以下是一个客户生产数据库中的Top 5 Timed Events：
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
CPU time 4,897 52.9
db file sequential read 1,557,308 1,887 1 20.4 User I/O
read by other session 3,818,768 1,769 0 19.1 User I/O
db file scattered read 973,678 807 1 8.7 User I/O
log file sync 23,299 17 1 0.2 Commit
历史上的今天...
>> 2008-02-27文章:
>> 2007-02-27文章:
>> 2006-02-27文章:
------
这篇 【Oracle Wait Events:read by other session】来自 eygle.com | CSDN网摘| del.icio.us|Google订阅 | 鲜果订阅 | 抓虾订阅
By eygle on 2009-02-27 16:01 | Comments (7) | Posted to FAQ | Internal | Edit |
留言 (7)
这个等待事件是由什么样的Data usage/update pattern引起的?
比如, 是不是另一个会话Consistent Read读不同版本的数据块引起的?
Posted by: 木匠 at February 28, 2009 1:53 AM
这个应该不是Consistent Read竞争引起，如果是Consistent Read引发的竞争，应该还在buffer busy wait的范畴
Posted by: eygle at February 28, 2009 9:59 PM
我就是不懂,才问你的呀. ^_^
"read by other session"这个等待事件由啥引起的?
根源SQL是啥?
怎样修复?
是不是问题太多了, 您拿出大师风范, 嘻嘻.
附: 昨天新学了一个知识点,在data buffer cache中,一个block最多有6个版本. 参考x$bh.
Posted by: 木匠 at March 1, 2009 1:16 PM
6 个cr版本，是受到隐含参数_db_block_max_cr_dba的控制的
_db_block_max_cr_dba 6 Maximum Allowed Number of CR buffers per dba
其实 "read by other session" 我也见过不多，这次客户是由于过多的全表扫描物理读导致的，因为新的应用，在一个小表上没有使用索引，通过view访问，每次都是全量物理读！
Posted by: eygle at March 1, 2009 2:16 PM
感觉像热快，我们系统仅仅是select，也出现了read by other session
Posted by: fulzu at May 6, 2009 1:45 PM
就是热点竞争，以前是Buffer Busy Wait的
Posted by: Julia at May 6, 2009 3:51 PM
我在用户处发现时,都是属于RAC环境,估计与RAC的CACHE FUSION有关吧
Posted by: oldboy at May 15, 2009 11:34 AM