Oracle "read by other session" Wait Event

When a session needs to read data from a block on disk into the Oracle buffer cache, it may have to wait for another session to finish doing the same thing. Time spent doing this is recorded as a read by other session event. I decided to reproduce this in an Oracle 12 database. First, in session 1, in red, I set up a user called Fred to create a table:

SQL> conn / as sysdba

Connected.

SQL> create user fred

  2  identified by bloggs

  3  default tablespace users

  4  quota unlimited on users

  5  /

 

User created.

 

SQL> grant create session, create table,

  2  select any dictionary, alter system to fred

  3  /

 

Grant succeeded.

 

SQL>

Still in session 1, Fred then created a table, added some data and checked that all the rows were in the same block:

SQL> conn fred/bloggs

Connected.

SQL> create table t1 (c1 number)

  2  /

 

Table created.

 

SQL> begin

  2  for i in 1..200 loop

  3  insert into t1 values(1);

  4  end loop;

  5  end;

  6  /

 

PL/SQL procedure successfully completed.

 

SQL> select dbms_rowid.rowid_block_number(rowid), count(*)

  2  from t1

  3  group by dbms_rowid.rowid_block_number(rowid)

  4  order by 1

  5  /

 

DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)   COUNT(*)

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

                                 223        200

 

SQL>

 

Then he started updating the data. After each update, he did a commit so that other sessions could see the changed data and a checkpoint to write the changes to disk:

SQL> begin

  2  while (1=1) loop

  3  update t1 set c1 = c1 + 1;

  4  commit;

  5  execute immediate 'alter system checkpoint';

  6  end loop;

  7  end;

  8  /

 

In 5 separate sessions in blue (sessions 2 through 6 inclusive), Fred repeatedly flushed the Oracle buffer cache and read the table into it: 

SQL> conn fred/bloggs

Connected.

SQL> declare

  2  grand_total number;

  3  begin

  4  while (1=1) loop

  5  execute immediate 'alter system flush buffer_cache';

  6  select sum(c1) into grand_total from t1;

  7  end loop;

  8  end;

  9  /

In a 7^th session, in green, Fred read the table into the buffer cache 10000 times. While he was doing this he had to wait for sessions 2 through 6, which were doing the same thing:

SQL> conn fred/bloggs

Connected.

SQL> declare

  2  grand_total number;

  3  begin

  4  for i in 1..10000 loop

  5  execute immediate 'alter system flush buffer_cache';

  6  select sum(c1) into grand_total from t1;

  7  end loop;

  8  end;

  9  /

 

PL/SQL procedure successfully completed.

 

SQL>

So, when he finished, the time spent waiting on the read by other session event could clearly be seen: 

SQL> select event, time_waited/100

  2  from v$session_event

  3  where sid = (select distinct sid from v$mystat)

  4  /

 

EVENT                               TIME_WAITED/100

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

Disk file operations I/O                        .03

latch: cache buffers chains                     .01

buffer busy waits                                 0

read by other session                         33.36

db file sequential read                        7.35

db file scattered read                        22.07

db file parallel read                            .2

latch: In memory undo latch                       0

latch: row cache objects                          0

library cache: mutex X                            0

SQL*Net message to client                         0

SQL*Net message from client                     .02

events in waitclass Other                    405.87

 

13 rows selected.

 

SQL>