read by other session等待事件模擬

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

客戶系統最近這個事件表現的比較突出，簡單的模擬了一下，這是一個和io相關的讀等待事件，因此它也伴隨著db file sequential read和db file sequential read一起出現

[@more@]

session 1:
SQL> select distinct sid from v$mystat;

SID
----------
159
SQL> 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 tt;
7 execute immediate v_string;
8 end loop;
9 end;
10 /

--==========================
session 2:
SQL> select distinct sid from v$mystat;

SID
----------
144

SQL> 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 tt;
7 execute immediate v_string;
8 end loop;
9 end;
10 /

--=========================
session 3:
SQL> select distinct sid from v$mystat;

SID
----------
142

SQL> 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 tt;
7 execute immediate v_string;
8 end loop;
9 end;
10 /

--===========================
session 4:(監控)
SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 read by other session file# block# class# User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file sequential read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 read by other session file# block# class# User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 read by other session file# block# class# User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file sequential read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 read by other session file# block# class# User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 read by other session file# block# class# User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 read by other session file# block# class# User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 read by other session file# block# class# User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL> select sid,event,p1text,p2text,p3text,wait_class from v$session_wait where
sid in (159,144,142);

SID EVENT P1TEXT P2TEXT P3TEXT WAIT_CLASS

---------- ------------------------- ---------- ---------- ---------- ----------

142 db file scattered read file# block# blocks User I/O
144 db file scattered read file# block# blocks User I/O
159 db file scattered read file# block# blocks User I/O

SQL>