Advantages and Disadvantages of using smaller and bigger data blocks

Advantages and Disadvantages of using smaller and bigger data blocks
Whether I will use bigger or smaller data blocks in my database it can be specified by parameter DB_BLOCK_SIZE or DB_nK_CACHE_SIZE. With the settings I can I can differentiate three types of data blocks in oracle.

1)Small Block(2KB-4KB)
2)Medium Block(8KB)
3)Large Block(16KB-32KB)

Advantages of Bigger Blocks
--------------------------------------------------------------------
•Using bigger blocks means more data transfer per I/O call. So faster data transfer from disk to memory.

•Using bigger blocks means more space for key storage in the branch nodes of B*-tree indexes, which reduces index height, which improves the performance of indexed queries.

•When using large block there are less probability of chained and migrated rows, which in turn reduced the number of reads required to get the information.

Disadvantages of bigger Blocks
----------------------------------------------------------------------
•If the rows are predominated random then you are increasing the possibility of contention in the buffer cache. Because now with same same amount of memory in buffer cache as it was in small blocks, we need more memory in the buffer cache to keep the same amount of buffers in memory in the buffer cache.

•If you have high transactional concurrency to a segment, using bigger blocks is just going to make the concurrency even higher.


Advantages and disadvantages of these blocks
---------------------------------------------------------------------
1)Small Block(2KB-4KB): The advantage of small blocks are they reduce block contention and they are really good where there is small rows or the selectivity of rows are highly random.

The disadvantages of small blocks are they have relatively larger overhead.

2)Medium Block(8KB): The advantage of medium blocks are if the rows are of medium size then you can bring a number of rows in a single I/O.

The disadvantage of it is space in the buffer cache will be wasted if you are doing random access to small rows and have a large block size. For example, with an 8KB block size and 60 byte row size, you are wasting 8000-60=7940 bytes in the buffer cache when doing random access.

3)Large Block(16KB-32KB): If you use larger block then relatively less overhead. Per I/O you can fetch more data. This is very good for sequential access, or very large rows.

Large block size is not good for index blocks used in an OLTP(Online Transaction Processing) type environment, because they increase block contention on the index leaf blocks.

Related Documents:
-------------------------
General Idea of Database Block Size and BLOCKSIZE
Choose an optimal Data block size in Oracle

DB_BLOCK_SIZE, DB_nK_CACHE_SIZE and BLOCKSIZE
First I want to say about BLOCKSIZE cluase in oracle. The BLOCKSIZE cluase is used to specify the block size for the tablespace. If you don't specify this clause while tablespace creation then standard that is default blocksize is used which is specified by parameter DB_BLOCK_SIZE. For example, You have DB_BLOCK_SIZE set to 8K=8192 and you specified CREATE TABLESPACE command without any BLOCKSIZE clause then database use 8k blocksize for the specified tablespace.

The parameter DB_BLOCK_SIZE specifies the size of Oracle database blocks in bytes. The default value of this parameter is 8192 and value ranges between 2048 to 32768. But it must be multiple of physical block size at device level.

Now lets have an attention of the DB_nK_CACHE_SIZE parameters. Here n is variable and can be 2, 4, 8, 16, 32. That is DB_2K_CACHE_SIZE, DB_4K_CACHE_SIZE, DB_16K_CACHE_SIZE etc are available. Now question may come why we will set DB_nK_CACHE_SIZE? What advantage we will get. Before look for advantage (which will be discussed in another topic) let me why we need to set this parameter? This parameter will need to be set whenever I wish to make or want to make a tablespace with non-standard data block size.

Suppose your standard block size that block size set by DB_BLOCK_SIZE is 8K and you want to make a tablespace with block size 16K then at first you need to set DB_16K_CACHE_SIZE and then you need to create tablespace with BLOCKSIZE clause specifying 16K.

Here is an example which shows database standard block size is 8k and you have made an tablespace with 16k blocksize.

Example:
------------------------------
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192

ALTER SYSTEM SET DB_16K_CACHE_SIZE=200M SCOPE=BOTH;
ALTER SYSTEM SET DB_CREATE_FILE_DEST='/oradata2';
CREATE TABLESPACE TEST BLOCKSIZE 16K;

Remember You can set this parameter only when DB_BLOCK_SIZE has a value other than nK. For example, if DB_BLOCK_SIZE=8192, then it is illegal to specify the parameter DB_8K_CACHE_SIZE Because the size for the 8 KB block cache is already specified by DB_CACHE_SIZE.

Choose an optimal Data block size in Oracle
Before going to proceed it will be better if you have an idea about DB_BLOCK_SIZE, BLOCKSIZE and DB_nK_CACHE_SIZE. In my post B_BLOCK_SIZE, BLOCKSIZE and DB_nK_CACHE_SIZE I tried to give an idea.

The default database data block buffer size is specified by the parameter DB_BLOCK_SIZE. It is common to ask what will be the data block size of my oracle database? Should I increase or decrease data block size whenever there is performance issue of my database. There is common scenario that with one blocksize a query takes 30 minutes and with another query of a tablespace a query takes 2 or 3 minutes. So choosing an optimal data block size is very necessary task.

The general rule while choosing an optimal data block size is,

•For OLTP (Online Transaction Processing) systems use smaller block sizes.
•For DSS (Decision Support Systems) systems use larger block sizes.

Whatever the size of the data, the goal is to minimize the number of reads required to retrieve the desired data.

•If the rows are small and access to the rows are random , then choose a smaller block size.
•If the rows are small and access is sequential, then choose a larger block size.
•If the rows are small and access is both random and sequential, then it might be effective to choose a larger block size.
•If the rows are large, such as rows containing large object (LOB) data, then choose a larger block size.


In my next topics I demonstrated on advantage and disadvantage of settings different types of blocks.