RE: "direct path read" and "db file sequential read" used for full table scans in 11g
From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Fri, 31 Aug 2012 02:00:32 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A82584B_at_onews32>
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.
T_TRIWORKTASK SQL> select segment_name from dba_extents where file_id = 5 and 421087 between block_id and block_id+blocks-1;
T_TRIWORKTASK SQL> select segment_name from dba_extents where file_id = 8 and 521715 between block_id and block_id+blocks-1;
T_TRIWORKTASK SQL> select segment_name from dba_extents where file_id = 8 and 182016 between block_id and block_id+blocks-1;
T_TRIWORKTASK From: Alex Fatkulin [mailto:afatkulin_at_gmail.com]
Date: Fri, 31 Aug 2012 02:00:32 +0000
Message-ID: <A250F0C68C23514CA9F3DF63D60EE10E1A82584B_at_onews32>
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 VALUEWAIT #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
------------------------------------ ----------- -----
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
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_at_gmail.com]
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.
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Aug 30 2012 - 21:00:32 CDT