Use Multiple blocksizes in Oracle

The introduction of Oracle 9i brought an amazing amount of complexity to the Oracle database engine. Oracle introduced many new internal features, including bitmap free lists, redo log based replication, dynamic SGA, and perhaps the most important feature of all, the ability to support multiple block sizes.

Multiple blocksizes are indeed beneficial in large databases to eliminate superfluous I/O and isolate critical objects into a separate data buffer cache. This allows large tables and indexes to have a larger block size than smaller objects. This is especially useful in hybrid databases where DSS transactions benefit from large block sizes, whilst OLTP operations are best suited to smaller block sizes. It also means that tablespaces can be transported between databases with differing block sizes. The database is created with a standard block size and up to 5 none-standard block sizes.

The DB_BLOCK_SIZE is used to specify the standard block size which is used for the SYSTEM and TEMPORARY tablespaces. All subsequent tablespaces will also be assigned this block size unless specified otherwise. To see default block size of the Database :
SQL> conn system/password as sysdba;
SQL> show parameter DB_BLOCK_SIZE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192


The DB_CACHE_SIZE parameter, rather than DB_BLOCK_BUFFERS, is used to define the size of the buffer cache for the standard block size. This can be set to any size, but it will be rounded to the nearest whole granule. If the total SGA is less then 128M the granule size is 4M, greater than 128M and it becomes 16M. The DB_CACHE_SIZE must be at least 1 granule in size and defaults to 48M. An individual buffer cache must be defined for each non-standard block size used. These are set up with the following parameters which default to a size of 0

DB_2K_CACHE_SIZE = 0M
DB_4K_CACHE_SIZE = 0M
DB_8K_CACHE_SIZE = 0M
DB_16K_CACHE_SIZE = 0M
DB_32K_CACHE_SIZE = 0M

Each cache that is used must have at least 1 granule assigned to it. The instance must be restarted before changes to these parameters take effect. The DB_nK_CACHE_SIZE parameters can only be set to zero if there are no online tablespace with a nK block size.

The following example shows whole the process of setting up a 16K buffer cache and defining a tablespace to use it:

SQL> CONN sys/password AS SYSDBA
Connected.
SQL> ALTER SYSTEM SET DB_16K_CACHE_SIZE=50M SCOPE=SPFILE;
System altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 787968 bytes
Variable Size 78641664 bytes
Database Buffers 88080384 bytes
Redo Buffers 262144 bytes
Database mounted.
Database opened.

SQL> CREATE TABLESPACE test_16k_ts DATAFILE '/u01/oradata/TSH1/test_16k_ts01.dbf' SIZE 100M BLOCKSIZE 16M;
Tablespace created.
SQL>

The KEEP and RECYCLE buffer caches can only be defined for the standard block size. In 8i these were defined using the BUFFER_POOL_KEEP and BUFFER_POOL_RECYCLE parameters, with their memory was taken from the total DB_BLOCK_BUFFERS. In 9i the parameters have been changed to DB_KEEP_CACHE_SIZE and DB_RECYCLE_CACHE_SIZE, with their memory allocations being totally separate to the main buffer cache.

A number of rules apply to the use of variable block sizes:

1. All partitions of a partitioned object must reside in tablespaces with the same block size.
2. All temporary tablespaces must be of the standard block size.
3. Index Organized Table Overflow and out-of-line LOB segments can be stored in a tablespace with a different block size that that of the base table.