Re[2]: db_file_multiblock_read

From: <dgoulet_at_vicr.com>
Date: Fri, 14 Jul 2000 13:55:37 -0400
Message-Id: <10558.112091@fatcity.com>

Denny & all,

First off I probably should explain that this was back on Oracle 6.0.36 with HP-UX 9.0. In this case Oracle made no attempt to cut back the DB_FILE_MULTIBLOCK_READ_COUNT which caused HP a pile of fun satisfying the request. Also we only had RULE based optimization so the execution paths were not the problem. I'm NOT totally sure as I never got a complete answer from one source, but this is what I made of all of the partial explanations I got (in a nutshell).

Oracle asked for 2KB * 800 or 1600KB of data HP would only supply 32KB max per time slice thereby taking 50 time slices to fulfill the request.
During the intermediate time slices Oracle just sat there waiting on IO.

The table being queried had over 27 million rows of data with an average row size of 128 bytes and we did expect a full table scan. I believe this was a case of learning a lesson by fire. It was also a case of doing something that an Un database educated SA (or more appropriately HACKER) suggested. Needless to say, this is a powerful tuning parameter to which Oracle has also added some intelligence. This thread has also been very educational although I'm not sure we (as a group) still understand the full implications of what is being said.

BTW: Steve's site has a very nice (PAT,PAT on Steve's back) URL on this entire process and why we should be interested.

As has been said before: "Sometimes you get the bear and sometimes he gets you". OUCH!!!!! Darn that hurt!!!

Dick Goulet

____________________Reply Separator____________________

Subject: Re: db_file_multiblock_read_count parameter Author: Denny Koovakattu <denny_vk_at_yahoo.com> Date: 7/14/00 8:12 AM

Hi Dick,

When you changed the DB_FILE_MULTIBLOCK_READ_COUNT did any of the execution plans for the queries change ? With such a drastic change ( 8 to 800 ) the optimizer could have chosen full table scans. I did some testing some time back and found that the maximum value recorded was 2M ( db_file_multiblock_read_count - 128 ) on HP-UX 11.0 running 8.0.5.1 (64 Bit) with 16K block size. ( On Oracle 7.3.4 the maximum value obtained was 256K. ) I am not sure whether HP silently enforced a lower value. ( Will have to check that out when I get time. ) If you go by the documentation, it looks like the default value for MAXPHYS is 1M and can be configured to be as high as 32M on V-Class systems.

/usr/conf/master.d/scsi-tune

@(#) scsi-tune $Date: 1998/06/22 11:48:22 $Revision: r11ros/3 PATCH_11.00 (PHKL_15705)
*
$TUNABLE
*
The following entries form the tunable parameter table. Any of these
values can be overwritten in the dfile. However, before changing any
value, know the ramifications of your change. This file is specific
for scsi tunables only.
*
km_disable KM_DISABLE 0
*range st_large_recs>=0
*range st_large_recs<=1
*range st_fail_overruns>=0
*range st_fail_overruns<=1
*range scsi_maxphys>=1048576
*range scsi_maxphys<=33554432
*
stape driver tunables
*
st_large_recs ST_LARGE_RECS 0 st_fail_overruns ST_FAIL_OVERRUNS 0
*
scsi subsystem tunables
*
scsi_maxphys SCSI_MAXPHYS 1048576
*
This value was previously fixed at 1MB (1048576). On V-Class systems,
this value can be set as high as 32MB (33554432) for large raw
SCSI disk transfers. Most systems won't need the value set this
high, so the recommended value should be set to the SCSI tape
maximum of 16M-1 (16777215) for V-Class. Non V-Class systems
should remain with the system default of 1048576. To enable
large record support for SCSI tape drives with the stape driver,
the new st_large_recs tunables must also be set to 1.
*
$$$

Regards,
Denny

dgoulet_at_vicr.com wrote:

> 
> Steve,
> 
>     Some time ago I changed a DB from 8 to 800 on the
> DB_FILE_MULTIBLOCK_READ_COUNT, as a suggested performance improvement, with
> disastrous results.  I asked OWWS what was going on & they referred me to HP.
> To get things back under control I returned the DB's
> DB_FILE_MULTIBLOCK_READ_COUNT to 8 which was a very dramatic improvement & put
> the matter on the list of items to discuss with our HPCE on his next pending
> visit.  Now I wish I could remember the guys name because he earned a great

deal
> of respect from me on his capabilities with HP-UX, all I can remember is that > his first name was Ben. He was later, to my great consternation, transferred to
> Atlanta by HP & I understand was one of the authors of HP-UX 11. Anyhow, he was
> as much puzzled as I was & took the question back to the office with him. About

> a month later I got an e-mail from an individual at HP's Atlanta office which
> contained a page from some manual that I have never been able to get hold of
> that basically said that "irrespective of other parameters, the OS will not
> return more than 32K of data from disk to any one process in any one time
> slice".  His recommendation was to set DB_FILE_MULTIBLOCK_READ_COUNT *
> DB_BLOCK_SIZE <= 32K.  I followed his recommendation with remarkable results,
> many end users wanted to know if we had purchased a new server.  And have

stuck
> to it to this day WITHOUT getting burned or even scorched. I did try a larger > value recently & although it did not misbehave as before, I was disappointed by

> the results.  At any rate, I'm NOT an SA & do not profess to be a UNIX Guru,
> more a jack of all OS's and a master of NONE.  But your question did spawn a
> second look out on Metalink.  May all find the attached helpful.
> 
> Dick Goulet
> 
> 
> 
> 
> 
>     Unix Installation/OS: RDBMS Technical Forum
>   Displayed below are the messages of the selected thread.
> 
>   Thread Status: Closed
> 
>   From: Robert Hernandez 14-Jun-00 21:19
>   Subject: max db_block_size*db_file_multiblock_read_count for different OS
> 
>   RDBMS Version: Oracle 8.x.x
>   Operating System and Version: HPUX-11.0,SUN 7.0/8.0,AIX 4.3
>   Error Number (if applicable):
>   Product (i.e. SQL*Loader, Import, etc.):
>   Product Version: 8.x.x
> 
>   max db_block_size*db_file_multiblock_read_count for different OS
> 
>   My company is just finishing a new warehouse product running on Oracle 8.x.x
> Enterprise Edition and HPUX 11.0. We are thinking to port it now to different

> platform. I looking for the following information to help me recommend we OS
> platform we should support next.
> 
>   I understand the max db_block_size*db_file_multiblock_read_count for
>   HP-UX 10.20 is 64K
>   HP-UX 11.0 is 256K
> 
>   but what about AIX 4.3
>   SUN 2.6
>   SUN 7.0
>   SUN 8.0
>   NT 4.0
>   NT 2000
>   And what is the max DB_BLOCK_SIZE for each OS running Oracle 8.1.6 EE?
> 
>   From: Oracle, Anand Viswanathan 15-Jun-00 15:05
>   Subject: Re : max db_block_size*db_file_multiblock_read_count for different

> 
>   Hi,
> 
>   db_block_size * db_file_multiblock_read_count is controlled by
>   max io that os can do.
> 
>   maxio is controlled by the kernel parameters maxphys for UFS and
>   vol_maxio for veritas filesystem on solaris.
> 
>   You have got to know the max value the os can support from the os
>   vendors for your versions.
> 
>   Irrespective of this ,we have got a kernel constant SSTIOMAX which
>   controls the maxio that particular rdbms version can support.
> 
>   So if both os side values and SSTIOMAX from the rdbms version
>   supports having a io size as specified by your db_block_size *
>   db_file_multiblock_read_count ,then your setting of
> db_file_multiblock_read_count is taken ,otherwise it defaults to lower value.
> 
>   On Oracle 8 it supports 512K,so if your os supports for this
>   parameter maxphys to be equal to 512K,then for block size of 8K
>   you can have a db_file_multiblock_read_count of 64.
> 
>   Similarly in other platforms you have to ensure the os kernel
>   parameters which controls the io size and to find out the max value
>   that can be supported for that os from the os vendor ,then if
>   you are using Oracle 8 then you can go upto iosize of 512K.
> 
>   Regards
> 
>   Anand
> 
>     Copyright (c) 1995,1999 Oracle Corporation. All Rights Reserved. Legal
> Notices and Terms of Use.
> 
> ____________________Reply Separator____________________
> Author: Steve Adams <steve.adams_at_ixora.com.au>
> Date:       7/14/00 9:53 AM
> 
> Hi Dick,
> 
> What makes you think that HP-UX has a limit of 32K per read?
> On buffered file systems all reads match the file system buffer size (normally
> 8K),
> but on raw or direct I/O you can go all the way up to MAXPHYS
> which has long been 256K and is now 1M.
> 
> Regards,
> Steve Adams
> http://www.ixora.com.au/
> http://www.oreilly.com/catalog/orinternals/
> http://www.christianity.net.au/
> 
> -----Original Message-----
> Sent:   Friday, July 14, 2000 1:09 AM
> To:     Multiple recipients of list ORACLE-L
> 
> One word of caution.  If you raise this parameter too far the operating system
> may well ignore you.  In HP-UX (the OS we use) the limit is 32K of data per

read

> therefore taking the db_block_size of 8k means that any value of
> DB_FILE_MULTI_BLOCK_READ_COUNT > 4 gets ignored.
> 
> Dick Goulet
> 
> ____________________Reply Separator____________________
> Author: Gaja Krishna Vaidyanatha <gajav_at_yahoo.com>
> Date:       7/12/00 3:36 PM
> 
> 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:
>   INET: dgoulet_at_vicr.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:
>   INET: dgoulet_at_vicr.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).

-- 
Denny Koovakattu
http://www.vitalsol.com/
http://www.oneco.net/

__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com

-- 
Author: Denny Koovakattu
  INET: denny_vk_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

Received on Fri Jul 14 2000 - 12:55:37 CDT

Re[2]: db_file_multiblock_read_count parameter