You Asked

Hello Gentlemen,

I am trying to understand why Oracle decided to put so many entries into the "AWR Instance Efficiency" section to help the DBA identify if the shared pool is too small or if hard or soft parsing is occurring. I am sure there is a good reason, but i don't get it.

Here are the entries that I am referring to:

AWR Instance Efficiency "Library Hit %", "Soft Parse %", "Execute to Parse %", "Parse CPU to Parse Elapsd %" and "%Non-Parse CPU:"

If the "Library Hit %" is low, then I know that either the shared pool is too small or cursors aren't been reused.
If the "Soft parse %" is low, then I know that most parsing was hard parsing.
How do we tell the amount of statement that were neither soft nor hard parsing?

I am not sure what "Execute to Parse %", "Parse CPU to Parse Elapsd %" and "%Non-Parse CPU" tells us if the these two statistics are already telling us all we need to know about whether the shared pool is too small and whether hard or soft parsing is occurring. Please tell us what the DBA can gather from these three statistics:

"Execute to Parse %"
"Parse CPU to Parse Elapsd %"
"%Non-Parse CPU"

Please clear up this confusion.

Thanks,

John

"A soft parse is any parse that is not a hard parse. " http://docs.oracle.com/cd/E11882_01/server.112/e40540/sqllangu.htm#CNCPT216

and Connor said...

"Parse CPU to Parse Elapsd %"
- Whilst obviously we want to keep parsing as low as practical, we must always be doing *some* parsing (because every SQL statement must be parse at least once). Now, parsing is a CPU-heavy activity. So almost all of the time spent parsing should be on CPU. So this percentage we'd like to see near 100. If its not, then it meant that when we did have to parse, something stopped us from getting access to the CPU, ie, its an indicator of possible contention.

"Execute to Parse %"
A measure of how many times we executed a statement compared to parsing it. The relevance depends on what the database is for. For high volume transactions, we want low parse, high execute, so the percentage should near 100. For a data warehouse, we dont really care so much.

"%Non-Parse CPU"
As the name suggests, how much CPU did we burn that was *not* parsing. If you think about (especially for OLTP) parsing being considered as "overhead" (ie, it doesn't really get any work done for us...it just enables 'real' work to be done, ie, our SQL), then the non-parse CPU is the percentage of CPU we used that actually gave us real business value.

Hope this helps.

Rating

(4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, April 05, 2016 - 2:07 am UTC

thanks, Connor. I undestand what you have said. However, I never would have guessed on how to read "Parse CPU to Parse Elapsd %" and "Parse CPU to Parse Elapsd". I don't recall seeing statistics being represented that way. Let me add two examples to see if I get the formula.

We want these values to be close to 100%.

Example 1, if 99% of the time was spent on Parse CPU out of the 100% of Parse Elapsed, then the value is 99/100*100%= 99%. Okay, that one works out.

Example 2, if a SQL statement is executed 100 times but only parsed 10 times, then the value for "Execute to Parse" = 100/10*100%=1000%. Is that possible to get a value of 1000%?

why couldn't Oracle just broken those into readable statistics like:

time spent executing
time spent parsing
time spent on Parse CPU
time spent on Parse Elapsed

that would have made more sense to us than these "X to Y" and "Y to Z" statistics.

Thanks,

John

April 05, 2016 - 2:43 am UTC

execute to parse is calculated as = 1 - parse / execute

For example, on my just started db on the laptop, I run an AWR report and get:

Key Instance Activity Stats

execute:             7,072
parse count (total): 6,031

and hence at the top of the report I get:

Execute to Parse %: 14.72

Hope this helps.

John Cantu, April 05, 2016 - 3:12 pm UTC

Hi Connor,

thanks for providing the formula for "execute to parse %". That helped me to understand.

Let's put this into practice. In a database, I do have a value of .80% for "execute to parse %'. This is a terrible percentage, but in the case, it doesn't have a significant impact based on the time-model showing it using only .1 % of DB Time. However, if I did want to tune it, I would have to either move over to PL/SQL or I can increase "session_cached_cursors" from the default value, right?

I see that only about 10% of parses result in a session cursor cache hit.

Ex:
parse count total 371812
session cursor cache hits 31688

Thanks,

John

April 06, 2016 - 12:02 am UTC

Exactly. For me, anything that is a percentage is always just additive information rather than definitive information.

eg, You *drive* 100km from A to B and get red lights 50% of the time. I *walk* the same journet and get red lights 50% of the time.

For both of us, the red light percentage is 50%, but that tells me nothing about who got there faster.

DB Time, Waits and (most commonly) bad SQL....that's where I always focus.

"Parse CPU to Parse Elapsd %" Follow-up

John Cantu, April 06, 2016 - 1:14 pm UTC

Hi Connor,

Regarding the "Parse CPU to Parse Elapsd %" -

On one of the databases, I see a low value of 16.30%. This looks bad. However, if I compare that to "% Non-Parse CPU", that low value become insignificant, too. For "% Non-Parse CPU" I have a value of 99.98! That is great! Hence, only .02 % of CPU is spent parsing so it really doesn't matter what "% Non-Parse is" right?

Also, will you please provide the formula for "Parse CPU to Parse Elapsd %"?

I figured it would be: (1 - (100-% Non-Parse CPU) / "parse time elapsed % of DB Time)) * 100, but that doesn't add up to 16.30%

(1 - (.02/.1)) * 100 = 80%

Thanks,

John

April 07, 2016 - 1:38 am UTC

Down in "Instance Activity Stats" you will see:

parse time cpu
parse time elapsed

Those two figures provide the percentage.

Note - some OS's can capture cpu data versus elapsed data at *different* granularity, so its not uncommon to see CPU being *more* that elapsed, because one was (say) captured in centiseconds, the other in milliseconds etc.

So you might see percentages greater than 100

A reader, April 09, 2016 - 4:04 pm UTC

thanks, Connor.

the formula does add up:

parse time cpu / parse time elapsed = parse CPU to Parse Elapsd %

It is odd that in the case of "execute to parse %", the formula was different, "1 - parse / execute".

Thanks,

John

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.

Breadcrumb