WARNING:  Using multiple blocksizes effectively is not simple.  It requires expert-level Oracle skills and an intimate knowledge of your I/O landscape.  While deploying multiple blocksizes can greatly reduce I/O and improve response time, it can also wreak havoc in the hands of inexperienced DBA's.  Using non-standard blocksizes is not recommended for beginners.

The use of multiple blocksizes in very large databases (VLDB) is more than 20 years old, and corporations have been using multiple blocksizes in the IDMS database with proven success since the 1980's.  There are well-documented reports of different response times using identical data and workloads with multiple block sizes.  This overview covers the following topics:

• 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?

For most databases, creating multiple blocksizes is not going to make a measurable difference, and the deployment of multiple blocksizes must be carefully evaluated on a case-by-case basis.  The DBA must carefully evaluate their database for I/O patterns and buffer efficiency to see if multiple blocksizes are right for their unique system.

While it is generally accepted that multiple blocksizes are not for every shop, they may be appropriate for large multi-terabyte databases where the DBA wants to incur the additional monitoring complexity to be able to control their I/O buffers at a lower-level of granularity.

For example, insert-intensive databases will perform less write I/O (via the DBWR process) with larger block sizes.  This is because more "logical inserts" can take place within the data buffer before the block becomes full and requires writing it back to disk.  Some shops define their high-insert tablespaces in a larger blocksize to minimize I/O, and some use SSD to achieve insert speeds of over 10,000 rows per second.

For a more complete discussion, see my Oracle Multiple blocksize tips.

Our experience with different blocksizes

BC has seen dozens of cases where changing the blocksize can have a profound impact on overall performance, both in response time and throughput performance, and the choice of blocksize is one of the most important tuning considerations.

BC recently had a server running Oracle 9.2.0.8. Two instances on the machine, each with the EXACT same parameters except for DB_BLOCK_SIZE, all files located on the EXACT same mounts.

When the 16k instance runs an 850,000 row update (no where clause), it finishes in 45 minutes.  When the 4k instance runs an 850,000 row update (no where clause), it finishes in 2.2 minutes. The change in blocksize caused the job to run TWENTY TIMES FASTER.

A client has an update that they must run (unfortunately) which updates ~830,000 rows, setting one column equal to the other (two column table). On their development environment this was taking roughly twenty seconds to perform. However, on their soon-to-be production environment it was taking roughly 45 minutes.

Explain plans were checked, trace files examined, and not much popped up except that the production machine was attempting larger I/Os during the update and was consequently taking much longer. Comparing the initialization parameters between production and development showed the exact same parameters, except that the upcoming production box was using a 16k block size and development was using a 4k block size.

The final result: When the update was run against the 16k blocksize DB, it took 45 minutes. Against the 4k blocksize DB on the same box with the same parameters and the same FS mounts, it took 2 minutes 20 seconds.

I even took it a step further to see if we could squeeze any more performance out of it. Setting FILESYSTEMIO_OPTIONS=SETALL (instead of none) I was able to get the update down to 1.5 minutes. Turning off DB_BLOCK_CHECKING (not recommended) I was able to get it down to 10 seconds.

By going from a 16k blocksize to a 4k blocksize with all other things being equal, we experienced roughly a twenty times improvement. We shaved off even more time setting FILESYSTEMIO_OPTIONS = SETALL. And then we changed DB_BLOCK_CHECKING, a parameter Oracle documentation says only adds a 1 to 10% overhead depending on concurrency of DML, which made the update 6 times faster alone.

The final result was a 270 times improvement over the original, changing only the db_block_size.  To be fair, I also tried setting the FILESYSTEMIO_OPTIONS and DB_BLOCK_CHECKING the same on the 16k blocksize instance, which resulted in the update taking 30 minutes as opposed to 45. The results were better, but the 4k blocksize database still won by 180 times.

What's more, all queries, both large and small, performed the same or better than in production, and a test insert of 100,000 rows went from 20 seconds on the 16k blocksize to 3 seconds on the 4k.

While this experiment definitely shows that changing only blocksize can have a profound effect, more thorough analysis will help get to the core of why changing blocksizes had such a positive tuning effect.

Oracle Ace Ben Prusinski notes that batch jobs can see a 3x performance improvement when moved to a larger blocksize: 

"My experiences have been that using different block sizes can make a difference.

