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

这个等待事件其实是oracle IO问题一个比较尴尬的场景，会话a在进行把磁盘上的数据块读到内存（data buffer cache）中这个操作，
会话b，会话c 同时也请求这个数据块。因为会话a还完全读入内存(data buffer cache)，就导致了b，c read by other session
所以会话a一般是db file sequential read  或 db file scattered read。
这个是oracle 10g 从oracle 9i的buffer busy waits中分离出来的，也是一种热块现象.

SQL> col name for a40SQL> col PARAMETER1 for a15SQL> col PARAMETER2 for a15SQL> col PARAMETER3 for a15SQL> col WAIT_CLASS for a30SQL> select name,PARAMETER1,PARAMETER2,PARAMETER3,WAIT_CLASS from v$event_name where name like '%db file s%';NAME                                     PARAMETER1      PARAMETER2      PARAMETER3      WAIT_CLASS---------------------------------------- --------------- --------------- --------------- ------------------------------db file sequential read                  file#           block#          blocks          User I/Odb file scattered read                   file#           block#          blocks          User I/Odb file single write                     file#           block#          blocks          User I/O

 

我们看到p1，p2 为文件号和块号。就可以根据此等待事件查到是那个文件的那个块在等待，如果此等待事件在awr的top n的话，需要关注了。

可以根据下面的sql 来定位表名和拥有者。

SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, A.PARTITION_NAME FROM DBA_EXTENTS A WHERE FILE_ID = &FILE_ID AND &BLOCK_ID BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS – 1;

通过这个对象查询出对应的操作语句 通过这个对象查询出对应的操作语句

 select * from v$sql where upper(sql_text) like ‘%object_name%’;

 

在同一时间，系统中如果伴随大量的 db file sequential read  或 db file scattered read ，对于此read by other session 的快速定位
造事会话 起到了阻碍作用。这个快速定位的方法还是awr。但是这个实效性不高。还是需要写实时脚本来监控。脚本内sql方法思路是：
找到db file sequential read  或 db file scattered read的会话。根据这个找到他们的sql。根据这个sql 里面的表，来和上述 根据热块的表对比，如果是一样
就是这个造事会话了。
如果情况紧急，影响到生产。并且造势会话可以kill，还是kill 来得快。

 

---查询热块对象sql语句：SELECT *  FROM (SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE, SUM(TCH) TOUCHTIME,          FROM X$BH B, DBA_OBJECTS O         WHERE B.OBJ = O.DATA_OBJECT_ID           AND B.TS# > 0         GROUP BY O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE         ORDER BY SUM(TCH) DESC) WHERE ROWNUM <= 10   --查找热点块操作语句 SELECT /*+rule*/ HASH_VALUE, SQL_TEXT  FROM V$SQLTEXT WHERE (HASH_VALUE, ADDRESS) IN       (SELECT A.HASH_VALUE, A.ADDRESS          FROM V$SQLTEXT A,               (SELECT DISTINCT A.OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE                  FROM DBA_EXTENTS A,                       (SELECT DBARFIL, DBABLK                          FROM (SELECT DBARFIL, DBABLK                                  FROM X$BH                                 ORDER BY TCH DESC)                         WHERE ROWNUM < 11) B                 WHERE A.RELATIVE_FNO = B.DBARFIL                   AND A.BLOCK_ID <= B.DBABLK                   AND A.BLOCK_ID + A.BLOCKS > B.DBABLK) B         WHERE A.SQL_TEXT LIKE '%' || B.SEGMENT_NAME || '%'           AND B.SEGMENT_TYPE = 'TABLE') ORDER BY HASH_VALUE, ADDRESS, PIECE;