Re: Solaris - db_file_multiblock_read

From: Ron Thomas <rthomas_at_hypercom.com>
Date: Mon, 19 Nov 2001 16:45:51 -0800
Message-ID: <F001.003C8F87.20011119163018@fatcity.com>

It would appear that extent size does play a role in this.

OS: HPUX 11.0
Oracle: 8.1.7 32-bit
DB Block size 8k

Setting multiblock read count to 1000 (v$parameter shows 128), then selecting against a table in an
LMT: Case 1:
Extent size: 128k
max p3: 16

Case 2:
Extent size 4M
max p3: 128

This would make since if oracle does not issue a read across extents.

Ron Thomas
Hypercom, Inc
rthomas_at_hypercom.com

                                                                                       
                                 
                    dm32840_at_oregon.We                                                  
                                 
                    st.Sun.COM               To:     ORACLE-L_at_fatcity.com              
                                 
                    Sent by:                 cc:                                       
                                 
                    root_at_fatcity.com         Subject:     Re: Solaris - 
db_file_multiblock_read_count                   
                                                                                       
                                 
                                                                                       
                                 
                    11/19/01 03:40 PM                                                  
                                 
                    Please respond to                                                  
                                 
                    ORACLE-L

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 <ORACLE-L_at_fatcity.com>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "Jeff Wiegard" <JWIEGARD_at_ngwmail.des.state.mn.us>
>From: "Jeff Wiegard" <JWIEGARD_at_ngwmail.des.state.mn.us>
>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
>
>Hi.
>
>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 =
>'db_file_multiblock_read_count';
>
> 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?
>
>Thanks,
>
>Jeff
>
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Jeff Wiegard
> INET: JWIEGARD_at_ngwmail.des.state.mn.us

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
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: ListGuru_at_fatcity.com (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).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Thomas
  INET: rthomas_at_hypercom.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: ListGuru_at_fatcity.com (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 - 18:45:51 CST

Re: Solaris - db_file_multiblock_read_count