For a past customer a large financial company, we improved database performance by increasing block size from 8k blocksize to 16k blocksize.

Performance for nightly data loads went down from 22 hours to 6 hours when we increased the database block size."

What are the benefits of using multiple data buffers?

The nature of multiple data buffers changes between releases.  In Oracle 8i, the KEEP and RECYCLE pools were a sub-set of the DEFAULT pool. Starting with Oracle9i, the KEEP and RECYCLE pools are allocated in addition to the db_cache_size.

The first round of benchmarking shows that larger block sizes do consume more CPU, especially in metadata intensive workloads, but overall read speeds are much better.

Operations on one million 16K files in one directory

Blocksize	Create	Read	Delete
4096	480s (244s sys)	17m14s (3m11s sys)	4m31s (2m15s sys)
8192	459s (238s sys)	15m20s (3m8s sys)	4m28s (2m29s sys)
16384	470s (240s sys)	14m47s (3m8s sys)	5m2s (3m9s sys)
32768	521s (270s sys)	14m39s (3m16s sys)	7m7s (4m41s sys)
65536	663s (362s sys)	14m31s (3m27s sys)	11m22s (7m48s sys)

The Oracle 11g Performance Tuning guide notes that the multiple buffer pools are indeed beneficial in I/O reduction, but only under certain circumstances:

With segments that have atypical access patterns, store blocks from those segments in two different buffer pools: the KEEP pool and the RECYCLE pool. A segment's access pattern may be atypical if it is constantly accessed (that is, hot) or infrequently accessed (for example, a large segment accessed by a batch job only once a day).

Multiple buffer pools let you address these differences. You can use a KEEP buffer pool to maintain frequently accessed segments in the buffer cache, and a RECYCLE buffer pool to prevent objects from consuming unnecessary space in the cache.  . .

By allocating objects to appropriate buffer pools, you can:

• Reduce or eliminate I/Os
• Isolate or limit an object to a separate cache"

Over the years, there have been heated discussions about the benefits of using multiple blocksizes, especially in these areas:

- Faster performance? - Some claim up to 3x faster elapsed times with a larger index blocksize.  See these TPC benchmarks that employed multiple blocksizes.  Vendors spend huge sums of money optimizing their systems for TPC benchmarks, so this indicates that they employed multiple blocksizes for a performance reason. 

- Faster updates - In 64-bit database with large data buffers (over 50 gig), some shops claim a benefit from segregating high-impact DML tables into a separate blocksize, assigned to a separate, small buffer.  They claim that this improved DML throughput because there are fewer RAM buffer chains to inspect for dirty blocks.

- Data segregation - Some DBA's use separate 2k data buffers for tablespaces that randomly fetch small rows, thereby maximizing RAM by not reading-in more data than required by the query.

Guy Harrison's bestselling book "Oracle SQL High-Performance Tuning" notes that larger blocksizes can improve the throughout of full-scan operations:

"The size of the Oracle block can have an influence on the efficiency of full-table scans.  Larger block sizes can often improve scan performance."  

Vendor notes on Oracle multiple blocksizes

These are many Oracle TPC benchmarks that thoroughly tested multiple blocksizes vs. one-size fits all.  Because a benchmark is all about maximizing performance, it appears that these world-record Oracle benchmarks chose multiple blocksizes because it provided the fastest performance for their hardware. These benchmarks are fully reproducible, so there performance gains can be proven independently.

This UNISYS Oracle benchmark used multiple blocksizes to achieve optimal performance"

The IBM Oracle Technical Brief titled "Oracle Architecture and Tuning on AIX" (November 2006) notes that careful evaluation is required before implementing multiple blocksizes:

The paper continues with specific examples of differing I/O patterns that are related to the database blocksize:

But what is Oracle's official position on multiple blocksizes.  For Oracle metal-level customers we have the Oracle MOSC system which provides the official position of Oracle's own experts.

MOSC Note: 46757.1 titled "Notes on Choosing an Optimal DB BLOCK SIZE" says that there are some benefits from having larger blocksizes, but only under specific criteria (paraphrased from MOSC):

• 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."

MOSC goes on to say that multiple blocksizes may benefit shops that have "mixed" block size requirements:

