RE: db file sequential read - again

db file sequential reads are single block read calls. db file scattered reads are multi block read calls.

Indexes are often/usually read with single block read calls, but not always. For example, an index fast full scan will read an index using multi block read calls.

Table full scans are multi block read calls, but not always.

Here is an example from a trace file. Comments added for clarity.

Actual Execution Plan of query

STAT #9 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT GROUP BY NOSORT (cr=8257 pr=17835 pw=0 time=7987272 us)'
STAT #9 id=2 cnt=10921 pid=1 pos=1 obj=59390 op='TABLE ACCESS BY INDEX ROWID ORDER_MASTER (cr=8257 pr=17835 pw=0 time=9409934 us)'
STAT #9 id=3 cnt=10921 pid=2 pos=1 obj=59394 op='INDEX RANGE SCAN FK_ORDMAST_CUSTID (cr=26 pr=34 pw=0 time=207179 us)'

 OBJECT_ID OBJECT_NAME
---------- ------------------------------

     59390 ORDER_MASTER
     59394 FK_ORDMAST_CUSTID

• Read 1 block of FK_ORDMAST_CUSTID (index range scan) WAIT #9: nam='db file sequential read' ela= 70890 file#=9 block#=29471 blocks=1 obj#=59394 tim=3385019627
• Read 1 block of FK_ORDMAST_CUSTID (index range scan) WAIT #9: nam='db file sequential read' ela= 56770 file#=9 block#=36342 blocks=1 obj#=59394 tim=3385077259
• Read 32 blocks of FK_ORDMAST_CUSTID (index range scan) WAIT #9: nam='db file scattered read' ela= 45127 file#=9 block#=36079 blocks=32 obj#=59394 tim=3385122535
• Read 1 block of ORDER_MASTER (table access by index) WAIT #9: nam='db file sequential read' ela= 40980 file#=8 block#=24356 blocks=1 obj#=59390 tim=3385163832
• Read 4 blocks of ORDER_MASTER (table access by index) WAIT #9: nam='db file scattered read' ela= 36591 file#=8 block#=24357 blocks=4 obj#=59390 tim=3385200647
• Read 32 blocks of ORDER_MASTER (table access by index) WAIT #9: nam='db file scattered read' ela= 60813 file#=8 block#=24363 blocks=32 obj#=59390 tim=3385261702

Regards,
Daniel Fink

• Original Message ------- On 3/13/2007 1:35 PM wrote:

I know this has been discussed here before....I have a small C++ program in a timer loop that spins around looking for the db file scattered read as well as the db file sequential read event and logs the segments to a table on the side. I've noticed that some of the 'sequential' read events have their P1 and P2 parameters referring to segments marked 'TABLE' in DBA_EXTENTS. I had always
thought the 'sequential' event referred to index blocks

• Can somebody re-explain this please?

Also ... what do db file sequential reads of block #0 of a datafile represent? This is 9.2.07 on AIX 5.3 (Asynch I/O enabled), all tablespaces are LMT. I'm assuming these single block reads of block 0 are for space management and therefore unavoidable?

Bonus question....this is a Siebel V7.5.3 db running in RULE mode with Indexes up the wahzoo. I'm seeing a lot of db file sequential read waits because RBO is
using any index it finds (which we all know could be a bad thing!). I'm thinking of putting hot indexes into their own buffer pool since I've got tons of memory. Does this sound like a reasonable approach to you folks?

Thanks in Advance!

Jeff H

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Mar 13 2007 - 09:11:02 CDT