Re: DB Block Size-Your Opinions

Chakravarthy KM Nalamotu (kittu_at_Hawaii.Edu) wrote:
: I came across a general recommendation to use 8K blocks
: for databases that are large. My Definition of large
: is anything greater than 20 GB.
: Wouldn't it be better to use 4K or even 2K blocks if I am
: sure that a single row of any table in the database will
: not be more than 4K or 2K?
: This way I am not briniging in all the unnecessary rows
: into the cache. Furthermore, cache has larger number
: of smaller relevant data blocks.
: Your thoughts on this topic are greatly appreciated.

: Thank you,
: Kittu.

I think block size should be determined by type of activities of database, not the size, i.e. whether it's OLTP or Report.

Larger size makes BIG difference in full table scans so it is more appropriate for data warehouse or report database.

If you are OLTP intensive, regardless how big your database is, smaller blocks would avoid buffer busy waits.

The thought of smaller block size is more because of contention reasons than economy reasons ( SGA wise ).

The theory is simple. Larger blocks contains more rows, so the chances of having contention (buffer busy waits ) in accessing the blocks in the memory is higher. This can be identified by querying v$waitstat ( check for db block wait ) and v$session_wait ( check for buffer busy wait ).

But in real life, most databases are sort combined. It is not practical to completely avoid full table scans, so my vote is still for larger block size, unless you are really really OLTP.

-Bass Chorng Received on Mon May 10 1999 - 12:42:51 CDT