Thursday, February 19, 2015

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 7th 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>

No comments: