Dick,
If you had set the DB_FILE_MULTIBLOCK_READ_COUNT to be 800 then I would
ASSUME that Oracle will reset that value to be max_io_size/db_block_size
(maximum allowed).
I believe the disastrous results might be that since the
db_file_multiblock_read_count is set to the maximum allow, the optimizer
might choose to do a full-table-scan more than usual. Folks, please correct
me if I'm wrong.
Cheers,
- Sah
> -----Original Message-----
> From: dgoulet_at_vicr.com [SMTP:dgoulet_at_vicr.com]
> Sent: Friday, July 14, 2000 10:36 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re:RE: Re[2]: db_file_multiblock_read_count parameter
>
> 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 OS
> 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 OS
>
>
> 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
Received on Fri Jul 14 2000 - 09:14:07 CDT
