Re: 9i multi cache buffer

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<xzZz9.74811$g9.210347_at_newsfeeds.bigpond.com>...
> There is no keep cache (nor a recycle cache) for any non-default block sizes
> (which is one reason, amongst many, why the use of non-standard block sizes
> is a very, very bad idea. They were invented to make tablespace
> transportation a practical possibility, and for no other reason. They are
> likely to play merry havoc with performance tuning, not improve things).
>
> Therefore, your table A will always be housed in the 2K buffer (as sized by
> your db_2K_cache_size parameter).
>
> There is a right block size for a database, and a wrong size. And it all
> depends on what operating system you are using. Therefore my strong advice
> is: unless you are transporting tablespaces, avoid this "feature" like the
> plague.
>
> I quote this little snippet from Steve Adams' website (www.ixora.com.au):
>
> Under Oracle9i individual tablespaces can have a block size that differs
> from the database block size. Of course, this should not be considered if
> the database is using buffered I/O, because in that case the database block
> size must match the file system buffer size exactly.
>
> 'Nuff said.

Here's a different view.

Oracle announced a record breaking 4CPU TCP-C (Linux and HPUX, identical Intel hardware, Oracle10) to coincide with OracleWorld. This was achieved using multiple buffer caches:

db_cache_size = 3500M
db_8k_cache_size = 512M
db_16k_cache_size = 3000M
db_keep_cache_size = 37750M
db_recycle_cache_size = 50M

TPC-C is an extreme OLTP workload characterised by small transactions and concurrent block access by multiple sessions. In this case a small blocksize is good - evidenced by the fact the TPC-C database uses db_block_size=2048.

Smaller block size means latches for block access (data and index) are held for shorter time, increasing concurrency. For other workloads (e.g DSS) larger block sizes may better suit the usage profile of the data.
Same for UNDO, for which Oracle used 8K blocks in the benchmark. What about a situation where your table has large rows (say 16K each) with small index entries (say a numeric primary key)? Do you really want them both stored in blocks of the same size?

Multiple buffer caches can significantly improve performance in the right circumstances. Only you can tell whether they apply to your situation.

Geoff Ingram

http://www.dbcool.com The all-free no-timeout Oracle tuning tool

Author "High Performance Oracle" Received on Thu Nov 14 2002 - 06:38:56 CST