【等待事件】User I/O类 等待事件（2.8）--read by other session
当我们查询一条数据时，Oracle第一次会将数据从磁盘读入 buffer cache。如果有两个或者多个session请求相同的信息，那么第一个session会将这个信息读入buffer cache，其他的session就会出现等待。
SELECT A.*
FROM V$EVENT_NAME A
WHERE NAME IN ('read by other session');
P1 = file# Absolute File# (AFN)This is the file number of the data file that contains the block that the waiting session wants.
P2 = block# This is the block number in the above file# that the waiting session wants access to. See Note:181306.1 to determine the tablespace, filename and object for this file#,block# pair.
P3 = class# Block class
This is the class of block being waited on. In particular:
class 1 indicates a "data block", which could be table or index
class 4 indicates a "segment header"
class >=15 indicate undo blocks
我们可以根据P1和P2参数值获取到等待的对象名称和类型：
SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
FROM DBA_EXTENTS
WHERE FILE_ID = FILE#
AND BLOCK#
BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
另外，其实我们也可以根据v$session的ROW_WAIT_OBJ#列获取到等待的对象的名称，SQL如下：
SELECT A.ROW_WAIT_OBJ#,
B.OBJECT_NAME,
A.SQL_ID,
A.SID,
A.BLOCKING_SESSION,
A.EVENT,
A.P1TEXT,
A.P1,
A.P2TEXT,
A.P2,
A.P3TEXT,
A.P3,
A.WAIT_CLASS
FROM V$SESSION A, DBA_OBJECTS B
WHERE A.ROW_WAIT_OBJ# = B.OBJECT_ID
AND A.EVENT='read by other session';
其实 read by other session 是在 Oracle 10g （10.1.0.2 and later） 新引入的一个等待事件，在 10g 以前版本，等待为 buffer busy waits，10g以后做的细分，所以才有了 read by other session。
Oracle官方解释如下：
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同时出现。
下面是在 Metalink 上的解释：
Solution：
This wait event occurs when we are trying to access a buffer in the buffer
cache but we find that the buffer is currently being read from disk by another user
so we need to wait for that to complete before we can access it. In previous versions, this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher, the 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。
一般来说出现这种等待事件是因为多个进程重复的读取相同的 blocks，比如一些session 扫描相同的 index或者在相同的block上执行 full table scan。解决这个等待事件最好是找到并优化相关的SQL语句
1. 如果系统中有这种等待事件，我们可以通过以下SQL查询v$session_wait得到详细信息
SELECT p1 "file#", p2 "block#", p3 "class#"
FROM v$session_wait
WHERE event = 'read by other session';
2. 如果上述查询出的结果是热块造成的，运行如下 SQL，查询出具体对象信息，其实这部分可以直接从 AWR的Segments by Buffer Busy Waits 看出来。
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;
3. 可以通过下面的SQL脚本查询到具体的SQL语句
SELECT 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.BLOCKS > B.DBABLK) B
WHERE A.SQL_TEXT LIKE '%' || B.SEGMENT_NAME || '%'
AND B.SEGMENT_TYPE = 'TABLE')
ORDER BY HASH_VALUE, ADDRESS, PIECE;
4.查看对应 SQL 的执行计划是否最优，必要时可以通过 DBMS_SQLTUNE 包迚行优化，通过SQL_PROFILE 文件稳固执行计划
5.查看表和索引的统计信息是否陈旧，必要时收集统计信息
read by other session等待事件模拟
首先，建表TB_RBOS_20160829_LHR：
SYS@lhrdb> SELECT * FROM V$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SYS@lhrdb> CREATE TABLE TB_RBOS_20160829_LHR AS SELECT * FROM DBA_OBJECTS;
Table created.
SYS@lhrdb> INSERT INTO TB_RBOS_20160829_LHR SELECT * FROM TB_RBOS_20160829_LHR;
87145 rows created.
SYS@lhrdb> INSERT INTO TB_RBOS_20160829_LHR SELECT * FROM TB_RBOS_20160829_LHR;
174290 rows created.
SYS@lhrdb> INSERT INTO TB_RBOS_20160829_LHR SELECT * FROM TB_RBOS_20160829_LHR;
348580 rows created.
SYS@lhrdb> INSERT INTO TB_RBOS_20160829_LHR SELECT * FROM TB_RBOS_20160829_LHR;
697160 rows created.
SYS@lhrdb> INSERT INTO TB_RBOS_20160829_LHR SELECT * FROM TB_RBOS_20160829_LHR;
1394320 rows created.
SYS@lhrdb> COMMIT;
Commit complete.
我们开3个session分别清空buffer同时对表TB_RBOS_20160829_LHR做统计操作：
session 1:
SYS@lhrdb> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
190
SYS@lhrdb> DECLARE
2 I NUMBER := 0;
3 V_STRING VARCHAR2(50) := 'alter system flush buffer_cache';
4 BEGIN
5 LOOP
6 SELECT COUNT(*) INTO I FROM TB_RBOS_20160829_LHR;
7 EXECUTE IMMEDIATE V_STRING;
8 END LOOP;
9 END;
10 /
session 2:
SYS@lhrdb> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
5
SYS@lhrdb> DECLARE
2 I NUMBER := 0;
3 V_STRING VARCHAR2(50) := 'alter system flush buffer_cache';
4 BEGIN
5 LOOP
6 SELECT COUNT(*) INTO I FROM TB_RBOS_20160829_LHR;
7 EXECUTE IMMEDIATE V_STRING;
8 END LOOP;
9 END;
10 /
session 3:
SYS@lhrdb> SELECT DISTINCT SID FROM V$MYSTAT;
SID
----------
68
SYS@lhrdb> DECLARE
2 I NUMBER := 0;
3 V_STRING VARCHAR2(50) := 'alter system flush buffer_cache';
4 BEGIN
5 LOOP
6 SELECT COUNT(*) INTO I FROM TB_RBOS_20160829_LHR;
7 EXECUTE IMMEDIATE V_STRING;
8 END LOOP;
9 END;
10 /
session 4:(监控)
SELECT A.SID,A.BLOCKING_SESSION,A.EVENT,A.P1TEXT,A.P1,A.P2TEXT,A.P2,A.P3TEXT,A.P3,A.WAIT_CLASS
FROM V$SESSION A
WHERE SID IN (190, 5, 68);
由于是11g，满足DPR的特性，所以会走direct path read绕过SGA直接读取数据到PGA的，我们先禁用该特性:
SYS@lhrdb> alter system set "_serial_direct_read"=never scope=both sid='*';
System altered.
禁用之后继续查询：
可以看到等待事件已经变为了read by other session了，当然我们也可以在表上创建索引CREATE INDEX IND_XX_LHR ON TB_RBOS_20160829_LHR(OBJECT_ID) NOLOGGING;然后查询的时候SELECT COUNT(object_id) INTO I FROM TB_RBOS_20160829_LHR;可以走索引，这样的话模拟出来的也是read by other session等待事件了。
我们根据P1和P2参数值获取到访问的对象名称和类型：
SELECT SEGMENT_NAME, SEGMENT_TYPE, OWNER, TABLESPACE_NAME
FROM DBA_EXTENTS
WHERE FILE_ID =1
AND 162048
BETWEEN BLOCK_ID AND BLOCK_ID + BLOCKS - 1;
另外，其实我们也可以根据v$session的ROW_WAIT_OBJ#列获取到等待的对象的名称，SQL如下：
SELECT A.ROW_WAIT_OBJ#,
B.OBJECT_NAME,
A.SQL_ID,
A.SID,
A.BLOCKING_SESSION,
A.EVENT,
A.P1TEXT,
A.P1,
A.P2TEXT,
A.P2,
A.P3TEXT,
A.P3,
A.WAIT_CLASS
FROM V$SESSION A, DBA_OBJECTS B
WHERE A.ROW_WAIT_OBJ# = B.OBJECT_ID
AND SID IN(190,5,68);
SELECT * FROM V$SQL A WHERE A.SQL_ID='97pq51643d7b5';
找到SQL_ID，剩下的就是优化SQL语句了。
更多相关文章
- 【等待事件】User I/O类 等待事件（2.7）--direct path read/write t
- 【故障处理】队列等待之TX - allocate ITL entry引起的死锁处理（
- 【等待事件】日志类 等待事件（4.4）--log buffer space（日志缓冲空间
- 【等待事件】System I/O类 等待事件（3.1）--db file parallel write
- 【故障处理】队列等待之enq: US - contention案例
- 【等待事件】日志类 等待事件（4.5）--log file sequential read
- 【等待事件】System I/O类 等待事件（3.2）--control file parallel
- 【故障处理】队列等待之enq IV - contention案例
- 【等待事件】日志类 等待事件（4.1）--log file switch（日志文件切换）