Re: Optimal DB_BLOCK_SIZE ??

Hi Ken
We have experimented on a sun solaris and on an alpha OSF/1 machine big differences in sorts elapsed and cpu time when we change the db_block_size from 2K to 4K
on sun from 1h30' with 2k block to 0h43' with 4k block on Alpha from 0h36' with 2k block to 0h18' with 4k block hope that's help
Yves
ken_at_airmail.net (Ken) wrote:

>Question: Does it ever make sense to set the DB_BLOCK_SIZE to a value
>*smaller* than the Operating System's block size? (My thought is: never.)
 

>Recently a few of us in our shop have been having a raging discussion
>(debate, dispute, etc.) over setting the DB_BLOCK_SIZE value for a large (50
>gig) Decision Support database and I'd like feedback from others so here
>goes. [We are in the process of making changes prior to implementation and
>we've decided to rebuild the database in order to change the block size,
>too.
 

>The default DB_BLOCK_SIZE for our platform (Pyramid Nile DC_OSx 1.1) running
>Oracle 7.1.6.2 is 2K. There are about 120 tables some very small, several
>very large (200 meg--1 gig data). 800 users will be doing pure ad hoc
>queries (yeah, I know it scares me too!) No OLTP at all. 20 of the largest
>tables are manually striped across 10 disks with each disk on a different
>controller. Indexes are on different disks and the system files are on
>different disks and controllers than the data and indexes. We are using the
>UNIX file system (not raw).
 

>When reading several different sources discuss the DB_BLOCK_SIZE setting
>they don't come out right out and say it but it appears they often assume
>that the default setting of the UNIX operating system (OS) block size is
>also 2K. Therefore when they suggest experimenting with different block
>size values "in multiples of the OS block size" like 2K 4K 8K, they are
>actually suggesting that you set the Oracle block size to a 1:1 or 1:2 or
>1:4 ratio (Oracle block:OS block).
 

>Our UNIX OS block size is 16K and it supports "2K fragments" which as I
>understand it is a feature that helps to more efficiently use space and does
>not affect file I/O (like dynamically changing O/S block size I/O).
>Our platform specific doco indicates that the DB_BLOCK_SIZE can be set to
>16K. Given that, it doesn't seem to make any sense to me at all to ever set
>the DB_BLOCK_SIZE to anything less than 16K regardless of what type of
>application (OLTP or OLAP).
 

>Is there any additional UNIX overhead in setting the DB_BLOCK_SIZE to 16K
>instead of 8K when the OS is already at 16K? Is there any additional
>overhead to Oracle in using 16K rather than 8K when the OS is already at
>16K?

>Ken Robinson

Yves Raisin                                     tel   : +41 21 341 81 11
Ofisa Informatique                              fax   : +41 21 341 84 57
Chemin des Charmettes 7                         e-mail: ra_at_ofisa.ch