Re: Optimal db_block_size

In article <36BB0538.551B6330_at_csgsystems.com>,   Terry Ball <terry_ball_at_csgsystems.com> wrote:
> A lot depends on what the database is being used for and/or the size of the
> tables. If you are using your database for Decsion Support or as a
> DataWarehouse, a larger block size is probably better. But if it is for
> OLTP, the smaller block size usually better.
>
> If you have tables that have a lot of chaining because the data can not fit
> in one block, then a larger block size is better.
>
> Terry Ball
> Sr. DBA, CSG Systems
>
> Peter Laursen wrote:
>
> > Whats is the optimal db_block_size on NT4.0 running Oracle 8.04?
> >
> > Ive been told never change the default 2k size, however now I hear 8k is
> > better? Anyone?
>

I have changed an OLTP that was built under VMS at 2K to 4K and seen a definite sustained improvement in performance. I have also converted another database built under Unix at 4K to 8K and again the same results. I am pretty sure the results are not due to the total re-org of the database since we re-organized all objects before making the change, and the rebuild was not followd by a slow degradation in performance over time.

For an OLTP I would think 8k is good, while for a OLAP, DSS, or true Warehouse a larger blocksize may well be appropriate especially if the OS supports a larger physical IO size. _________ Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Feb 05 1999 - 11:06:11 CST