Tuesday, January 18, 2011

Database Block Size

What is DB_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) 


Advantages

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” ·

Disadvantages

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) 

Advantages


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

Suggested for OLTP databases

Disadvantages
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.)
Advantages

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.

Disadvantages
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 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete