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

From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: "Mark W. Farnham" <mwf@xxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 31 Aug 2012 17:56:23 +0000

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

SQL> analyze table tridata.t_triworktask list chained rows;

Table analyzed.

SQL> select count(*) from chained_rows;

  COUNT(*)
----------
     72668

SQL> alter table "TRIDATA"."T_TRIWORKTASK" shrink space;

Table altered.

SQL> analyze table tridata.t_triworktask list chained rows;

Table analyzed.

SQL> select count(*) from chained_rows;

  COUNT(*)
----------
     35475




-----Original Message-----
From: Mark W. Farnham [mailto:mwf@xxxxxxxx]
Sent: Thursday, August 30, 2012 7:52 PM


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.


________________________________

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

Follow-Ups:
- Re: "direct path read" and "db file sequential read" used for full table scans in 11g
  - From: Tanel Poder

References:
- "direct path read" and "db file sequential read" used for full table scans in 11g
  - From: Allen, Brandon
- RE: "direct path read" and "db file sequential read" used for full table scans in 11g
  - From: Mark W. Farnham

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

Other related posts: