Re: Odd statement in docs regarding block size

Absolutely 100% wrong, and another classic myth (backed up, in this case, by some rather misleading comments in the Oracle doco., I grant you).

Contention is most certainly an issue to worry about. But do you cure your headaches by cutting off your head? Curing contention by reducing your block size will indeed cure it... and cripple performance into the bargain. It's a rather drastic approach, in other words.

It's perfectly possible to reduce the *useable* space in a block, without altering the block size. You use initrans, maxtrans and pctfree to do so. Bump pctfree up to 80 or 90, for example, and a 16K block will actually have about as much room in it as a 2K block.

What's also missing from your analysis is the effect of a larger block size on indexes. And both OLTP and Data Warehouses use indexes. A lot.

Regards
HJR "Ganesh Raja" <ganesh_at_gtfs-gulf.com> wrote in message news:a8aed4.0205290531.3bebd803_at_posting.google.com...
> dbuckingham <member_at_dbforums.com> wrote in message
news:<3cf471bd$1_at_usenetgateway.com>...
> > HJR comments that in NT/2000 he always sets the DB_BLOCK_SIZE to be 16k
> > and others back that it should be either the same as or a multiple of
> > the os_block_size. In NT/2000 I believe that the default is 8k, so is
> > there a real need to set them at 16k?
> >
> > Basically I am after some clarification as to how I should set them in
> > NT/2000 for currently we have them set to 8k and i want to know if this
> > is optimal?
> >
> > Any comments.
>
> Your Block Size should depend on the Application Type You ar using. In
> case of an OLTP Application 4K or 8K will do a lot of good to the
> database throughput. In a Database that caters to OLAP/ DW
> Applications it is better to keep it at 16 or 32K[not sure if 32 is
> possible in NT].
>
> This Difference arises Because of the way Data is requested in the
> Applications. In an OLTP Applications it will be More Of
> Insert/Updates and deletes and if they are not directed at the same
> block it wil reduce contention. But in the case of a DW Application It
> is Huge Amount of data that is selected for analysis and if this data
> is packed into a Few number of blocks the number of blocks visited by
> Oracle will be less and hence the response time will really increase.
>
> HTH
>
> Regards,
> Ganesh R
Received on Wed May 29 2002 - 14:41:06 CDT