Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Solaris - db_file_multiblock_read_count

Re: Solaris - db_file_multiblock_read_count

From: David Miller <dm32840_at_oregon.West.Sun.COM>
Date: Mon, 19 Nov 2001 15:11:31 -0800
Message-ID: <>

Hi Jeff,

I suspect that the size of the reads is based on the extent size within the table you're reading. I don't believe Oracle will issue a read across extents even if multiblock_read_count is high enough.

The maximum limit for a read within Oracle has varied a lot with releases on Solaris, but in your case it appears that it will allow 1 MB as the maximum read (128 * 8K). That has been the largest value allowed to date in any release on Solaris.

In testing we've done, the larger read sizes are more efficient because the application can issue a single read. You proposed altering multiblock_read_count to 24. I suspect (based on the above) that you then would get a read of 24 blocks followed by a read of 1 block (if my extent assumption is correct). That would require twice as many reads issued by the application and your performance would suffer somewhat.

As Jared mentioned if you want to set it lower than 128, then set it to 25. But leaving it higher should have no resource implications. It might affect how the optimizer handles some queries (by biasing more towards full table scans) but in your example you're forcing a FTS anyway, so it wouldn't make any difference.

Dave Miller
Sun Microsystems, Inc.

>Date: Mon, 19 Nov 2001 13:20:18 -0800
>To: Multiple recipients of list ORACLE-L <>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "Jeff Wiegard" <>
>From: "Jeff Wiegard" <>
>Subject: Solaris - db_file_multiblock_read_count
>X-ListServer: v1.0g, build 70; ListGuru (c) 1996-2001 Bruce A. Bergman
>Mime-Version: 1.0
>Content-Transfer-Encoding: 7bit
>I was hoping for some confirmation here.
>I'm running 8.0.5 on Solaris 2.7, with block size set to 8192.
>There is no 'maxphys' parameter in /etc/system.
>After some testing, I've decided to set the
>db_file_multiblock_read_count = 24, based on the following:
>1. SQL> alter session set db_file_multiblock_read_count = 1000;
>2. select value from v$parameter where name =
> This gave me a value = 128
>3. alter session set events '10046 trace name context forever,
>level 8';
>4. select /*+ FULL(t) */ count(*) from sys.source$ t;
>The trace file gave me the following:
>WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=660 p3=25
>WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1167 p3=25
>WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1207 p3=25
>WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1272 p3=25
>WAIT #1: nam='db file scattered read' ela= 0 p1=1 p2=1312 p3=25
>So then setting db_file_multiblock_read_count to 24 would be about
>right ... right?
>Please see the official ORACLE-L FAQ:
>Author: Jeff Wiegard

Please see the official ORACLE-L FAQ:
Author: David Miller
  INET: dm32840_at_oregon.West.Sun.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Mon Nov 19 2001 - 17:11:31 CST

Original text of this message