Re: "direct path read" and "db file sequential read" used for full table scans in 11g
Date: Fri, 31 Aug 2012 09:35:38 +0100
Message-ID: <4BBE56F6C6A4476BA26E354F332157E9_at_Primary>
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Gaja Krishna Vaidyanatha" <gajav_at_yahoo.com> To: "Oracle-L List" <oracle-l_at_freelists.org> Sent: Friday, August 31, 2012 8:11 AM Subject: Re: "direct path read" and "db file sequential read" used for full table scans in 11g
| Regardless of the new feature in 11g, I have observed "db file sequential
read" events on even simple full table scans like - select /*+ FULL(t) */
count(*) from xxx t. This occurs when Oracle is unable to read DBFMBRC
worth of blocks from a given extent. This usually happens towards the edge
of the high-water-mark of the table, but in theory can happen in any extent
of the table.
Are you sure about that - the size of a direct path read has nothing to do with the db_file_multiblock_read_count value, so I'd be surprised if Oracle made any decision to switch because of that. Is it possible that it's more a case of something funny happening between the low high water mark and the high high water mark - and even that's unlikely since every formatted patch between the two is typical a reasonable number of consecutive block.
|
| For example, assume a given table has 8 extents (where each full extent
is 16 blocks of real data) and also assume that the total number of blocks
below the high-water-mark is 124. In this scenario, the trace file will
show 7 calls of "db file scattered read" (direct path read in your case)
and this accounts for 112 blocks. This will be followed by 12 calls of "db
file sequential read" to account for the remaining blocks below the
high-water-mark. So bottom line, even in full-table-scans or index
fast-full scans, there could be occurrences of "db file sequential read"
for Oracle to perform single block reads.
|
Apart from Mark's comment about chained rows - which is something that messes with Exadata smart scans as well - there are reads to the undo segments because delayed block cleanout after a direct path read is constantly repeated and never written back; and then they MAY BE (I'd have to check) reads of bitmap space management blocks. A simple check of the latter is to (a) check the number of extents and (b) dump a few blocks that were read from the correct file by db file sequential read.
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com/all_postings
Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543
- Original Message ----- From: "Gaja Krishna Vaidyanatha" <gajav_at_yahoo.com> To: "Oracle-L List" <oracle-l_at_freelists.org> Sent: Friday, August 31, 2012 8:11 AM Subject: Re: "direct path read" and "db file sequential read" used for full table scans in 11g
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 31 2012 - 03:35:38 CDT