In the IOUG 2005 conference proceeding titled "OMBDB: An Innovative Paradigm for Data Warehousing Architectures", Anthony D. Noriega notes evidence that his databases benefited greatly from employing multiple blocksizes and notes that multiple blocksizes are commonly used in large databases with limited RAM resources, in applications such as marketing, advertisement, finance, pharmaceutical, document management, manufacturing, inventory control, and entertainment industry:

Next, let's examine the possible benefits of using multiple blocksizes in large environments.

A simple example of Oracle with multiple blocksizes

Let's consider an OLTP database with these characteristics:

- The vast majority of text rows are small, say 80 bytes.  A 2k blocksize would reduce the waste from reading-in a 8k block, only to fetch 80 bytes.

- The database is 100 gigabytes, but there is only 8 gigabytes of available data buffers.

- The database stores images (BLOB, CLOB) in a separate tablespace, requiring a large blocksize to avoid fragmentation.

- The database is heavily indexed, and index access patterns tend to read large sections of the index.

- The data has a typical usage skew, with some popular rows, and some rows that are rarely accessed.

A typical database has popular blocks and unpopular blocks

In this simple example we see the mixed I/O patterns as described in MOSC.

We know that we need a 16k blocksize to keep our CLOB data from fragmenting, and we do not want the buffer wastage that occurs when we read-in a 32k block just to access an 80 byte row. 

Below, we see a simple illustration to show that small blocksizes provide a lower-level of data granularity, allowing more frequently-referenced rows to remain in the RAM buffer:

Smaller blocks reduce wastage when reading small rows

As we see from this illustration, the smaller blocksizes allow for more hot rows to remain cached within the data buffers.  Next lets see how the relative size of the database size to the data buffer size influences the choice to try-out multiple blocksizes.

Size Matters!

Also note that the overall percentage of cache is an influence in the choice to deploy multiple blocksizes.  In a large database with a tiny percentage of RAM, the DBA may want to maximize the efficiency of the buffers:

Large databases with limited RAM and varying I/O patterns may find a greater benefit to multiple blocksizes

Of course, as RAM becomes cheaper and more shops move-away from spinning platter disks into solid-state Oracle, this will become a moot issue.  For details, see the book "Oracle disk I/O tuning with SSD".

Let's explore this important Oracle performance issue, and see if we can find consensus.

Real world experiences with large index blocksizes

There are numerous reports by end-users who have enjoyed a benefit from larger blocksizes for indexes, but many experts disagree on the benefits.  While some fervently says that blocksize will not effect performance, several credible sources note a positive experience with large blocksizes

- Bert Scalzo notes that the blocksize made a huge performance difference in his benchmark tests:

"The larger block size yielded improvements to the load (almost 32%) with no detriment to the TPS.

In fact, the TPS improved over 2%. But notice that we have reached a critical juncture in block size increases.

The load time improvement decreased quite significantly--138% to 32%--and the TPS gain was nearly three times as much as that of the 4K block size."

- M. J. Schwenger notes that his shop had experienced good results with larger blocksizes for their indexes:

- Balkrishan Mittal noted a performance problem when he switched his indexes to a smaller blocksize:

- David Aldridge notes a test where is noted a 6% reduction with larger index blocksizes, a significant difference, especially to larger shops :

- Chris Foot (Oracle ACE and senior database architect) notes that employing multiple block sizes can help maximize I/O performance:  "

- Santosh Kumar notes a significant response time reduction after a move to a larger blocksize:  "where a user has given example of the response time of the same query from two databases, one was having standard block size of 8k and other one was having 16k:

- Steve Taylor, the Technical Services Manager for Eagle Investment Systems Corporation notes a significant I/O reduction:

Are multiple blocksizes right for my database?

Every database is unique, and all prudent DBA's will test changes to standard blocksizes with a representative test using a real-world workload. 

Remember, multiple blocksizes are not for everyone, and you need to carefully check to see if your database will benefit by performing a statistically valid workload test in your QA instance.

Most shops will use tools such as the Oracle benchmark factory or the Oracle 10g SQL tuning Sets (STS) to capture and replay a representative workload.  See the book "Database Benchmarking" for complete details on best practices for SGA changes.  Also, note the new 11g SQL performance analyzer (SPA) which can be used to test changes to any init.ora settings.

My related multiple blocksize notes

See my related notes on Oracle multiple blocksizes: