Re: sequential read on full-table scan?

From: Martic Zoran <zoran_martic_at_yahoo.com>
Date: Fri, 20 May 2005 22:23:50 -0700 (PDT)
Message-ID: <20050521052350.35989.qmail@web52603.mail.yahoo.com>

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_at_gmail.com> 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

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

Received on Sat May 21 2005 - 01:28:30 CDT