RE: "direct path read" and "db file sequential read" used for full table scans in 11g
Date: Thu, 30 Aug 2012 22:51:53 -0400
Message-ID: <00d001cd8723$94e05f70$bea11e50$_at_rsiz.com>
How many columns in your table?
If less than 255, do you have very many chained and/or migrated rows?
If it is the case that you read adaptively direct a block and you need to fetch a row from the rowid relocation of a migrated row guess what happens. If you need a column from a chained row piece, guess what happens.
This may or may not be the case you are seeing. In your raw trace file you will see a direct read followed by individual reads to fetch the pieces for each row that needs pieces to be fetched. This will not be obvious from aggregated trace files.
As for documentation, I *thought* I remembered reading about adr in the new features section of the release notes. However I do not believe the multiple row piece implications were documented or even well understood by support prior to the resolution of a bug I worked on regarding the interaction of KEEP CACHE and adr. If there is documentation beyond my behavior notes from testing and extended trace files, I have not seen it.
I believe adr has implications for planning physical storage (including the ordering of columns) if the size and shape of a table's rows drives much piece fetching. It has not reached the top of my priorities for testing since fixing the adr/keep cache interaction solved the problem for my client.
If anyone can point us at documentation of what is supposed to happen with adr and multiple row pieces, please point us at it!
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Allen, Brandon
Sent: Thursday, August 30, 2012 4:46 PM
To: oracle-l_at_freelists.org
Subject: "direct path read" and "db file sequential read" used for full
table scans in 11g
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.
- 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.
- Why is it using "db file sequential read" for a full table scan instead of "db file scattered read"?
- 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
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 30 2012 - 21:51:53 CDT