Tuesday, January 18, 2011

Database Block Size


DB_BLOCK_SIZE is the SGA initialization parameter that is used to determine the size of database blocks in bytes.

Oracle recommends that your database block size match, or be multiples of your operating system block size. One can use smaller block sizes, but the performance cost is significant. The choice should depend on the type of application you are running. If you have many small transactions as with OLTP, use a smaller block size. With fewer but larger transactions, as with a Data warehousing (DW) application, use a larger block size.

db_block_size = integer ( Range of values 2048 to 32768, but may be limited by operating system)

Whats is the ideal block size for My database?

There is no standard block size suggested, Based on the type of database and applications, we need to choose the block size that suits our need.
Baiscally we can choose smaller,medium and large block size, the following oulines each.

Smaller (2k, 4k) 


Good for small rows with lots of wide-spread random access I/O (row lookups by index, etc.) ·
Reduces the chances for block contention and possibility of “Buffer Busy Waits” ·


Smaller blocks support a smaller number of entries in the Interested Transaction List (controlled by InitTrans and MaxTrans),
limiting the number of concurrent changes · Not recommended for large data rows (limits the number of rows stored per block and increases potential for row chaining) ·
Less data transfer per I/O call

Medium (8k) 


The most suitable “middle ground” between block contention, efficient data storage, and I/O throughput for general transaction processing systems ·

Suggested for OLTP databases

Memory and I/O can be wasted if a majority of data is accessed randomly and consists of very small rows (<100 bytes)

Larger (16k, 32k, etc.)

More data transfer per I/O call ·

More space for index key storage, reducing index height and improved index-based queries ·
Less chance for chained and/or migrated rows · Excellent for sequential data access, table scans, etc prevalent in DSS and reporting environments 

Suggested for DW or reporting databases.

In OLTP environments, increases the potential for block contention and the possibility of “Buffer Busy Waits”, especially when updating traditional rollback segment and index leaf blocks


  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer
    LinkedIn profile -
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at | +91 - 9581017828.

  2. This article is really helpful for me. I am regular visitor to this blog. Share such kind of article more in future. Personally i like this article a lot and you can have a look at my services also: I was seriously search for a Salesforce training institutes in ameerpet which offer job assistance and Salesforce training institutes in Hyderabad who are providing certification material. It's worth to join Salesforce training institutes in India because of their real time projects material and 24x7 support from customer desk. You can easily find the best Salesforce training institutes in kukatpally kphb which are also a part of Pega training institutes in hyderabad. This is amazing to join Data science training institutes in ameerpet who are quire popular with Selenium training institutes in ameerpet and trending coureses like Java training institutes in ameerpet and data science related programming coures python training institutes in ameerpet If you want HCM course then this workday training institutes in ameerpet is best for you to get job on workday.