| Lesson 2 | Determining an appropriate database block size |
| Objective | Set the db_block_buffers parameter |
Determine Database Block Size
Using block size for tuning
As we know from experience with Oracle, tuning is usually a series of trade-offs. Oracle must fetch a data block from the disk or data buffer whenever row information is requested. These fetch requests often translate into high I/O for our database.
Because the OS resources to fetch a small block (2k or 4k) are not significantly different from the resources required to fetch a large block (8k or 16k), I/O can often be minimized by making your db_block_size as large as possible. Large block sizes mean that more RAM must be available in the db_block_buffers.
Because the OS resources to fetch a small block (2k or 4k) are not significantly different from the resources required to fetch a large block (8k or 16k), I/O can often be minimized by making your db_block_size as large as possible. Large block sizes mean that more RAM must be available in the db_block_buffers.
Oracle only allows one db_block_size for the entire database. Hence the DBA must decide on a common block size to service most queries with a minimum of I/O. If your database server has enough memory, and your buffer hit ratio does not suffer because of the increased demands on the RAM, you usually will see performance improvements with large block sizes.
Choosing data block size
There are two factors to consider in setting an appropriate block size: number of rows requested and future reads from the database.
Number of rows requested
Since you have a limited amount of memory, fetching a large block when you only need a single row wastes a huge amount of RAM in the db_block_buffers, and may take space from other blocks. Fetching a small block size would use less space.
Let us review the example below.
Let us review the example below.
- We set our db_block_size to 64K and populate our database with rows.
- We request a row, causing Oracle to read a 64K block into the data buffer.
- For this buffer, we read our 80 byte row
Oracle block buffers
Future reads from the data block
If you read adjacent information from the data block, setting a large block size will reduce the number of I/Os per transaction.
For example, in a read from a range of customer records stored in time sequence, because the other customer rows are very likely to reside beside one another on the same data block, it is possible to complete the query with one I/O.
In a range scan using an index, because index nodes are stored next to one another on the data block, rather than on several smaller blocks, scanning the index on one large block greatly reduces I/O.
Click the link below to view the pictorial illustration.
For example, in a read from a range of customer records stored in time sequence, because the other customer rows are very likely to reside beside one another on the same data block, it is possible to complete the query with one I/O.
In a range scan using an index, because index nodes are stored next to one another on the data block, rather than on several smaller blocks, scanning the index on one large block greatly reduces I/O.
Click the link below to view the pictorial illustration.
Oracle index scan
Our one I/O returns 800 times more data in a single row fetch.
The next lesson demonstrates how to optimize use of space within Oracle data blocks.
Our one I/O returns 800 times more data in a single row fetch.
The next lesson demonstrates how to optimize use of space within Oracle data blocks.
