Re: reduce block size?

I would say that the recommendation to reduce your block size from 8k to 2k is nonsense.

Most operating system work with an IO block size of 8k. This means that if Oracle reads a single 2k block into SGA, 6k of the IO is wasted. Not very efficient.

I've found that IO is optimal when the block size is equal to or a multiple of the operating system IO block size eg 8k or 16k. It most cases 8k is the right size but occasionally 16k will give a better IO performance especially if working with hash clusters.

The only trade-off is that a larger block size requires more RAM. However RAM is cheaper that IO, easier to add to your system, and OLTP often become IO-bound before memory-bound, especially if you have any batch processes running against the database.

I suggest you keep it at 8K.
Ciaran Harron

David Spaisman wrote:

> Hello:
>
> I ma working with an Oracle 8.0.4 application on NT 4 sp4. There are
> about 125 users and the applicaton is definitely transaction-based wit
> about 125 tables. This application serves users in the U.S. We have a
> similar IT group in Europe and the same application there. They
> currently have less users buit the application is expected to grow(I
> don't know at what rate)..
>
> My DBA associate in Europe had a consultant(I believe from Oracle)
> review the application and made three recomendations ;1) Take care of
> some fragmented tables 2) decrease the shared pool as it was causing
> memory swapping and 3) reduce the block size from 8k to 2k because of
> the transactional nature of the application.
>
> I agree with the first two recommendations but I am quite surprised
> about the third: reducing the block size.
>
> Has any one ever seen this type of recommendation ? Is it correct or
> not? Has any one ever done this?
>
> If you need any additional information, please let me know. Thanks.
>
> David Spaisman
Received on Thu Sep 09 1999 - 22:43:46 CDT