Oracle Training Oracle Support Development Oracle Apps

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 

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?

 



 

 
 
��  
 
 
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