POW: DB_CACHE_SIZE… and son :)

March 14, 2008  |  dba

The DB_CACHE_SIZE parameter, in old Oracle versions know as DB_BLOCK_BUFFERS,  sets the size of memory data buffer cache region.

Since 9i we can have different cache size parameters for multiple blocksizes, including DB_nK_CACHE_SIZE. For instance you can put your indexes in structures with higher cache size,  like DB_32K_CACHE_SIZE.

DB_nK_CACHE_SIZE specifies the size of the cache for the nK buffers. Generally we can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For instance when you set DB_BLOCK_SIZE=2K, then you cannot specify the parameter DB_2K_CACHE_SIZE (because the size for the 2 KB block cache is already specified by DB_CACHE_SIZE).

By standard database conception larger block sizes are appropriate to warehouse databases,  usually read-only data. Smaller block sizes are much better for OLTP databases that usually has small transactions and lots of activivity. The block size can be 2K^n where n between 1 and 5. OLTP databases benefit from smaller block sizes because disks I/O activity is frequent but also request small’s blocks of data.

So, don’t mess aroundwith OLTP with 32kb block or datawarehouse with 2KB 🙂

Nowadays a traditional and default ORACLE DB uses 8kB… but in 7.x version i had lot’s of databases with 2kb and 4kb performing very well

Keep Tuning 🙂


Leave a Reply