RE: "direct path read" and "db file sequential read" used for full table scans in 11g
Date: Thu, 13 Sep 2012 20:43:43 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A837F65_at_onews32>
Hi List,
I wanted to thank you all for your help with this and also give a summary of what I found and a follow up on how the problem was resolved.
As Alex pointed out, my full table scan was actually using direct path reads, but I was just thrown off by the fact that it was doing so many sequential reads that I thought the direct path read wasn't happening. I initially thought the few direct path reads I was seeing were for sorting in TEMP, but I now see that those are categorized separately as "direct path read temp" events.
Mark was right about the chained rows - there are 270 columns in this table and many chained rows. I believe this was contributing to the fact that I was seeing so many 'db file sequential reads', along with possibly what Gaja stated about the fact that sequential reads often occur along with the usual scattered reads even during a regular full table scan (not using direct path reads). Tanel was also right - I do indeed see very high values for "table fetch continued row" when scanning this table.
So, after I got all that figured out with your help, I realized that the performance problem we were seeing was due to these direct path reads preventing the table from being fully cached. This table is large (1GB) relative to the buffer cache (~3GB), but it is very frequently used, and unfortunately must frequently be accessed via FTS due to the queries required by the application, so with such a critical table being kept out of the cache due to direct path reads, performance was suffering severely.
The solution I've implemented is implementation of a KEEP pool for this table. In doing so, I believe I also encountered a bug (8897574), probably the same one Mark mentioned, where Oracle doesn't load large objects into the KEEP pool as it should when direct path reads are being done. I'm currently running on 11.2.0.1 on Windows and there is no patch for this bug on Windows so the solution is to upgrade to 11.2.0.2+, which we plan to do in the near future, but for a quick workaround, I found that I was still able to get the table to load in the KEEP pool as long as I accessed it via regular reads instead of direct path, so if I force a full scan by way of a full index range scan (using an INDEX hint), then the table gets loaded into the KEEP pool as it should, and it is read from the buffer cache instead of through direct path reads and our performance has been consistently great.
Thanks again!
Brandon
From: tanel_at_poderc.com [mailto:tanel_at_poderc.com] On Behalf Of Tanel Poder Sent: Friday, August 31, 2012 11:17 AM
Ok I didn't read the original post properly, I thought that the single block reads were just because of some other access path (index range scan) in the exec plan. But you only had the full table scan ...
Whenever something puzzles me like that, then the next step is to look into V$SESSTAT for extra hints - like the "table fetch continued row" metric.
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-lReceived on Thu Sep 13 2012 - 15:43:43 CDT