Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle tuning Guru achieves 20x performance improvement

June 17, 2008

Steve Karam, the Oracle wunderkind (the world's youngest Oracle ACE and Oracle Certified Master), has published an astute case study describing how he tuned a batch update down from 45 minutes to only 10 seconds! 

>> Read "Does Size Matter? by Steve Karam, OCM, Oracle ACE

In order to have a statistically valid test, Karam used a the real-world workload, using identical server, disk and instance settings.  Karam notes how moving a high-DML table to a smaller blocksize resulted in a 20x throughput improvement: 

"By going from a 16k blocksize to a 4k blocksize with all other things being equal, we experienced roughly a twenty times improvement."

Steve is now investigating further areas in the environment that could benefit from block optimization, targeting frequently used and highly concurrent segments as candidates for a smaller blocksize.  Let's take a closer look at the use of multiple blocksizes to reduce waste and improve performance throughput.


Tuning with multiple blocksizes is an advanced DBA tuning technique that requires a complete understanding of the I/O stack.  In general, multiple blocksizes are used in large mission critical databases with thousands of users and hundreds of transactions per second, and it's a technique that requires careful testing and validation. 

Multiple blocksizes is also complex because the DBA must automate the management of multiple data buffer caches.  If you are considering implementing multiple blocksizes, you will need to carefully testing and validate the benefits using real-world workloads under representative production conditions.  Savvy Oracle professionals will use a recognized database benchmarking simulation technique (See the book Oracle Benchmarking for details) or use the new Oracle 11g SQL Performance Analyzer (SPA), Oracle's official testing tool for verifying the impact of system-wide changes. 

For complete details on I/O tuning for Oracle, see my tips on using multiple blocksizes for details are the book "Oracle Tuning: The Definitive Reference" for a complete methodology for managing your I/O subsystem in Oracle.

All about Oracle blocksizes

While not every shop is going to experience a 20x response time improvement by using non-standard blocksizes, your operating system vendor and Oracle publish tips for setting the correct blocksize

Any experienced DBA will tell you that tuning the external I/O sub-system can have a profound impact of performance, and this is done in two places, the external environment, and the device media control layer (DMCL), the point at which Oracle maps his logical structures to the physical hardware.

Optimization of I/O requires that you check all areas of the external environment:

- OS Kernel - Tweaking the OS kernel configuration (setting direct I/O, using specialized I/O drivers)

- Disks - Optimizing disks (optimal RAID, load balancing of disks and  controllers, monitor disk enqueues (e.g. EMC Symmetrics), deploying SSD.

- Network - Tuning the interconnect in RAC environment and packet behavior in client server connections

We also have the DMCL (Device-Media Control Layer), the point where the logical Oracle structures (tablespaces) map to their physical data files (by optimizing db_block_size, db_file_multiblock_read_countdbms_stats.gather_system_stats, filesystemio_options, disk_asynch_io, db_writer_processes, and other Oracle filesystem I/O options.  It is these critical settings that determine the "actual" read size for the Oracle database.  Remember, the db_file_multiblock_read_count also has a huge effect of I/O, often as much as the blocksize itself.

But you don't always see a performance improvement by using a smaller blocksize, you can see the opposite as well.  Oracle consultant Ben Prusinski notes that batch jobs can see a 3x performance improvement when moved to a larger blocksize: 

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

In general, different blocksizes can improve performance in a variety of ways, but every system is different, and only a real-world workload benchmark can show what benefits you may experience.  See my 2008 survey on multiple blocksizes for details.

  • 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 as long as the I/O does not exceed the capabilities of the disk subsystem.

Oracle also notes that systems with a "hybrid" of I/O access patterns may benefit from implementing multiple blocksizes.  For details, see my Oracle Multiple blocksize tips.


Related block size notes

See my related notes on tuning the Oracle I/O subsystem:




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational