Re: sequential read on full-table scan?
- From: Martic Zoran <zoran_martic@xxxxxxxxx>
- To: paul.baumgartel@xxxxxxxxxxxx
- Date: Fri, 20 May 2005 22:23:50 -0700 (PDT)
Paul,
So, you proved the fact about why MBR can be very slow
and ugly, making much more I/O then needed. Your 16
block MBR become many small MBR (scattered Oracle read
or sequential disk I/O) + a few cache reads + a few
single block reads (sequential Oracle reads od single
block I/O reads).
You reads like this:
block 9813, 9814 and 9815 - scattered read (MBR) with
MBRC=3
block 9816 - cache read
block 9817 and 9818 - scattred Oracle read with MBRC=2
block 9819 - cache read
block 9820 - sequential Oracle read
block 9821 - cache read
block 9822 - sequential Oracle read
In this case you got 2 MBR + 2 SBR + 3 cache reads
instead of 1 MBR with MBRC=10 (or 16).
I am confident that this is probably much more costly
then doing one MBR.
It is the interesting fact how Oracle data cache may
ruin performances sometimes.
We are not aware of this fact many times.
I believe that this is showing how important is to
collect system statistics to reveal to Oracle
optimizer the real speed of MBR.
The only problem is that Oracle is not predicting in
his cost how many MBR/SBR operation are going to
happen instead of 1 MBR? Is this true?
Regards,
Zoran
--- Paul Baumgartel <paul.baumgartel@xxxxxxxxx> wrote:
> Zoran,
>
> I took your advice and found some interesting
> information. Here's a
> typical snippet of the trace file:
>
> WAIT #64: nam='db file scattered read' ela= 1150
> p1=17 p2=9813 p3=3
> WAIT #64: nam='db file scattered read' ela= 959
> p1=17 p2=9817 p3=2
> WAIT #64: nam='db file sequential read' ela= 2040
> p1=17 p2=9820 p3=1
> WAIT #64: nam='db file sequential read' ela= 547
> p1=17 p2=9822 p3=1
>
>
> Note that there ARE scattered reads going on as well
> as sequential,
> but the scattered reads are grabbing only 2 or 3
> blocks at a time. DB
> file multiblock read count is set to 16.
>
> Paul
__________________________________
Do you Yahoo!?
Yahoo! Mail - Find what you need with new enhanced search.
http://info.mail.yahoo.com/mail_250
--
//www.freelists.org/webpage/oracle-l
Other related posts:
