RE: db_file_multiblock_read_count parameter

Hi Linda,

The max that Oracle will currently support for a single I/O is 1M, at least on Solaris. Some older versions of Oracle won't support more than 128K in a single I/O.

So the db_multiblock count can be as big as 1M / 8K or 128. Setting it to 128 will let Oracle use 1M I/O's.

As Steve Adams mentioned, this can affect query plans by making full table scans seem less resource-intensive and so more likely to be chosen by the COST optimizer. You'll need to test this in your own environment to be sure problems don't occur, especially in other queries that might be running on the system.

Dave Miller
Sun Microsystems, Inc.

>Date: Thu, 13 Jul 2000 12:17:52 -0800
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>X-Comment: Oracle RDBMS Community Forum
>X-Sender: "Linda Wang" <lwang_at_messagemedia.com>
>From: "Linda Wang" <lwang_at_messagemedia.com>
>Subject: RE: db_file_multiblock_read_count parameter
>X-ListServer: v1.0f, build 69; ListGuru (c) 1996-2000 Bruce A. Bergman
>Mime-Version: 1.0
>Content-Transfer-Encoding: 7bit
>
>Dave,
>My dba_block_size = 8K, My maxphys = 8388608,
>What should my db_file_multiblock_read_count be?
>
>Thanks.
>Linda
>
>
>-----Original Message-----
>Miller
>Sent: Thursday, July 13, 2000 10:45 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Hi Alex,
>
>That's not true at all. Even before Solaris 7 it makes sense to use large
>I/O's if you have a reasonably large disk farm.
>
>In full table scan testing we've done internally, we can get more than twice
>as much performance with exactly the same database using an appropriate
>multi-block read count to create 1M I/O's instead of 64K. This will use
>less
>total CPU, especially less system time.
>
>The reason for this is that the number of necessary system calls is
>decreased. Even if the physical I/O stays at 64K or 128K, which is often
>the
>case if you use a volume manager like VxVM, since the application just has
>to
>issue a single read across the user/kernel interface, the efficiency is much
>higher. This single system call can then be handled by the kernel to issue
>the necessary I/O's in batches that do not require any further context
>switches.
>
>Also, the max I/O size in Solaris from at least 2.6 on is controlled by the
>parameter in /etc/system called "maxphys". I believe the default in 2.6
>is 126976 which is nearly 128K. Increasing it can be useful in some
>circumstances, especially when you have connected hardware RAID devices that
>have a logical unit made of several physical disks look like a single disk
>to the operating system. I've personally tested values as high as 4M, but
>individual results may vary.
>
>As always, YMMV. Since the db_file_multiblock_read_count parameter is
>dynamic,
>I'd recommend testing various values to see what's best for your database.
>
>Dave Miller
>Sun Microsystems, Inc.
>
>
>>Date: Wed, 12 Jul 2000 17:19:29 -0800
>>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>>X-Comment: Oracle RDBMS Community Forum
>>X-Sender: Alex Hillman <alex_hillman_at_physia.com>
>>From: Alex Hillman <alex_hillman_at_physia.com>
>>Subject: RE: db_file_multiblock_read_count parameter
>>X-ListServer: v1.0f, build 69; ListGuru (c) 1996-2000 Bruce A. Bergman
>>Mime-Version: 1.0
>>
>>There is no sense to make this parameter more then max IO size for OS. For
>>solaris before 2.7 it is 64K, after that it is configurable in /etc/system.
>>Do not remember what is maximum, but not less then 128K.
>>
>>Alex Hillman
>>
>>-----Original Message-----
>>Sent: Wednesday, July 12, 2000 7:36 PM
>>To: Multiple recipients of list ORACLE-L
>>
>>
>>Prasada,
>>
>>It is a dynamic parameter that can be modified using an "alter
>>system" or an "alter session" as the case may be.
>>
>>Cheers,
>>
>>Gaja.
>>
>>--- Prasada R Gunda <Prasada.Gunda1_at_hartfordlife.com> wrote:
>>>
>>>
>>> Hi All,
>>>
>>> We are using ETL(extraction,transformation and loading) tool
>>> to load data from
>>> source databases to warehouse database.
>>> This tool allows us to write custom sql and does not allow DDL
>>> statements.
>>>
>>> I am using full table scan on some of the tables in some sql
>>> quries. I would
>>> like to increase the value of db_file_multiblock_read_count
>>> when I do FTS.
>>>
>>> Is there any way I could increase the
>>> db_file_multiblock_read_count value other
>>> than changing it in init.ora file.
>>>
>>> Thanks in advance for your help.
>>>
>>> Regards,
>>> Prasad
>>>
>>>
>>> --
>>> Author: Prasada R Gunda
>>> INET: Prasada.Gunda1_at_hartfordlife.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).
>>
>>
>>=====
>>Gaja Krishna Vaidyanatha
>>Director, I-O Management Products
>>Quest Software Inc.
>>(972)-304-1170
>>gajav_at_yahoo.com
>>
>>"Opinions and views expressed are my own and not of Quest"
>>
>>__________________________________________________
>>Do You Yahoo!?
>>Get Yahoo! Mail - Free email you can access from anywhere!
>>http://mail.yahoo.com/
>>--
>>Author: Gaja Krishna Vaidyanatha
>> INET: gajav_at_yahoo.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).
>
>--
>Author: David Miller
> INET: djm_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).
>
>--
>Author: Linda Wang
> INET: lwang_at_messagemedia.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
Received on Fri Jul 14 2000 - 11:39:31 CDT