Oracle Space Block Size

Question: How big is a block size in Oracle 13c?
In Oracle Database 13c, the block size refers to the fundamental unit of storage for the database. Block size is crucial in determining the efficiency of storage management and data retrieval, and it directly impacts the performance of the database. The default block size for Oracle Database 13c is typically 8 KB (8,192 bytes). However, Oracle provides the flexibility to use different block sizes ranging from 2 KB to 32 KB, in increments of 2 KB. These block sizes can be configured depending on the specific requirements and characteristics of the database and its associated applications. To configure the block size in Oracle 13c, you can set the DB_BLOCK_SIZE initialization parameter during the creation of the database. For instance, to set a block size of 16 KB, you can include the following line in your init.ora file or within the CREATE DATABASE statement:

DB_BLOCK_SIZE = 16384

It is important to note that once a database has been created, the block size cannot be changed without recreating the database. Therefore, careful consideration and planning are essential when deciding on the appropriate block size for your Oracle 13c database.
In summary, the block size in Oracle Database 13c can range from 2 KB to 32 KB, with the default being 8 KB. The choice of block size should be based on the specific needs and performance requirements of your database and applications.

There is only one reason not to use the largest block size available in your environment for a new database:
If you cannot support a greater number of users performing updates and inserts against a single block. Other than that, increasing the database block size should improve the performance of almost everything in your application. Larger database block sizes help keep indexes from splitting levels and help keep more data in memory longer. To support many concurrent inserts and updates, increase the setting for the pctfree parameter at the object level.

Oracle stores blocks of data in memory. It is in your best interest to make sure those blocks are as densely packed with data as possible. If your data storage is inefficient at the block level, you will not gain as much benefit as you can from the caches available in the database. If the rows in your application are not going to be updated, set the pctfree as low as possible. For partitioned tables, set the pctfree value for each partition to maximize the row storage within blocks. By default, each partition will use the pctfree setting for the table. Set a low pctfree value for indexes. If the pctfree setting is too low, updates may force Oracle to move the row (called a migrated row). In some cases row chaining is inevitable, such as when your row length is greater than your database block size. When row chaining and migration occur, each access of a row will require accessing multiple blocks, impacting the number of logical reads required for each command. You can detect row chaining by analyzing the table and then checking its statistics via USER_TABLES; the Chain_Cnt column will be populated with the number of chained rows when the table is analyzed.