{"id":774,"date":"2006-05-22T12:00:00","date_gmt":"2006-05-22T12:00:00","guid":{"rendered":"http:\/\/orcldoug.com\/blog\/?p=774"},"modified":"2006-05-22T12:00:00","modified_gmt":"2006-05-22T12:00:00","slug":"direct-path-reads","status":"publish","type":"post","link":"http:\/\/orcldoug.com\/blog\/2006\/05\/22\/direct-path-reads\/","title":{"rendered":"Direct Path Reads"},"content":{"rendered":"

After one of my Hotsos Presentations, I had a discussion with one of the delegates about how his site used parallel execution for some jobs purely to take advantage of Direct Path Reads<\/a>. He suggested that he thought that was why the parallel 2 tests I ran were always significantly faster than serial. I spoke to a couple of people about this and wondered how I could test it, until Jonathan Lewis pointed out the _serial_direct_read<\/a> hidden parameter<\/span>. The reason those last two words are in italics is that you shouldn’t set the values of hidden parameters without discussing it with support. You have been warned 😉<\/p>\n

As I don’t run my business on my server at home, it’ s safe for me to test (now that I’ve bitten the bullet and run a cable down to the server room – it’s the first decent playtime I’ve had for ages)<\/a>. Based on a simple test of a full table-scan on a 3-disk software RAID array, there was a small improvement, but I’m not sure it justifies switching to Direct Path Reads except where Oracle would normally use them anyway.<\/p>\n

I ran the tests multiples times, immediately after startup to pick up any timing variations, but the timings were pretty stable. I also looked through raw trace files to check that everything looked as expected.<\/p>\n

_serial_direct_read = false – average of 6 minutes
_serial_direct_read = true – average of 5 minutes 15 seconds<\/p>\n

So direct path was about 10-15% quicker for this very basic full scan of a 9Gb \/ 65 million row table on my particular configuration<\/p>\n

Updated 23rd May<\/span><\/p>\n

<\/p>\n

Based on comments from Alex and Jonathan, I thought it might be worth adding snippets of the first few trace lines for direct and non-direct i\/o. So you can blame them for the longer post 😉 I also had to re-run the tests because some other tests I’d been running overnight had deleted the existing trace files 🙁<\/p>\n

First the non-direct path version.<\/p>\n

PARSING IN CURSOR #1 len=59 dep=0 uid=27 oct=3 lid=27 tim=1121447319073032 hv=819013003 ad='7931a52c' select \/*+ noparallel(test_tab1) *\/ count(*) from test_tab1END OF STMTPARSE #1:c=258960,e=282301,p=30,cr=490,cu=0,mis=1,r=0,dep=0,og=1,tim=1121447319073015EXEC #1:c=1000,e=120,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1121447319073424WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1650815232 #bytes=1 p3=0 obj#=10321 tim=1121447319073538WAIT #1: nam='db file sequential read' ela= 261 file#=6 block#=9 blocks=1 obj#=10645 tim=1121447319074867WAIT #1: nam='db file scattered read' ela= 13255 file#=6 block#=10 blocks=128 obj#=10645 tim=1121447319090157WAIT #1: nam='db file scattered read' ela= 12671 file#=6 block#=138 blocks=128 obj#=10645 tim=1121447319107768WAIT #1: nam='db file scattered read' ela= 59119 file#=6 block#=266 blocks=128 obj#=10645 tim=1121447319170777WAIT #1: nam='db file scattered read' ela= 13354 file#=6 block#=394 blocks=128 obj#=10645 tim=1121447319187905WAIT #1: nam='db file scattered read' ela= 39190 file#=6 block#=522 blocks=128 obj#=10645 tim=1121447319231031WAIT #1: nam='db file scattered read' ela= 44925 file#=6 block#=650 blocks=128 obj#=10645 tim=1121447319279664WAIT #1: nam='db file scattered read' ela= 53011 file#=6 block#=778 blocks=128 obj#=10645 tim=1121447319336530WAIT #1: nam='db file scattered read' ela= 13912 file#=6 block#=906 blocks=128 obj#=10645 tim=1121447319354510WAIT #1: nam='db file scattered read' ela= 13537 file#=6 block#=1034 blocks=128 obj#=10645 tim=1121447319371915WAIT #1: nam='db file scattered read' ela= 155474 file#=6 block#=1162 blocks=128 obj#=10645 tim=1121447319531089WAIT #1: nam='db file scattered read' ela= 121519 file#=6 block#=1290 blocks=128 obj#=10645 tim=1121447319656727WAIT #1: nam='db file scattered read' ela= 14227 file#=6 block#=1418 blocks=128 obj#=10645 tim=1121447319674802WAIT #1: nam='db file scattered read' ela= 14165 file#=6 block#=1546 blocks=128 obj#=10645 tim=1121447319692961WAIT #1: nam='db file scattered read' ela= 14300 file#=6 block#=1674 blocks=128 obj#=10645 tim=1121447319711088WAIT #1: nam='db file scattered read' ela= 13211 file#=6 block#=1802 blocks=128 obj#=10645 tim=1121447319728129WAIT #1: nam='db file scattered read' ela= 12815 file#=6 block#=1930 blocks=128 obj#=10645 tim=1121447319744562WAIT #1: nam='db file scattered read' ela= 170935 file#=6 block#=2058 blocks=128 obj#=10645 tim=1121447319919103WAIT #1: nam='db file scattered read' ela= 13233 file#=6 block#=2186 blocks=128 obj#=10645 tim=1121447319936146WAIT #1: nam='db file scattered read' ela= 12734 file#=6 block#=2314 blocks=128 obj#=10645 tim=1121447319952560WAIT #1: nam='db file scattered read' ela= 12588 file#=6 block#=2442 blocks=128 obj#=10645 tim=1121447319968803WAIT #1: nam='db file scattered read' ela= 12784 file#=6 block#=2570 blocks=128 obj#=10645 tim=1121447319985244WAIT #1: nam='db file scattered read' ela= 12841 file#=6 block#=2698 blocks=128 obj#=10645 tim=1121447320001782WAIT #1: nam='db file scattered read' ela= 119155 file#=6 block#=2826 blocks=128 obj#=10645 tim=1121447320124638WAIT #1: nam='db file scattered read' ela= 14094 file#=6 block#=2954 blocks=128 obj#=10645 tim=1121447320142658WAIT #1: nam='db file scattered read' ela= 14177 file#=6 block#=3082 blocks=128 obj#=10645 tim=1121447320160695WAIT #1: nam='db file scattered read' ela= 14348 file#=6 block#=3210 blocks=128 obj#=10645 tim=1121447320179073WAIT #1: nam='db file scattered read' ela= 13571 file#=6 block#=3338 blocks=128 obj#=10645 tim=1121447320196489WAIT #1: nam='db file scattered read' ela= 12636 file#=6 block#=3466 blocks=128 obj#=10645 tim=1121447320212753WAIT #1: nam='db file scattered read' ela= 180444 file#=6 block#=3594 blocks=128 obj#=10645 tim=1121447320396801WAIT #1: nam='db file scattered read' ela= 13109 file#=6 block#=3722 blocks=128 obj#=10645 tim=1121447320413796WAIT #1: nam='db file scattered read' ela= 12731 file#=6 block#=3850 blocks=128 obj#=10645 tim=1121447320430198WAIT #1: nam='db file scattered read' ela= 12603 file#=6 block#=3978 blocks=127 obj#=10645 tim=1121447320446431WAIT #1: nam='db file scattered read' ela= 136330 file#=6 block#=12297 blocks=128 obj#=10645 tim=1121447320586371WAIT #1: nam='db file scattered read' ela= 221123 file#=6 block#=12425 blocks=128 obj#=10645 tim=1121447320811727WAIT #1: nam='db file scattered read' ela= 12813 file#=6 block#=12553 blocks=128 obj#=10645 tim=1121447320828202<\/pre>\n
Now the direct path version.<\/p>\n
PARSING IN CURSOR #2 len=59 dep=0 uid=27 oct=3 lid=27 tim=1121446553535587 hv=819013003 ad='792091ec'select \/*+ noparallel(test_tab1) **\/ count(*) from test_tab1END OF STMTPARSE #2:c=1000,e=210,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1121446553535576EXEC #2:c=0,e=180,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1121446553535975WAIT #2: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1121446553536079WAIT #2: nam='reliable message' ela= 178 channel context=2100923636 channel handle=2100769092 broadcastmessage=2101549144 obj#=-1 tim=1121446553537492WAIT #2: nam='enq: KO - fast object checkpoint' ela= 142 name|mode=1263468550 2=65548 0=1 obj#=-1 tim=1121446553537742WAIT #2: nam='direct path read' ela= 34 file number=6 first dba=10 block cnt=118 obj#=10645 tim=1121446553552515WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=128 block cnt=128 obj#=10645 tim=1121446553560209WAIT #2: nam='direct path read' ela= 37 file number=6 first dba=256 block cnt=128 obj#=10645 tim=1121446553567431WAIT #2: nam='direct path read' ela= 24 file number=6 first dba=384 block cnt=128 obj#=10645 tim=1121446553574642WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=512 block cnt=128 obj#=10645 tim=1121446553581789WAIT #2: nam='direct path read' ela= 22 file number=6 first dba=640 block cnt=128 obj#=10645 tim=1121446553589104WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=768 block cnt=128 obj#=10645 tim=1121446553596289WAIT #2: nam='direct path read' ela= 24 file number=6 first dba=896 block cnt=128 obj#=10645 tim=1121446553603503WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=1024 block cnt=128 obj#=10645 tim=1121446553610829WAIT #2: nam='direct path read' ela= 22 file number=6 first dba=1152 block cnt=128 obj#=10645 tim=1121446553618050WAIT #2: nam='direct path read' ela= 38 file number=6 first dba=1280 block cnt=128 obj#=10645 tim=1121446553625165WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=1408 block cnt=128 obj#=10645 tim=1121446553632616WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=1536 block cnt=128 obj#=10645 tim=1121446553639771WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=1664 block cnt=128 obj#=10645 tim=1121446553647160WAIT #2: nam='direct path read' ela= 38 file number=6 first dba=1792 block cnt=128 obj#=10645 tim=1121446553654347WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=1920 block cnt=128 obj#=10645 tim=1121446553661575WAIT #2: nam='direct path read' ela= 37 file number=6 first dba=2048 block cnt=128 obj#=10645 tim=1121446553668804WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=2176 block cnt=128 obj#=10645 tim=1121446553676065WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=2304 block cnt=128 obj#=10645 tim=1121446553683242WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=2432 block cnt=128 obj#=10645 tim=1121446553690664WAIT #2: nam='direct path read' ela= 38 file number=6 first dba=2560 block cnt=128 obj#=10645 tim=1121446553697846WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=2688 block cnt=128 obj#=10645 tim=1121446553705100WAIT #2: nam='direct path read' ela= 37 file number=6 first dba=2816 block cnt=128 obj#=10645 tim=1121446553712275WAIT #2: nam='direct path read' ela= 22 file number=6 first dba=2944 block cnt=128 obj#=10645 tim=1121446553719487WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=3072 block cnt=128 obj#=10645 tim=1121446553726668WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=3200 block cnt=128 obj#=10645 tim=1121446553734055WAIT #2: nam='direct path read' ela= 35 file number=6 first dba=3328 block cnt=128 obj#=10645 tim=1121446553741235WAIT #2: nam='direct path read' ela= 23 file number=6 first dba=3456 block cnt=128 obj#=10645 tim=1121446553748471WAIT #2: nam='direct path read' ela= 37 file number=6 first dba=3584 block cnt=128 obj#=10645 tim=1121446553755657WAIT #2: nam='direct path read' ela= 21 file number=6 first dba=3712 block cnt=128 obj#=10645 tim=1121446553762872WAIT #2: nam='direct path read' ela= 38 file number=6 first dba=3840 block cnt=128 obj#=10645 tim=1121446553770034WAIT #2: nam='direct path read' ela= 12 file number=6 first dba=3968 block cnt=128 obj#=10645 tim=1121446553772397WAIT #2: nam='direct path read' ela= 36 file number=6 first dba=4096 block cnt=9 obj#=10645 tim=1121446553778625<\/pre>\n
Later on, I might even be able to put some explanation round this! For now, it’s off to work. <\/p>\n","protected":false},"excerpt":{"rendered":"
After one of my Hotsos Presentations, I had a discussion with one of the delegates about how his site used parallel execution for some jobs purely to take advantage of Direct Path Reads. He suggested that he thought that was why the parallel 2 tests I ran were always significantly faster than serial. I spoke… Continue reading Direct Path Reads<\/span><\/a><\/p>\n","protected":false},"author":0,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-774","post","type-post","status-publish","format-standard","hentry","category-uncategorized","entry"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":1320,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/23\/parallel-query-and-11g-part-2\/","url_meta":{"origin":774,"position":0},"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":835,"url":"http:\/\/orcldoug.com\/blog\/2006\/03\/14\/px-and-the-magic-of-2\/","url_meta":{"origin":774,"position":1},"title":"PX and “the Magic of 2”","date":"March 14, 2006","format":false,"excerpt":"Now that I've had time to sit back and relax and absorb things a little, I thought I should fix a couple of outright bugs in the recent PX paper (Later update - link to the paper) and revisit the conclusions before moving on to testing different aspects. First, the\u2026","rel":"","context":"With 2 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1321,"url":"http:\/\/orcldoug.com\/blog\/2007\/09\/24\/11g-and-direct-path-reads\/","url_meta":{"origin":774,"position":2},"title":"11g and direct path reads","date":"September 24, 2007","format":false,"excerpt":"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 \ud83d\ude09 I've been playing around a little tonight, trying\u2026","rel":"","context":"With 27 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":926,"url":"http:\/\/orcldoug.com\/blog\/2005\/10\/24\/intel-isp4400\/","url_meta":{"origin":774,"position":3},"title":"Intel ISP4400","date":"October 24, 2005","format":false,"excerpt":"I've wanted to carry on investigating parallel execution for a while but, unless you happen to be working on a reasonably large data warehouse with a few spare test environments kicking around, it's difficult. Without wishing to reopen the whole debate about the value of single user test scripts (which\u2026","rel":"","context":"With 8 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":902,"url":"http:\/\/orcldoug.com\/blog\/2005\/11\/18\/hotsos-symposium-2006\/","url_meta":{"origin":774,"position":4},"title":"Hotsos Symposium 2006","date":"November 18, 2005","format":false,"excerpt":"My goodness, nearly a week without a blog posting and my review of Jonathan's book still hasn't appeared. It's been a crazy week at work and what little spare time I've had has been spent on getting my server installed. It looks like I won't be able to get wireless\u2026","rel":"","context":"With 6 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":1395,"url":"http:\/\/orcldoug.com\/blog\/2008\/03\/25\/ash-and-the-psychology-of-hidden-parameters\/","url_meta":{"origin":774,"position":5},"title":"ASH and the psychology of Hidden Parameters","date":"March 25, 2008","format":false,"excerpt":"Time for a quick break from the final push to complete the course slides. I've (probably foolishly) decided to apply the 10.2.0.4 patch to my test database.As I was confirming the details of when Oracle starts to flush information from the ASH Buffer to the workload repository, I thought I'd\u2026","rel":"","context":"With 5 comments","img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/774","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"}],"replies":[{"embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/comments?post=774"}],"version-history":[{"count":0,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/posts\/774\/revisions"}],"wp:attachment":[{"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/media?parent=774"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/categories?post=774"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/orcldoug.com\/blog\/wp-json\/wp\/v2\/tags?post=774"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}