"direct path read" and "db file sequential read" used for full table scans in 11g

From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 30 Aug 2012 20:45:41 +0000

Hi list,
I'm still coming up to speed with 11g and I just came across the new, seemingly 
undocumented, feature of "adaptive direct path reads*" for full table scans.  I 
stumbled upon it because I've got a query that is running much faster in a test 
environment than in production and I found that it seems to be due to the test 
environment having a smaller buffer cache which, if I understand correctly, 
makes Oracle more likely to bypass the buffer cache and do direct path reads.  
So on to my questions:

This is on 11.2.0.1, running on Windows Server 2003.



1)      How can I force production to use the direct path reads?  I've already 
tried "alter session set "_serial_direct_read"=true;", and I added a comment to 
the query to force a hard parse into a new cursor, but it still didn't work - 
the query execution still used "db file sequential read" instead of "direct 
path read" (I ran a 10046 level 12 trace to verify).  I also tried setting 
_serial_direct_read=always because I saw that suggested somewhere, but Oracle 
rejected that as an invalid value for the parameter so maybe that only works in 
older or later versions than the one I'm on.

2)      Why is it using "db file sequential read" for a full table scan instead 
of "db file scattered read"?

3)      I also tried setting PARALLEL to 2 on the table in question and it did 
force Oracle to use a parallel execution plan, but it still used "db file 
sequential read" instead of "direct path read" - any idea why this could be?

Thanks in advance for any ideas,
Brandon

*I haven't been able to find an official term for it in the Oracle docs or MOS, 
but that seems to be the popular term used for it in blogs.  If you're not 
familiar with this new feature, see MOS 1457693.1 and 793845.1, or just do a 
web search for "oracle 11g direct path read".

Brandon Allen
Database Administrator
OneNeck IT Services
480.315.3048


________________________________

Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

--
//www.freelists.org/webpage/oracle-l

Follow-Ups:
- RE: "direct path read" and "db file sequential read" used for full table scans in 11g
  - From: Mark W. Farnham

"direct path read" and "db file sequential read" used for full table scans in 11g

Other related posts: