RE: db_file_multiblock_read_count parameter

Hi All,

My experiments support what Dave has said. It does help to use a larger multiblock read size than maxphys. However, there is a risk of tricking the optimizer into thinking that full table scans are very cheap, so some care is needed. For more information, have a look at the web tip on this matter at my companies web site.

Regards,
Steve Adams

http://www.ixora.com.au/
http://www.oreilly.com/catalog/orinternals/
http://www.christianity.net.au/


-----Original Message-----
From:	David Miller [SMTP:djm_at_oregon.West.Sun.COM]
Sent:	Friday, July 14, 2000 2:45 AM
To:	Multiple recipients of list ORACLE-L
Subject:	RE: db_file_multiblock_read_count parameter

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