Re: I/O and db_file_multiblock_read_count

  • From: "Stefan Knecht" <knecht.stefan@xxxxxxxxx>
  • To: kevin.lidh@xxxxxxxxx
  • Date: Sat, 9 Dec 2006 09:03:45 +0100

As Mladen already noted, check if you ran dbms_stats.gather_system_stats. Ff
you have system stats in place, Oracle won't use dbfmrc.

Stefan

On 12/8/06, Kevin Lidh <kevin.lidh@xxxxxxxxx> wrote:


I was reading an article about the appropriate setting for
db_file_multiblock_read_count.  I'm on a HP-UX 11.11 64-bit system with
Oracle 9.2.0.7.0.  The original value was 16 and I bounced the database
and ran a million record full-scan test (10046 trace) and then set the value
to 128 (max value) and re-ran the test.  Although I did have less calls to
'db file scattered read' (140 compared to 1008), the time to get the same
number of blocks was longer.  A random example from the trace files looks
like this:

WAIT #1: nam='db file scattered read' ela= 21614 p1=6 p2=3979 p3=126  -->
126 blocks = 21614
WAIT #1: nam='db file scattered read' ela= 10724 p1=6 p2=4107 p3=126

WAIT #1: nam='db file scattered read' ela= 577 p1=6 p2=3979 p3=16
WAIT #1: nam='db file scattered read' ela= 1524 p1=6 p2=3995 p3=16
WAIT #1: nam='db file scattered read' ela= 916 p1=6 p2=4011 p3=16
WAIT #1: nam='db file scattered read' ela= 1022 p1=6 p2=4027 p3=16
WAIT #1: nam='db file scattered read' ela= 1095 p1=6 p2=4043 p3=16
WAIT #1: nam='db file scattered read' ela= 1026 p1=6 p2=4059 p3=16
WAIT #1: nam='db file scattered read' ela= 829 p1=6 p2=4075 p3=16
WAIT #1: nam='db file scattered read' ela= 826 p1=6 p2=4091 p3=14   -->
126 blocks = 7815
WAIT #1: nam='db file scattered read' ela= 437 p1=6 p2=4107 p3=16

And I ran the test again with 16 after my 128 test and the results were
similar to the first test.  The cumulative times for the 'db file scattered
read' was 1.3181s for the 16 and 2.5149s when it was 128.  We use a
Hitachi SAN and I know it has caching but I would still think that the
fewer, bigger requests would be faster.  Is there a measurement or setting
on the operating system or even SAN that I can look at to help determine the
optimal setting?

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 12-2006