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

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Fri, 31 Aug 2012 17:56:23 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A82617A_at_onews32>

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_at_rsiz.com] 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.
--

http://www.freelists.org/webpage/oracle-l Received on Fri Aug 31 2012 - 12:56:23 CDT