Parse CPU to Parse Elapsd – What is Wrong with this Quote?

September 1, 2011

I located another interesting statement in the “Oracle Database 11g Performance Tuning Recipes” book, so I thought that it was time for another blog article that takes a look at a statement from the book.  Chapter 4 of the book is significantly better written (much more accurate) than chapter 3.

To set the stage, let’s take a look at the “Instance Efficiency Percentages” section of two AWR reports that I have collected over the years:

Example #1:

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   27.89    In-memory Sort %:  100.00
            Library Hit   %:   91.32        Soft Parse %:   76.52
         Execute to Parse %:   30.71         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   10.26     % Non-Parse CPU:   92.99 

Example #2:

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
               Buffer Hit %:   99.89    In-memory Sort %:  100.00
              Library Hit %:   97.45        Soft Parse %:   97.79
         Execute to Parse %:   97.30         Latch Hit %:   95.31
Parse CPU to Parse Elapsd %:   79.21       Non-Parse CPU:   99.10  

For obvious reasons, it is not a desirable outcome to see that a large percentage of the CPU time consumed by a database instance is used for parsing.  Ideally, most of the CPU time would be consumed actually executing the SQL statements submitted by user sessions.

Now let’s take a look at the quote from Recipe 4-10 on page 133:

“The Parse CPU to Parse Elapsd metric shows how much time the CPU is spending parsing SQL statements. The lower this metric is, the better. In the following example [not quoted], it is about 2%, which is very low. If this metric ever gets to 5%, it may mean investigation is warranted to determine why the CPU is spending this much time simply parsing SQL statements.”

What, if anything, is wrong with the above quote from the book?

The point of blog articles like this one is not to insult authors who have spent thousands of hours carefully constructing an accurate and helpful book, but instead to suggest that readers investigate when something stated does not exactly match what one believes to be true.  It could be that the author “took a long walk down a short pier”, or that the author is revealing accurate information which simply cannot be found through other resources (and may in the process be directly contradicting information sources you have used in the past).  If you do not investigate in such cases, you may lose an important opportunity to learn something that could prove to be extremely valuable.