|Lesson 6||Setting the Block Size|
|Objective||Choose an appropriate block size for your database. |
Setting the Block Size and choosing an appropriate Block Size for your Database
One of the most critical decisions that you face when creating a new database is that of choosing the database block size.
The block is the level at which Oracle performs data input and output(I/O).
Data is always read and written in blocks. The
db_block_size parameter controls the block size. Some typical settings are:
db_block_size = 2048
db_block_size = 4096
db_block_size = 8192
Here are some points to keep in mind when choosing block size:
- On most operating systems, 2048 is the default block size. The maximum setting is usually 65536, but also varies by operating system.
It is important to carefully choose the block size to be used with a new database, because once the database has been created, the block size cannot be changed. If you create a database, and later decide that you want to use a different block size, your only recourse is to create an entirely new database, and transfer all your data from the old to the new.
Here are some of the pros and cons of large vs small block sizes: Each block contains a certain amount of housekeeping information as overhead. Larger block sizes contain a greater percentage of data with respect to this overhead.
- Large block sizes are often more I/O efficient for queries that scan large amounts of data.
- Small block sizes are often more I/O efficient when you have a lot of small updates taking place.
The database block size must be at least as big as your operating system block size and should be an integer multiple of that value as well. So if your operating system block size is 512 bytes, then a database block size of 4096 (512 * 8) is OK,
but a database block size of 3,723 (not wholly divisible by 512) would hurt performance.
For the COIN database, let's go with a 4096 block size. That's a conservative and reasonably safe choice. If you are running Oracle,
add the line
db_block_size = 4096 to your initCOIN.ora file