Data Blocks  «Prev 

Oracle Index Scan

Specifying Database Block Sizes

The DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database.
This block size is used for the SYSTEM tablespace and by default in other tablespaces. Oracle Database can support up to four additional nonstandard block sizes.
DB_BLOCK_SIZE Initialization Parameter
The most commonly used block size should be picked as the standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is set to either 4K or 8K. If you do not set a value for this parameter, the default data block size is operating system specific, which is generally adequate.
You cannot change the block size after database creation except by re-creating the database.
If the database block size is different from the operating system block size, ensure that the database block size is a multiple of the operating system block size. For example, if your operating system block size is 2K (2048 bytes), the following setting for the DB_BLOCK_SIZE initialization parameter is valid:


The parameter DB_BLOCK_SIZE multiplied by the parameter DB_BLOCK_BUFFERS determines the size of the buffer cache. BUFFER_POOL_RECYCLE and BUFFER_POOL_KEEP determine the size to be allocated to the RECYCLE and KEEP pools, respectively, from the buffer cache. When creating or altering tables and indexes, you can specify the BUFFER_POOL in the STORAGE clause.

1) We set our db_block_size to 64K and populate our database with rows.

2) We request an index range scan, causing Oracle to read a 64K data block into the data buffer

3) From this buffer, we read our index nodes, and because all of the index nodes reside on the same data block, we complete our index range scan with only one I/O.

Ad Oracle DBA Cloud