tag:blogger.com,1999:blog-4169710303065679169.post2157347402770955131..comments2024-03-18T04:20:59.682-07:00Comments on A Wider View : BLOG HAS MOVED *<a href="http://blog.orapub.com">HERE</a>*: The mystery surrounding 11g and direct readsCraig Shallahamer, President/Founder, OraPubhttp://www.blogger.com/profile/04109635337570098781noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-4169710303065679169.post-15542050235701428152011-10-05T09:52:49.147-07:002011-10-05T09:52:49.147-07:00Wow..outstanding experiment Craig.
I faced similar...Wow..outstanding experiment Craig.<br />I faced similar problem a while ago.<br />I was reading CLOB Column from a 9.2.0.8 system,the table size is around 10G.It was very slow (took around 30 mins on my powerful server) and it was doing scattered reads<br />on the other hand same read from same table on a default 11GR2 system took only 4 mins where it was doing direct path read :)<br />This is the first time ORACLE's so called intelligent feature worked well for me (Now i am afraid what are the bugs it's gonna bring it with this feature :) )Unknownhttps://www.blogger.com/profile/14660558716605746531noreply@blogger.comtag:blogger.com,1999:blog-4169710303065679169.post-85232630140876006392010-02-23T01:32:39.869-08:002010-02-23T01:32:39.869-08:00Hi Craig,
A side note on the topic: in 11g one can...Hi Craig,<br />A side note on the topic: in 11g one can use alter session set "_serial_direct_read"=TRUE; to force Oracle to use direct reads.<br /><br />PS: I have very much liked both your books, and I am looking forward to reading more on this blog!<br /><br />Cheers,<br />LucaLuca Canalihttps://www.blogger.com/profile/06252662329568134677noreply@blogger.comtag:blogger.com,1999:blog-4169710303065679169.post-52303558253998364772010-02-16T12:10:27.976-08:002010-02-16T12:10:27.976-08:00Taral, I have not looked into altering the number ...Taral, I have not looked into altering the number of blocks read when a direct path read temp event occurs... sorry.<br /><br />Randolf, I agree... I was too specific (I try to be very careful about statements like that). While the three things I mentioned are important, I also believe there are others. Thanks for commenting!Craig Shallahamer, President/Founder, OraPubhttps://www.blogger.com/profile/04109635337570098781noreply@blogger.comtag:blogger.com,1999:blog-4169710303065679169.post-65991790443803377342010-02-08T02:13:27.631-08:002010-02-08T02:13:27.631-08:00Craig,
regarding your description (quote from abo...Craig,<br /><br />regarding your description (quote from above):<br /><br />"There are supposed to be three factors involved in determining whether a scattered or direct read should result; size of the table, event 10949, and the resurrected instance parameter _small_table_threshold. It is supposed to work like this: if the size of the table is larger then _small_table_threshold (default 996 for my 11gR2 system) AND event 10949 is set to 0 (default), then a direct read results instead of a scattered read."<br /><br />I don't think this description is entirely correct respectively there are more factors to consider. You can find for instance a good starting point at <a href="http://afatkulin.blogspot.com/2009/01/11g-adaptive-direct-path-reads-what-is.html" rel="nofollow">Alex Fatkulin's blog</a>.<br /><br />It doesn't answer your question why changing the "_small_table_threshold" didn't activate the direct read, but may be the parameter is simply ignored for that purpose.<br /><br />RandolfRandolfhttps://www.blogger.com/profile/13463198440639982695noreply@blogger.comtag:blogger.com,1999:blog-4169710303065679169.post-30792409268601396112010-02-05T09:46:16.780-08:002010-02-05T09:46:16.780-08:00Hello Sir,
Very good Explanation. But i have one ...Hello Sir,<br /><br />Very good Explanation. But i have one question<br /><br />We are using 10.2.0.3 on sparc solaris 10 64bit OS.<br /><br />I was wondering about two events in my environment which is taking some long time. Yes, we are doing huge amount of grouping, sorting and hash joins which spill data to temp.<br /><br />Mostly, all time goes to "direct path read temp". I took trace and saw that it's just reading 1 block every time(p3) vs other reads like scattered or direct path write it read/write 32 blocks.<br /><br />So, is there any reason why direct path read temp would read only one block at time ? Can we set something that can make it read more blocks in one fetchTaralhttps://www.blogger.com/profile/00532059230992312309noreply@blogger.com