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

From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: Tanel Poder <tanel@xxxxxxxxxxxxxx>, "Mark W. Farnham" <mwf@xxxxxxxx>, "gajav@xxxxxxxxx" <gajav@xxxxxxxxx>, "Sayan Malakshinov (xt.and.r@xxxxxxxxx)" <xt.and.r@xxxxxxxxx>, "Alex Fatkulin (afatkulin@xxxxxxxxx)" <afatkulin@xxxxxxxxx>, "Paul Drake (bdbafh@xxxxxxxxx)" <bdbafh@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 13 Sep 2012 20:43:43 +0000

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@xxxxxxxxxx [mailto:tanel@xxxxxxxxxx] 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.

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

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

Other related posts: