Sep 24: 11g and direct path reads
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!
#1.1 - Doug Burns said:
2007-09-24 20:28 - (Reply)
Direct path reads ARE going to operate faster than scattered reads, and have less impact on other processes because they avoid latches - so they are a good thing.
Oh, absolutely! That's what I picked up from the serial direct reads exercise from last year. I was just surprised to see it and, dare I say it, quite excited
Then again, I decided to run a couple of sessions scanning the same table because I wondered about each process running it's own reads without the benefit of the buffer cache (but I suppose that's limited anyway for FTS). Both sessions used direct path reads.
Still a lot to play around with though, as you say.
#1.1.1 - Marco Gralike said:
2007-09-24 22:34 - (Reply)
Just to tickle your paranoia...
I long time ago (when I was young ) and parallelism came to light, I once encountered a "feature" where when one table was created in parallel all statements were handled by the cost based optimizer, despite the instance rule based optimizer setting. Could it be some "strange" behaviour like described...
or just, missing out on a new hidden parameter / feature (http://julian.dyke.users.btopenworld.com/com/Internals/Parameters/Parameters.html)
#1.1.1.1 - Doug Burns said:
2007-09-24 22:45 - (Reply)
I went to look for that link today and then got distracted by other (real) work
This one certainly looks promising ...
http://julian.dyke.users.btopenworld.com/com/Internals/Parameters/adaptive_direct_read.html
Tracked: Jul 24, 21:04
Tracked: Jul 24, 21:07
Tracked: Jul 24, 21:08
Tracked: Apr 21, 06:03
Tracked: Jun 24, 10:42
Tracked: Jun 27, 20:33
Tracked: Aug 15, 23:56
Tracked: Sep 26, 16:32