Oracle db_block_checksum performance bug

Monday Mar 10, 2008

We recently ran across a pretty strange performance bug with the checksum function of Oracle. This bug (6814520) causes excessive CPU to be used for the checksum routine. The checksum feature of Oracle is enabled by the db_block_checksum=TRUE parameter in Oracle. With the release of Oracle 10gR2, "TRUE" is the default setting. The order of magnitude of CPU overhead depends on the type of Solaris SPARC architecture.

Chip     %overhead
----     ---------
SPARC64       250%  
USIV           45%  
------------------
w/patch         8% 

Oracle released a patch via metalink to address this situation. This patch is for 10.2.0.3 installations. The fix will be included in: 11.1.0.7, 10.2.0.4, and 10.2.0.5.

If you are unsure whether or not you are hitting this bug, you can easily alter this parameter on the fly:

SQL> alter system set db_block_checksum=FALSE

Warning, this will disable the checksum feature and blocks written when this is turned to FALSE will not contain checksum information.

Regardless of whether you are currently hitting the bug or not, the recommendation is:

INSTALL "6814520" WHEN USING ORACLE 10.2.0.3 !!!

Ensuring directIO with Oracle 10.2.0.3 on Solaris UFS filesystems

Thursday Feb 14, 2008

I usually really dislike blog entries that have nothing to say other than repackage bug descriptions and offer them up as knowledge, but in this case I have made an exception since the full impact of the bug is not fully described.

There is a fairly nasty Oracle bug with 10.2.0.3 that prevents the use of DirectIO with Solaris. The metalink note "406472.1" describes the failure modes but fails to mention the performance impact if you use "filesystemio_options=setall" and fail to have the mandatory patch "5752399" in place.

This was particularly troubling to me since we have been recommending for years the use of the "setall" to ensure all the proper filesystem options are set for optimal performance. I just finished working a customer situation where this patch was not installed and their critical batch run-times were nearly 4x as large... Not a pretty situation.... OK, So bottom line:

MAKE SURE YOU INSTALL "5752399" WHEN USING ORACLE 10.2.0.3 !!!