Sep 24: 11g and direct path reads
Curioser and curioser ....
I've been thinking about this on and off today and I really can't understand the behaviour I'm seeing. In fact, it's a good job it was a busy day or it might have sent me round the twist
I've been playing around a little tonight, trying to find the obvious mistake I'm making and I can't see it. I tried various different statements on some smaller test tables with similar results.
One of the comments Jonathan Lewis left in the last blog gave me food for thought, though. He commented on the lack of a segment checkpoint at the start of the full table scan.
"One interesting omission is the absence of the segment checkpoint that normally precedes a direct path tablescan."
Maybe the FTS is using direct path reads because it's a straightforward read of the table and no blocks for that segment exist in the buffer cache yet? I decided to try updating one of the rows, which would perform a full table scan (there are no indexes on this table) which I expected to use db file scattered reads.
First I performed another FTS, just to make sure that it was using direct path reads. It was, as confirmed by both an ASH report and a trace file (I'm starting to get paranoid!)
So I picked out a row to update and updated it. This would require a FTS because I'm using an unindexed column to identify the row.
I monitored this while it was running and finally, from an ASH report, I could see the wait event I was looking for.
I committed the update. So now there should be multiple blocks of this table in the buffer cache, including one that's been updated. In another session I performed the same simple select count against test_tab2 and this time, there was a segment checkpoint in there.
It's still using direct path reads, though, and not a suggestion of parallelism anywhere!
I've been thinking about this on and off today and I really can't understand the behaviour I'm seeing. In fact, it's a good job it was a busy day or it might have sent me round the twist
I've been playing around a little tonight, trying to find the obvious mistake I'm making and I can't see it. I tried various different statements on some smaller test tables with similar results.
One of the comments Jonathan Lewis left in the last blog gave me food for thought, though. He commented on the lack of a segment checkpoint at the start of the full table scan.
"One interesting omission is the absence of the segment checkpoint that normally precedes a direct path tablescan."
Maybe the FTS is using direct path reads because it's a straightforward read of the table and no blocks for that segment exist in the buffer cache yet? I decided to try updating one of the rows, which would perform a full table scan (there are no indexes on this table) which I expected to use db file scattered reads.
First I performed another FTS, just to make sure that it was using direct path reads. It was, as confirmed by both an ASH report and a trace file (I'm starting to get paranoid!
)SQL> select count(*) from test_tab2; COUNT(*) ---------- 65536000
So I picked out a row to update and updated it. This would require a FTS because I'm using an unindexed column to identify the row.
SQL> select * from test_tab2 where rownum=1;
PK_ID NUM_CODE
---------- ----------
STR_PAD
--------------------------------------------------------------------------------
1 1
tReEjSRqBLQHiFYNCokRvNANVmohDvdLuuLbtQHwlgwIjNhXezdYxjeOESlmOvBvFbicWgKoUgZfbhkz
mRFAfIZyMxOHLJrqIfkF
SQL> update test_tab2 set str_pad='DOUG_TEST' where pk_id=1;
1 row updated.I monitored this while it was running and finally, from an ASH report, I could see the wait event I was looking for.
Event Event Class % Event Sessions ----------------------------------- --------------- ---------- ---------- db file scattered read User I/O 59.15 0.34 CPU + Wait for CPU CPU 30.99 0.18
I committed the update. So now there should be multiple blocks of this table in the buffer cache, including one that's been updated. In another session I performed the same simple select count against test_tab2 and this time, there was a segment checkpoint in there.
PARSING IN CURSOR #3 len=30 dep=0 uid=88 oct=3 lid=88 tim=1190662307795682 hv=3408193527
ad='7a0e6e68' sqlid='5f6zrrv5k9tzr'
select count(*) from test_tab2
END OF STMT
PARSE #3:c=5000,e=4564,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1190662307795665
EXEC #3:c=0,e=231,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1190662307796185
WAIT #3: nam='SQL*Net message to client' ela= 8 driver id=1650815232 #bytes=1 p3=0
obj#=5808 tim=1190662307796296
WAIT #3: nam='reliable message' ela= 25 channel context=2042343360 channel handle=2042172560
broadcast message=2043267192 obj#=5808 tim=1190662307801012
WAIT #3: nam='reliable message' ela= 340 channel context=2042343360 channel handle=2042172560
broadcast message=2043267192 obj#=5808 tim=1190662307801451
WAIT #3: nam='enq: KO - fast object checkpoint' ela= 1994 name|mode=1263468550 2=65556 0=1
obj#=5808 tim=1190662307803549
WAIT #3: nam='direct path read' ela= 32596 file number=6 first dba=4106 block cnt=22 obj#=70941
tim=1190662307841063
WAIT #3: nam='direct path read' ela= 58 file number=6 first dba=4128 block cnt=96 obj#=70941
tim=1190662307844828It's still using direct path reads, though, and not a suggestion of parallelism anywhere!
Comments