Message-Id: <10558.112064@fatcity.com> From: dgoulet@vicr.com Date: Fri, 14 Jul 2000 09:30:10 -0400 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____________________ Subject: RE: Re[2]: db_file_multiblock_read_count parameter Author: Steve Adams 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----- From: dgoulet@vicr.com [SMTP:dgoulet@vicr.com] Sent: Friday, July 14, 2000 1:09 AM To: Multiple recipients of list ORACLE-L Subject: Re[2]: db_file_multiblock_read_count parameter 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 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 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@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@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@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@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@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@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@fatcity.com (note EXACT spelling of 'ListGuru') and in