“One interesting omission is the absence of the segment checkpoint that normally precedes a direct path tablescan.”<\/em>
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.<\/p>\nFirst 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! 😉)<\/p>\n
SQL> select count(*) from test_tab2;\n\n COUNT(*)\n----------\n 65536000<\/pre>\nSo 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.<\/p>\n
SQL> select * from test_tab2 where rownum=1;\n\n PK_ID NUM_CODE\n---------- ----------\nSTR_PAD\n--------------------------------------------------------------------------------\n 1 1\ntReEjSRqBLQHiFYNCokRvNANVmohDvdLuuLbtQHwlgwIjNhXezdYxjeOESlmOvBvFbicWgKoUgZfbhkz\nmRFAfIZyMxOHLJrqIfkF\n\nSQL> update test_tab2 set str_pad='DOUG_TEST' where pk_id=1;\n\n1 row updated.<\/pre>\nI monitored this while it was running and finally, from an ASH report, I could see the wait event I was looking for.<\/p>\n
Event Event Class % Event Sessions\n----------------------------------- --------------- ---------- ----------\ndb file scattered read User I\/O 59.15 0.34\nCPU + Wait for CPU CPU 30.99 0.18<\/pre>\nI 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. <\/p>\n
PARSING IN CURSOR #3 len=30 dep=0 uid=88 oct=3 lid=88 tim=1190662307795682 hv=3408193527 \n ad='7a0e6e68' sqlid='5f6zrrv5k9tzr'\nselect count(*) from test_tab2\nEND OF STMT\nPARSE #3:c=5000,e=4564,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=1190662307795665\nEXEC #3:c=0,e=231,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1190662307796185\nWAIT #3: nam='SQL*Net message to client' ela= 8 driver id=1650815232 #bytes=1 p3=0 \n obj#=5808 tim=1190662307796296\nWAIT #3: nam='reliable message' ela= 25 channel context=2042343360 channel handle=2042172560 \n broadcast message=2043267192 obj#=5808 tim=1190662307801012\nWAIT #3: nam='reliable message' ela= 340 channel context=2042343360 channel handle=2042172560 \n broadcast message=2043267192 obj#=5808 tim=1190662307801451\nWAIT #3: nam='enq: KO - fast object checkpoint<\/strong>' ela= 1994 name|mode=1263468550 2=65556 0=1 \n obj#=5808 tim=1190662307803549\nWAIT #3: nam='direct path read<\/strong>' ela= 32596 file number=6 first dba=4106 block cnt=22 obj#=70941 \n tim=1190662307841063\nWAIT #3: nam='direct path read<\/strong>' ela= 58 file number=6 first dba=4128 block cnt=96 obj#=70941 \n tim=1190662307844828<\/pre>\nIt’s still using direct path reads, though, and not a suggestion of parallelism anywhere!<\/p>\n","protected":false},"excerpt":{"rendered":"
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… Continue reading 11g and direct path reads<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1321","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1284,"url":"http:\/\/orcldoug.com\/blog\/2007\/06\/10\/non-reusable-space\/","url_meta":{"origin":1321,"position":0},"title":"Non-reusable Space?","date":"June 10, 2007","format":false,"excerpt":"A problem cropped up at work this week and, whilst it wasn't particularly tricky and didn't take long to solve, it struck me that the story of tracking it down might be useful and you might hit the problem yourself one day.A recently-deployed Data Warehouse database at work has a\u2026","rel":"","context":"With 13 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1320,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g-part-2\/","url_meta":{"origin":1321,"position":1},"title":"Parallel Query and 11g – Part 2","date":"September 23, 2007","format":false,"excerpt":"Now, that's weird. A little surprising might be more accurate and maybe I'm missing something.During the various tests with and without parallel hints and different parallel_io_cap_enabled settings, I expected the runs that didn't use parallelism to show up \"db file scattered read\" events in the trace files. For example, here's\u2026","rel":"","context":"With 10 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":962,"url":"http:\/\/orcldoug.com\/blog\/2006\/06\/11\/time-for-a-vote-i-think\/","url_meta":{"origin":1321,"position":2},"title":"Time for a vote, I think","date":"June 11, 2006","format":false,"excerpt":"Well there are a lot of new templates on this blog and you can choose your own. Many of them don't work too well with the current content. The Direct Path Reads blog is a particularly interesting template test \ud83d\ude41I'd be interested, though, to hear which are the most popular\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":929,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/21\/book-review-oracle-insights-tales-of-the-oak-table\/","url_meta":{"origin":1321,"position":3},"title":"Book Review: Oracle Insights, Tales of the Oak Table","date":"October 21, 2005","format":false,"excerpt":"I've been looking for something to read about Oracle recently and couldn't wait until Jonathan Lewis' CBO book arrived. I knew of TOTOT (as Mogens N\ufffdrgaard has referred to it) and planned to buy it at some stage because I've loved past work by some of the authors, but didn't\u2026","rel":"","context":"With 1 comment","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1382,"url":"http:\/\/orcldoug.com\/blog\/2008\/02\/22\/how-useful-are-diagnosticoptimization-tools-another-view\/","url_meta":{"origin":1321,"position":4},"title":"“How useful are diagnostic\/optimization tools?” – Another View","date":"February 22, 2008","format":false,"excerpt":"There have been a couple of very interesting blog postings over the past few weeks from Daniel Fink and Alex Gorbachev, prompted by a panel discussion at last week's RMOUG conference. Well, I suspect the panel was prompted by an earlier blog posting and many late night conversations. I've just\u2026","rel":"","context":"With 25 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1363,"url":"http:\/\/orcldoug.com\/blog\/2007\/12\/04\/murali-vallath\/","url_meta":{"origin":1321,"position":5},"title":"Murali Vallath","date":"December 4, 2007","format":false,"excerpt":"When I blogged about Openworld, I decided not to mention one presentation at the time. I attended Murali Vallath's \"Oracle Parallel Query Feature in a RAC environment\" on Sunday afternoon. I almost didn't because I know the subject pretty well, but I thought I might learn something or see a\u2026","rel":"","context":"With 42 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1321","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=1321"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/1321\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=1321"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=1321"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=1321"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}