Important Oracle Blocksize Performance bugs

In general, different blocksizes can improve performance in a variety of ways for segments with specialized I/O requirements, but they are normally reserved for large mission-critical databases with high transaction loads:

Contention reduction - small rows in a large block perform worse under heavy DML than large rows in a small blocksize.
Faster updates - Heavy insert/update tables can see faster performance when segregated into another blocksize which is mapped to a small data buffer cache. Smaller data buffer caches often see faster throughput performance.
Reduced Pinging - RAC can perform far faster with smaller blocksizes, reducing cache fusion overhead.
Less RAM waste - Moving random access small row tables to a smaller blocksize (with a corresponding small blocksize buffer) will reduce buffer waste and improve the chance that other data blocks will remain in the cache.
Faster scans - Tables and indexes that require full scans can see faster performance when placed in a large blocksize

The selective use of different blocksizes can result in stunning performance gains for objects with specialized I/O needs, but careful testing is require before implementing multiple blocksizes, and it is critical to test different blocksizes under real-world workloads, with a multi-user test on a representative copy of the production database.

Inadequate testing?

Oracle does not reveal the depth of their quality assurance testing, but many Oracle customers believe that Oracle does complete regression testing on major features of their software, such as blocksize. However, Oracle ACE Director Daniel Morgan, says that “The right size is 8K because that is the only size Oracle tests”, a serious allegation, given that the Oracle documentation, Oracle University and MOSC all recommend non-standard blocksizes under special circumstances:

Large blocks gives more data transfer per I/O call.
Larger blocksizes provides less fragmentation (row chaining and row migration) of large objects (LOB, BLOB, CLOB)
Indexes like big blocks because index height can be lower and more space exists within the index branch nodes.
Moving indexes to a larger blocksize saves disk space. Oracle says "you will conserve about 4% of data storage (4GB on every 100GB) for every large index in your database by moving from a 2KB database block size to an 8KB database block size."

So, does Oracle really not do testing with non-standard blocksizes? Oracle ACE Director Morgan says that he was quoting Bryn Llewellyn of Oracle Corporation:

“Which brings us full circle to the statement Brynn made to me and that I have repeated several times in this thread. Oracle only tests 8K blocks.”

While Oracle does not release details of their testing with multiple blocksizes, there are many reports of important performance bugs relating to different blocksizes, some dating back to Oracle9i. If true, these allegations might make new shops think twice about deploying software that has not been properly tested.

Oracle guru Steve Karam recently achieved a 20x performance gain from moving a database from a 16k blocksize to a 4k blocksize, and there are other industry reports that there are significant bugs in Oracle relating to blocksize, especially when used with large blocksizes and bitmap freelists (ASSM).

There may be an important bug in 16k blocksizes when used with ASSM which has been unfixed since Oracle 9i.
This OTN thread suggests a bug in Windows servers running 16k blocksizes.
Blogger David Aldridge also noted a blocksize bug that is exclusive to 32k blocksizes:

"I have previously encountered bugs on bitmap index corruption on a 32kb data segment blocksize that made me cautious of the risks of using extreme values for limited benefits”. It was a 32kb bug, not a 16kb bug."

In sum, different blocksizes can work wonders under certain circumstances, but special care must be taken to ensure that you run proper benchmark tests before deploying a non-standard Oracle blocksize.

To learn more, see my related notes on Oracle blocksize performance:

The benefits of using multiple blocksizes
Vendor notes on multiple block sizes
A simple example of using multiple block sizes
DBA reports of differing response time with different block sizes
Are multiple block sizes right for your database?

��