Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: formulas for correct sizing of various parameters

Re: formulas for correct sizing of various parameters

From: Howard J. Rogers <howardjr_at_www.com>
Date: Sat, 1 Sep 2001 09:52:12 +1000
Message-ID: <3b902213@news.iprimus.com.au>

"Pete Finnigan" <pete_at_peterfinnigan.demon.co.uk> wrote in message news:UrsAyTApR0j7Ewt3_at_peterfinnigan.demon.co.uk...
> Hi
>
> The block size you choose should be a multiple of the OS block size,
> otherwise reads and writes of Oracle blocks could / would cross OS block
> boundaries. The block size you choose depends on the type of database
> and applications you have ie OLTP. In batch processing for instance
> where you have large amounts of data to process a larger block size is
> better and a large db_block_buffers, as the data you need will tend to
> be in the SGA more, reducing disk reads. For OLTP where random reads
> occur a smaller block size is better to reduce the read times and the
> amount of data stored in the SGA that is not likely to be needed. ie
> users could read data from anywhere in OLTP, in batch processing the
> data is likely to be read in some sort of order and processed in some
> sort of order. Just picking a general size such as 8k as Steve Adams
> suggests might work in most occasions, but in larger systems choosing
> the correct block size can make quite a difference in performance.
>

Wrong, wrong, wrong! Steve Adams doesn't suggest a 'general' size. He suggests a specific size, and explains why it's the right one, and it's to do with an interaction with your file system. Anything other than that, and you induce performance degradation.

Of course, that's an ideal -and for sure, if you are so hard up for resources that you can't allocate sufficient buffers to the Buffer Cache, then the business of reading 8k into memory when you only want to access 458 bytes of it is certainly a consideration.

But as Steve also points out, a larger block size makes for huge performance improvements on index reads -and what do OLTP systems use to retrieve the small bits of data they want to access? Yup -indexes.

Block size is file-system dependent, not application-dependent, unless you are short on resources (iun which case you probably shouldn't be using Oracle in the first place).

Regards
HJR
> In view of the above, I cannot see easily how one formula can be used.
>
> just my two penneth
>
> cheers
>
> Pete Finnigan
> www.pentest-limited.com
>
> In article <3B8D37BC.5B72B7FB_at_mitre.org>, David Wilburn
> <dwilburn_at_mitre.org> writes
> >I've got plenty of books and documents that all say that specifying
> >correct values for initial/next/max extents, db_block_size, etc., are
> >all critically important. However, I've yet to find any formulas
> >specifying how to come up with the correct values.
> >
> >I've read that (at least of solaris) the block size should be some
> >multiple of the output of the command 'pagesize' (in this case, 8KB),
> >but I'm not sure whether it should be 8KB, 16KB, etc. Which should it
> >be?
> >
> >How do I calculate appropriate values for initial/next/max extents?
> >
> >We're getting a new system in shortly, and I'll have the opportunity to
> >rebuild our DB, so I'd like to get things right the first time.
> >
> >-Dave Wilburn
>
> --
> Pete Finnigan
> IT Security Consultant
> PenTest Limited
>
> Office 01565 830 990
> Fax 01565 830 889
> Mobile 07974 087 885
>
> pete.finnigan_at_pentest-limited.com
>
> www.pentest-limited.com
Received on Fri Aug 31 2001 - 18:52:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US