Effective use of blocks in Oracle (1)

Data Block

In an Oracle database, the block is the smallest unit of data file I/O and the smallest unit of space that can be allocated. An Oracle block consists of one or morecontiguous operating system blocks.

Standard block size

• Use the db_block_size parameter when creating a database. It cannot be changed unless the database is re-created.
• Used for system and temporary tablespaces
• Db_cache_size specifies the default cache size for the standard block size:

1. Minimum size = a grain group (4 MB or 16 MB)
2. Default Value = 48 MB

The db_block_size initialization parameter is used to specify the standard block size of the database. The block size is used for the system tablespace and any temporary tablespace. Unless specified, the standard block size is also used as the default block size for the tablespace. Oracle supports up to four types of additional non-standard block sizes.

Db_cache_sizeThe parameter is used to specify the cache size of the standard block size buffer. The standard block size is specified by db_block_size.

Note:A grain group is a continuous virtual memory allocation unit. The size of the grain group depends on the total size of the estimated SGA. The total size is calculated based on the parameter value of sga_max_size: 4 MB if the size of the estimated SGA is <128 MB; otherwise, it is 16 Mb.

Non-standard block size

• Use the following dynamic parameters to configure the additional Cache

1. Db_2k_cache_size is used for 2 kb Blocks
2. Db_4k_cache_size is used for 4 kb Blocks
3. Db_8k_cache_size is used for 8 KB Blocks
4. Db_16k_cache_size is used for 16 KB Blocks
5. Db_32k_cache_size is used for 32 KB Blocks

• Db_nk_cache_size is not allowed if NK is the standard block size.
• Minimum size of each cache: one grain group

The database buffer cache initialization parameter determines the size of the SGA database buffer cache component. You can use these parameters to specify the cache size for various block sizes used by the database. If you want to use multiple block sizes in the database, you must set db_cache_size and at least one db_nk_cache_size parameter. Each parameter specifies the buffer cache size for the corresponding block size. The default value of db_nk_cache_size is zero. If an online tablespace with a block size of N kb exists, do not set this parameter to zero.

The specific block size of the platform has some restrictions. For example, if the maximum block size on the platform is smaller than 32 KB, you cannot set db_32k_cache_size. In addition, if the minimum block size is greater than 2 kb, you cannot set db_2k_cache_size.

Note:These parameters cannot be used to adjust the cache size of a standard block. For example, if the value of db_block_size is 2 kb, it is invalid to set db_2k_cache_size. The cache size of the standard block is always determined by the value of db_cache_size.

Create a tablespace with a non-standard block size

1 CreateTablespace tbs_1 datafile'Tbs_1.dbf'Size 10 m blocksize 4 K;
 

-- To specify this clause, you must set the db_cache_size parameter and at least one db_nk_cache_size parameter. The specified integer in this clause must correspond to the setting value of a db_nk_cache_size parameter.

Small block size:

• Advantages

1. Small blocks reduce block contention, because there are fewer rows per block.
2. Small blocks are good for small rows.
3. Small blocks are good for random access. if it is unlikely that a block will be reused after it is read into memory, then a smaller block size makes more efficient use of the buffer cache. this is especially important when memory resources are scarce, because

Size of the database buffer cache is limited.

• Disadvantages

1. Small blocks have relatively large overhead.
2. Depending on the size of the row you may end up storing only a small number of rows per block,. This can cause additional I/OS.
3. Small blocks can cause moreindex blocks to be read.

• Performance:

1. For random access to a large object, as in an OLTP environment, small blocks are favored

Large Block Size:

• Advantages

1. There is less overhead and thus more room to store useful data.
2. Large blocks are good for sequential reads.
3. Large blocks are good for very large rows.
4. Larger blocks improve the performance of index reads. the larger blocks can hold more index entries in each block, which has CES the number of levels in large indexes. fewer index levels mean fewer I/OS when traversing the index branches.

• Disadvantages

1. A large block size is not good for index Blocks Used in An OLTP environment, because they increase block contention on the index leaf blocks.
2. Space in the buffer cache is wasted if you randomly access small rows and have a large block size. for example, with an 8 KB block size and a 50 byte row size, you waste 7,950 bytes in the buffer cache when doing a random access.

• Performance

1. Sequential access to large amounts of data, as in a DSS environment, prefers large blocks.