read by other session

1.oracle数据库实例启动运行一天的时候， 发现有大量的read by other session 等待。占到等待的48%

2、查询系统是否因为有对象被阻塞导致，查询发现无对象被阻塞

3、查询系统等待事件，发现几十个read by other session等待，都是从一台web的服务器上连接过来
SELECT * FROM v$session WHERE wait_class#<>6;

4、read by other session等待事件比较陌生，幸好伴随有db file parallel write 的等待事件，初步怀疑读取数据到内存中等待导致

5、查询资料发现
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中分离出来的，也是需要一种热块现象

6、根据FILE#，BLOCK#查询热块对象
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;

7、通过这个对象查询出对应的操作语句
select * from v$sql where upper(sql_text) like ‘%object_name%’;

8、直接查找热点块对象语句

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 E.OWNER, E.SEGMENT_NAME, E.SEGMENT_TYPE

  FROM DBA_EXTENTS E,

       (SELECT *

          FROM (SELECT ADDR, TS#, FILE#, DBARFIL, DBABLK, TCH

                  FROM X$BH

                 ORDER BY TCH DESC)

         WHERE ROWNUM < 11) B

 WHERE E.RELATIVE_FNO = B.DBARFIL

   AND E.BLOCK_ID <= B.DBABLK

   AND E.BLOCK_ID + E.BLOCKS > B.DBABLK;

9、直接查找热点块操作语句

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;

二。详细的过程

read by other session
  
个人觉得，10G以后的buffer busy wait等待变的非常的‘单纯’。就是获得cache buffer chain latch以后，遍历chain的时候，如果数据块存在在chain上，那么就释放cache buffer chain latch，并且pin住它，如果此时有其他的session要pin这个数据块，那么就会产生buffer busy wait.
Oracle Dba必须了解的Read By Other Session等待：
微博里写了篇关于Buffer Busy Waits等待，这里再介绍一种跟它很相像的一个等待Read By Other Session，这个等待在10G前就是Buffer Busy Waits等待的一种情况，10G后把这个等待从Buffer Busy Waits等待的划分了出来。总的来说，这个等待的发生在由于：进程（我们假设进程A）需要访问的数据块不在内存里，因此不得不把数据块从磁盘LOAD到内存的过程中，另一个/些（假设进程B、C）也要读取这个数据块，这个时候进程A发生的等待可能是db file sequential read/db file parallel read/db file scattered read中的某一个或某几个，而进程B、C发生的等待就是我们今天要介绍的Read By Other Session，Oracle不会让多个进程去同时物理读取一个数据块LOAD入共享内存，只会让第一个进程去DISK读取，然后放入共享内存，这么做也是为了保护共享内存。步骤大致如下：
1）Oracle计算出查询所要请求数据块的地址，然后根据地址+块类计算出块所在buffer cache的Hash Bucket Number，然后获取到保护这个Hash Bucket的CBC Latch，在持有CBC Latch的情况下，在Hash Bucket里寻找目标Block是否存在在Bucket里，最终没有找到，释放CBC Latch。（可能Oracle 实际发生的情况与我描述的不符，看最后的解释）。
2）申请Lru Latch，搜索Lru-Aux链表找寻空闲块。其实这个链表是以buffer header上的指针串起来的，buffer header上还有指向buffer block的指针，找到后，修改buffer header的某些条目，比如把block address的信息设置上去，指向内存里的某个buffer，主要是标示内存里的这个块已经被使用了。
3）再次获取正确的CBC Latch，把这个buffer header链接到正确的hash chain上去，也就是把它放到正确的hash bucket里去。
4）以排他的x模式去pin这个buffer。这样别的会话就能知道这个buffer还不能访问。
5）释放Lru Latch，CBC Latch.
6）开始物理读取，这个时候前台进程等待db file sequential read/db file parallel read/db file scattered read ，具体等待那一种，看读取的类型，也可能会出现多种等待。
7）在物理读取期间，如果有其他会话也想访问这个数据块，等待Read By Other Session 。
8）读取结束后，申请CBC Latch，unpin这个buffer，释放CBC Latch。
10G前把这个等待归入到了Buffer Busy Waits等待里，这么归类也是有道理的：Buffer Busy Waits等待的本质是由于欲获得buffer lock的会话由于申请的锁模式跟buffer上已经存在的锁模式冲突导致的。而read by other session也符合这一核心要点。物理读取数据块的进程在对应的buffer上加了x模式的buffer lock，其他进程想要以s模式读取数据块，由于x模式的buffer lock，与任何其他锁模式都不兼容，因此产生了read by other session等待，也就是10G前的buffer busy waits等待中的一种情形。直接路径读取会不会产生read by other session？不会的，直接路径读取把数据读取在进程自己的PGA里，不涉及到共享内存的占用，这种情况下，即使有并发，各个进程间的等待也只是direct path read。步骤1我的描述里，进程在发现数据块不在内存后，释放了CBC Latch，然后去申请Lru Latch,获得空闲内存块后，再次去持有CBC Latch，而这个过程也非常可能是进程在发现数据块不在内存后，不释放CBC Latch，直接去持有Lru Latch。至于到底是哪个过程，我就不做验证了，有兴趣的可以去手工的持有latch验证下。我个人觉得不释放CBC Latch，直接去持有Lru Latch的可能性比较大:1）这样省去了释放CBC Latch最后又获得一次CBC Latch的资源消耗 2）Lru的Latch的级别设计的比CBC Latch级别高，因此在持有CBC Latch的情况下再去持有Lru Latch更显得顺理成章。当然这个只是我YY，理论上持有Lru Latch情况下，以immediate 方式去申请CBC Latch也是可以的。这里只是给大家大体介绍一下这个等待的一些基本情况，更深的细节暂时就不过多研究了。