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

From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
To: "Oracle-L List" <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 31 Aug 2012 09:35:38 +0100

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@xxxxxxxxx>
To: "Oracle-L List" <oracle-l@xxxxxxxxxxxxx>
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@xxxxxxxxx>
To: "Oracle-L List" <oracle-l@xxxxxxxxxxxxx>
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

--
//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: Gaja Krishna Vaidyanatha

References:
- RE: "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: Gaja Krishna Vaidyanatha

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

Other related posts: