【等待事件】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');

【等待事件】User I/O类 等待事件(2.8)--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);

【等待事件】User I/O类 等待事件(2.8)--read by other session 

由于是11g,满足DPR的特性,所以会走direct path read绕过SGA直接读取数据到PGA的,我们先禁用该特性:

SYS@lhrdb> alter system set "_serial_direct_read"=never scope=both sid='*';

 

System altered.

禁用之后继续查询:

【等待事件】User I/O类 等待事件(2.8)--read by other session 

可以看到等待事件已经变为了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; 

【等待事件】User I/O类 等待事件(2.8)--read by other session 

另外,其实我们也可以根据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);

【等待事件】User I/O类 等待事件(2.8)--read by other session 

SELECT * FROM V$SQL A WHERE A.SQL_ID='97pq51643d7b5';

【等待事件】User I/O类 等待事件(2.8)--read by other session 

找到SQL_ID,剩下的就是优化SQL语句了。 


上一篇:【函数】wm_concat包的订制


下一篇:【DB笔试面试459】ORA-00904: