I/O and db_file_multiblock_read_count

From: Kevin Lidh <kevin.lidh_at_gmail.com>
Date: Fri, 8 Dec 2006 13:46:00 -0700
Message-ID: <cb4807f0612081246l15eacf6bh89cc786ab3e8c1e4@mail.gmail.com>

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?

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

Received on Fri Dec 08 2006 - 14:46:00 CST