Correction - RE: RE: db file sequential read - again
- From: daniel.fink@xxxxxxxxxxxxxx
- To: jherrick@xxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Tue, 13 Mar 2007 14:30:07 +0000
Sorry, not enough coffee this morning and I'm still not adjusted to the new DST!
The 3rd section (table full scans) should read
Tables are often read with multi block reads, but not always.
------- Original Message -------
On 3/13/2007 2:11 PM wrote:
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Correction - RE: RE: db file sequential read - again
