I/O and db_file_multiblock_read_count
- From: "Kevin Lidh" <kevin.lidh@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 8 Dec 2006 13:46:00 -0700
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:
