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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 31 Aug 2012 11:37:39 -0400
Message-ID: <017a01cd878e$8f1e3600$ad5aa200$_at_rsiz.com>

Just for clarity: the 255/254 columns causing multiple row pieces may well be intra-block chaining. Whether that causes a re-fetch of the current block when the block is only in your pga from adr I either didn't test or cannot remember and was moot after the KEEP cache interaction with adr bug was fixed. (Thanks Tim Gorman for careful alertness to rumor control: if the additional row pieces will fit, Oracle will [of course, right?] use the current block. It would be easy to think the column count boundary automatically put the pieces of a row in different blocks from what I wrote.)

JL mentioned the also important re-block cleanout and consistency for adr and that is also important. From the raw trace of course you can tell which is happening to you from the row block address on the individual block reads. I *think* that is more important with multiple direct full table scans and you will likely see fewer single block reads per direct block batch than you do when you are reading multiple row pieces for every row. Your mileage will vary. In my case even forcing all the blocks into cache one at a time by reading all the distinct block addresses and then fetching them one at a time to avoid adr kicking in, subsequent full table scans *still* operated as 1 batch of blocks via adr (with a common maximum size but some variability which matches what JL wrote about boundaries and such) then *many* single block reads with row block addresses from the table (not undo), then another batch of blocks via adr and repeat. I wish I had taken note of whether any of the single block reads were the current block.

Whether the "finish up on a boundary" (my words) effects Gaja mentioned occur with direct reads I don't know. I think he was basing what he wrote on his tests with a "simple" full table scan with the word "simple" meaning it was of the non-direct variety.

If I inadvertently put any errors in the mouths of what I understood others to have written, the blame is on me.

Overall, adr is probably a big win. There are certainly some important corner cases. When important enough they might even justify some physical rebuilding or separation of entities into multiple physical tables for implementation. And remember: Physical deployment issues will change over time. Fortunately, if you keep the relational model clean and merely make the compromise at the physical deployment layer it will be easy to reassemble multiple pieces of an entity when practicality arrives.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mark W. Farnham
Sent: Thursday, August 30, 2012 10:52 PM To: Brandon.Allen_at_OneNeck.com; oracle-l_at_freelists.org Subject: RE: "direct path read" and "db file sequential read" used for full table scans in 11g

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 Fri Aug 31 2012 - 10:37:39 CDT

This message: [ Message body ]
Next message: Gaja Krishna Vaidyanatha: "Re: "direct path read" and "db file sequential read" used for full table scans in 11g"
Previous message: Rich Jesse: "Re: Huge CPU Wait spikes displayed in EM12c"
In reply to: Mark W. Farnham: "RE: "direct path read" and "db file sequential read" used for full table scans in 11g"
Next in thread: Allen, Brandon: "RE: "direct path read" and "db file sequential read" used for full table scans in 11g"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message