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
Other related posts:
- » "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Sayan Malakshinov
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Sayan Malakshinov
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Tanel Poder
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Alex Fatkulin
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Alex Fatkulin
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Mark W. Farnham
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Kellyn Pot'vin
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Gaja Krishna Vaidyanatha
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Jonathan Lewis
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- bill thater
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Mark W. Farnham
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Gaja Krishna Vaidyanatha
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Jonathan Lewis
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Gaja Krishna Vaidyanatha
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g - Allen, Brandon
- » Re: "direct path read" and "db file sequential read" used for full table scans in 11g- Tanel Poder
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon
- » RE: "direct path read" and "db file sequential read" used for full table scans in 11g- Allen, Brandon