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

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Thu, 30 Aug 2012 20:45:41 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A824F21_at_onews32>

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.

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

Received on Thu Aug 30 2012 - 15:45:41 CDT