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;
/
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;
