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

From: Gaja Krishna Vaidyanatha <gajav@xxxxxxxxx>
To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 31 Aug 2012 09:13:47 -0700 (PDT)

Hi Jonathan, Mark et al.,
My writeup was for a "simple full table scan" (like the example I gave), just 
to point out that "db file sequential reads" does occur for full scans. And 
yes, I am sure about that. This is true even with nothing else happening on the 
database (like delayed block cleanout). I was just wondering whether a similar 
phenomenon was being experienced with ADR.

Cheers,

Gaja
 
Gaja Krishna Vaidyanatha,
CEO & Founder, DBPerfMan LLC
http://www.dbperfman.com
http://www.dbcloudman.com

Phone - +1-650-743-6060
LinkedIn - http://www.linkedin.com/in/gajakrishnavaidyanatha

Co-author: Oracle Insights:Tales of the Oak Table - 
http://www.apress.com/9781590593875
Primary Author: Oracle Performance Tuning 101 - http://www.amzn.com/0072131454
Enabling Cloud Deployment & Management for Oracle & Big Data


________________________________
 From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
To: Oracle-L List <oracle-l@xxxxxxxxxxxxx> 
Sent: Friday, August 31, 2012 1:35 AM
Subject: Re: "direct path read" and "db file sequential read" used for full 
table scans in 11g
 
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

--
//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: Jonathan Lewis

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
  - From: Jonathan Lewis

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

Other related posts: