DBAs must decide what the standard database block size should be when they first creat a new Oracle database.. Oracle generally supports five options, 2K, 4K, 8K (now the default), 16K and 32K, which is determined by the DB_BLOCK_SIZE parameter. It is virtually impossible to change the standard block size once the database is created, without recreating the entire database.
The block size is important because it has inherent performance impacts that range from contention to reads. The smaller blocks are better to reduce contention because there are usually fewer rows per block and this is generally favored for online transaction systems. Data warehouse or DSS systems, on the other hand, benefit from a larger block size because Oracle is able to retrieve more rows per block read. Fortunately, we have had the ability to create tablespaces with different block sizes, since Oracle 9i, which gives DBAs the ability to even further control storage options and allows for transportable tablespaces between databases.
It is well understood that the DB_BLOCK_SIZE becomes the default block size for the data files for any tablespace that is created without a specific blocksize option. However, folks may also assume that the default block size actually applies to ALL of the Oracle database files associated with their databases, however, this is not the case. The DB_BLOCK_SIZE parameter does not control the size of the redo log files (this includes online redo logs, archived redo logs and standby redo logs) nor the control file.
Read the full story at Database Journal:
Understanding 4KB Sector Support for Oracle Files
Back to DBAsupport.com
