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

From: Tanel Poder <tanel_at_tanelpoder.com>
Date: Fri, 31 Aug 2012 21:17:23 +0300
Message-ID: <CAMHX9JK3yYHk18+n4zDx41A_SBYwSRsrwTyjAb8G3jneOdgKFw_at_mail.gmail.com>

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.

For example this:

http://blog.tanelpoder.com/2009/11/04/detect-chained-and-migrated-rows-in-oracle/

-- 
Tanel Poder
Blog - http://blog.tanelpoder.com
App  - http://voic.ee


On Fri, Aug 31, 2012 at 8:56 PM, Allen, Brandon
<Brandon.Allen_at_oneneck.com>wrote:



> Thanks Mark, it looks like that could explain what I'm seeing.  The table

> has 270 columns and lots of chained rows as you can see below

> (db_block_size is 8k).  I cleaned up some of the chained rows with an

> online shrink, but I still have a lot more to clean up and I may need to

> consider creating a separate 16k tablespace and buffer cache for this table

> but I'd like to avoid that if possible.

>

> QL> select count(*) from dba_tab_columns where table_name =

> 'T_TRIWORKTASK';

>

>   COUNT(*)

> ----------

>        270

>

> SQL> select count(*) from tridata.t_triworktask;

>

>   COUNT(*)

> ----------

>     602695

>

>



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

Received on Fri Aug 31 2012 - 13:17:23 CDT