Monday, September 16, 2013

Execution Switching between "direct path read" and "db file scattered read"

Oracle’s locking principle: blocking is only eligible among writers. Reader never blocks writer, and writer never blocks reader.

    What happens when underlying table is changed during "direct path read" ?

As we know, "direct path read" bypasses Buffer Cache, and directly fetches data from disk files into session private PGA. When data is modified, the disk files can contain the new data which is later than the starting point of already running query. In order to read the consistent version of data, the query has to visit Buffer Cache to reconstruct them by applying UNDO. Thus it has to use a different accessing approach.

Continuing from the example of Blog: Impact of Direct Reads on Delayed Block Cleanouts, we add a few more steps to demonstrate that Oracle dynamically adjusts the access methods during query execution.

The test is performed on Oracle 11.2.0.3.0 with db_cache_size = 304M.

At first, start 3 Oracle Sessions:
    Session_1: monitor sesssion
  Session_2: select sesssion
  Session_3: delete sesssion


In Session_1, prepare test by:
 drop table t1;
 drop table t2;
 create table t1 (id number, pad varchar2(500)) ;
 insert into t1 select rownum, rpad('*', 500, '*') from dual connect by level <= 140000;
 commit;
 create table t2 as select rownum id from dual connect by level < = 100000;
 alter system flush buffer_cache;


In Session_2, run query:
   select /*+ leading(t2) use_nl(t1) */ count(*) from t1, t2;

Back to Session_1, run query:
  select event, p1text, p1, p2text, p2, p3text, p3, row_wait_obj#, sql_exec_id, seq#
   from v$session where sid = &sid_2;

 
We can see the event: "direct path read" on table T1.

To understand the internal implementation, run
 select file#, block#, class#
   from v$bh b, dba_objects d
  where object_name = 'T1' and objd = data_object_id and b.status = 'xcur' and class# != 1;


which lists the special blocks loaded in Buffer Cache:

  FILE#   BLOCK#  CLASS#
  -----  -------  ------
      6  1349410       4 ('segment header')
      6  1364513       7 ('extent map')

  
The reason of "direct path read" still reads these blocks into Buffer Cache is to let all sessions share them. And probably it is acting as a central synchronizing role.

If we make a block dump of above blocks, we can see all the extents, which equals to:
  select extents from dba_segments where segment_name = 'T1';

The dumps also include something like: objq, objaq, which are often mentioned in the discussion of "direct path read".

Run one more query:
   select dbms_rowid.rowid_block_number(rowid) from t1 where id = 600;

to get the block number of the row to be deleted in Session_3:
    1369733

Go to Session_3, delete one row:
  delete from t1 where id = 600;

Back again to Session_1, run query again:
  select event, p1text, p1, p2text, p2, p3text, p3, row_wait_obj#, sql_exec_id, seq#
   from v$session where sid = &sid_2;


now we see the event: "db file scattered read" or "db file sequential read" on table T1.

Run query:
 select event, p1, p2, p3, d.object_name, sql_exec_id, seq#
 from   v$active_session_history t, dba_objects d
 where  d.object_name in ('T1', 'T2')
     and t.current_obj# = d.data_object_id
     and session_id = &sid_2 and session_serial# = &serial_2
 group by d.object_name, sql_exec_id, seq#, event, p1, p2, p3
 order by min(sample_time);


we get:

 EVENT                   P1      P2 P3 OBJECT_NAME  SQL_EXEC_ID   SEQ#
 ---------------------- --- ------- -- ------------ ----------- ------
 direct path read         6 1369216 32 T1              16777216  39127
 db file scattered read   6 1369728 16 T1              16777216  49540


If we go back again to Session_3, make a commit or rollback, Session_2 will not switch back to "direct path read".

However, if we flush out all dirty buffers by:
    alter system flush buffer_cache;
 or
    alter system checkpoint;

Session_2 will switch back to "direct path read".

Query:
 select event, p1, p2, p3, d.object_name, sql_exec_id, seq#
 from   v$active_session_history t, dba_objects d
 where  d.object_name in ('T1', 'T2')
     and t.current_obj# = d.data_object_id
     and session_id = &sid and session_serial# = &serial
 group by d.object_name, sql_exec_id, seq#, event, p1, p2, p3
 order by min(sample_time);


confirms these dynamic changing:

 EVENT                   P1      P2 P3 OBJECT_NAME  SQL_EXEC_ID   SEQ#
 ---------------------- --- ------- -- ------------ ----------- ------
 direct path read         6 1369216 32 T1              16777216  39127
 db file scattered read   6 1369728 16 T1              16777216  49540
 direct path read         6 1349411 13 T1              16777216  63731
 

Now it raises an interesting question:

 How Oracle guarantees correct result when switching back to "direct path read" ?
 since the data file now contains fresh data which is not yet visible to the select session.

A 10046 dump trace will help us understand it:

  nam='direct path read' file number=6 first dba=1369216 block cnt=32 obj#=596657 tim=9813000274679
  nam='db file sequential read' file#=6 block#=1364873 blocks=1 obj#=596657 tim=9813012825308
    ......
  nam='db file scattered read' file#=6 block#=1369712 blocks=16 obj#=596657 tim=9813012877616
  nam='db file scattered read' file#=6 block#=1369728 blocks=16 obj#=596657 tim=9813012877787

           (read extent of deleted row)
  nam='db file sequential read' file#=3 block#=626 blocks=1 obj#=0 tim=9813012877868

           (read UNDO block)
  nam='db file sequential read' file#=6 block#=1349410 blocks=1 obj#=596657 tim=9813012877986

           (read segment header block)
  nam='direct path read' file number=6 first dba=1349411 block cnt=13 obj#=596657 tim=9813012878205


In the above dump,

1. obj#=596657 is Table T1.

2. line: 'db file scattered read' file#=6 block#=1369728 blocks=16
    is to read extent starting from block#=1369728, and having 16 blocks,
    which contains the block 1369733 of the above deleted row (id = 600)
    ( 1369728 <= 1369733 <= (1369728+16-1) ).

3. line: 'db file sequential read' file#=3 block#=626 blocks=1 obj#=0
    is to read UNDO block which contains the before image of the deleted row.

From the dump, we can see that immediately following "db file scattered read" (file#=6 block#=1369728) is an UNDO block read (file#=3 block#=626), and then, it is a "segment header" read of block#=1349410,afterwords, it continues with "direct path read".

So "direct path read" can also use UNDO to get the consistent version of modified blocks.

In above test, we only execute once a delete statement. If we loop over a DML update statement:

begin
  for i in 1..600 loop
    update t1 set pad = rpad('*', 400, '*'||i) where id = i*100;
    dbms_session.sleep(1);
    execute immediate 'alter system flush buffer_cache';
  end loop;
end;
/
Then in Session_1, run following query to observe Session_2 activity. We can observe the toggling between single block read and multi block read (parameter p3 is varied between 1 and 32 (or some other number > 1)). Among them, there are also some read of UNOD blocks.

select program, session_id, session_serial#, event, p1, p2, p3, p1text, p2text, p3text, v.* 
from   v$active_session_history v
where  session_id = &sid_2 and sample_time > sysdate - 10/1440
order by sample_time desc;