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

From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: Alex Fatkulin <afatkulin@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Fri, 31 Aug 2012 02:00:32 +0000

Thanks Alex.  Yes, there were some direct path reads and I was assuming 
(incorrectly) that those were just from the sort operation.  I checked a few 
events from my raw trace and verified that both the direct reads and buffered 
reads are indeed reading from the table being scanned and it appears to be 
switching back and forth between the two read events as you can see below.  Is 
that normal for it to go back and forth between direct and buffered reads, or 
is it supposed to do all direct reads once it chooses that path?
My dbfmrc is 16 and arraysize is just the default 15.

SQL> show parameter multiblock

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
db_file_multiblock_read_count        integer     16


WAIT #7: nam='direct path read' ela= 17 file number=8 first dba1888 block cnt 
obj#'9095 tim26813501002
WAIT #7: nam='db file sequential read' ela= 4914 file#=5 block#B1087 blocks=1 
obj#'9095 tim26813506497
WAIT #7: nam='db file sequential read' ela= 6650 file#=8 block#R1715 blocks=1 
obj#'9095 tim26813513187
WAIT #7: nam='direct path read' ela= 831 file number=8 first dba2016 block 
cnt obj#'9095 tim26813517581

SQL> select segment_name from dba_extents where file_id = 8 and 181888 between 
block_id and block_id+blocks-1;

SEGMENT_NAME
---------------------------------------------------------------------------------
T_TRIWORKTASK

SQL> select segment_name from dba_extents where file_id = 5 and 421087 between 
block_id and block_id+blocks-1;

SEGMENT_NAME
---------------------------------------------------------------------------------
T_TRIWORKTASK

SQL> select segment_name from dba_extents where file_id = 8 and 521715 between 
block_id and block_id+blocks-1;

SEGMENT_NAME
---------------------------------------------------------------------------------
T_TRIWORKTASK

SQL> select segment_name from dba_extents where file_id = 8 and 182016 between 
block_id and block_id+blocks-1;

SEGMENT_NAME
---------------------------------------------------------------------------------
T_TRIWORKTASK



From: Alex Fatkulin [mailto:afatkulin@xxxxxxxxx]

From the events it looks like direct path read did happen


________________________________

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

Follow-Ups:
- Re: "direct path read" and "db file sequential read" used for full table scans in 11g
  - From: Alex Fatkulin

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: Sayan Malakshinov
- 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: Alex Fatkulin

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

Other related posts: