Thanks for the question, Doug.

Asked: December 07, 2001 - 8:57 am UTC

Last updated: November 19, 2016 - 3:53 am UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Tom - this is from running a statspack report. It captures activity over a 3 day period.

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.58 In-memory Sort %: 99.93
Library Hit %: 99.31 Soft Parse %: 90.44
Execute to Parse %: 93.29 Latch Hit %: 99.97
Parse CPU to Parse Elapsd %: 13.69 % Non-Parse CPU: 100.00

My question is - what explains the Parse CPU to Parse Elapsd ratio being so abysmal compared to the rest? Every hit I have on the Oracle support site and related subjects on your site and even some other sources indicate that it ususally goes hand in hand with the Execute to Parse ratio, which in this case is 93% (not fantastic, but not horrible - certainly not 13%)..

Also the hits indicate to look for bind variable problems. I found a couple of statements with bind variable problems that won't be fixed until the next update.

But.. I'm at a loss as to why that ratio is SO BAD.. 13% while the execute to parse is in the low 90's.. Can you point me in the right direction? What am I looking at here?

Thanks.

and Tom said...

Execute to parse and Parse CPU to Parse Elapsed cannot be compared in any way shape or form.

EXECUTE to PARSE (1- parse/execute)

This is a measure of how many times a statement was executed as opposed to being parsed. In a system where we parse, and then execute the statement, and never execute it again in the same session, this ratio will be 0 (bad). On a system where we parse a statment once and then execute it 100 times, we'll get a 99% ratio. The closer we get to 100, the more efficient we are. 93.29 is about 1 parse for every 15 executes (well done) on average.

The next number to look at is the Parse CPU to Parse Elapsd. In this case for every CPU second spent parsing we spent about 7.3 seconds wall clock time parsing. This means we spent some time waiting for a resource ? if the ratio was 100 percent, it would imply CPU time was equal to elapsed time and we processed without any waits. You have some bad waits parsing -- which are typically due to contention on the shared pool but can be caused by other things as well (eg: trying to compile or alter some objects in a busy system can really jam things up and through some abnormally long parse times in there).

I would start with a sql_trace/tkprof (with timed stats on) to see whats going on. Bottom line however -- these too numbers are not related really (although -- if your execute to parse was to drop seriously that would introduce more contention -- and perhaps make the problem worse. A good execute to parse does not imply a good parse cpu to parse elapsed however)....

Rating

(6 ratings)

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

Comments

Excellent

Doug, December 09, 2001 - 5:08 pm UTC

Very interesting. I will have to track down what is holding up the CPU now.

From % to seconds

Josat, September 11, 2002 - 2:28 pm UTC

Tom

In answering his questions, you convert couple of %stat into number of executions and number of seconds, for example,
"93.29 is about 1
parse for every 15 executes (well done) on average" and "for
every CPU second spent parsing we spent about 7.3 seconds wall clock time"

How did you translate the % into these numbers?

Thanks

September 11, 2002 - 3:35 pm UTC

  1* select (1-1/15)*100 from dual
ops$tkyte@ORA920.US.ORACLE.COM> /

(1-1/15)*100
------------
  93.333333


Math?  If someone says the pct of Executes To Parses was 93.29%, you just setup 

(1 - execute/parse) = 93.29% => 1-.9329 = execute/parse => .0671 = execute/parse which means execute to parse is 671 parses per 10,000 executes or about 1/15th

Same sort of logic with CPU

Low Parse CPU to Parse Elapsed

VLS, September 09, 2006 - 9:33 am UTC

Hi Tom,

A very low parse cpu to parse elapsed is waits on cpu second for parsing. Does it implies waits due to other latch contention as well ? I mean, if I have a contention for CBC latch, can other sessions wait on library cache latch and thus contribute to low parse cpu to parse elapsed ?

Also, if some of my application queries are not making use of bind variable, will it impact my "execute to parse" ratio or "parse cpu to parse elapsed" or both.

Regards
VLS

September 09, 2006 - 12:24 pm UTC

it implies nothing really.

but cross latch contention like that - no, not really at all. if you have contention for the cache buffers chains, that is separate and distinct from the library cache - one would not really "block" the other.

and beware ratios.

1 second cpu
2 seconds elapsed
50% ratio

but who cares.

Parse CPU to Parse Elapsed is zero

Chaitanya P V, March 11, 2011 - 7:49 am UTC

This is in continuation to this note: "if the ratio was 100 percent, it would imply CPU time was equal to elapsed time and we processed without any waits." However, my AWR report shows 0% for Parse CPU to Parse Elapsed. Does that mean CPU had to indefinitely wait and never processed? Here is a glimpse of it:

Buffer Nowait %: 99.98 Redo NoWait %: 100.00
Buffer Hit %: 99.91 In-memory Sort %: 99.88
Library Hit %: 99.89 Soft Parse %: 99.23
Execute to Parse %: 75.94 Latch Hit %: 99.94
Parse CPU to Parse Elapsd %: 0.00 % Non-Parse CPU: 93.89

Please advise.

March 11, 2011 - 9:53 am UTC

look at the underlying numbers, what is your parse time cpu, what is your parse time elapsed. Those are the numbers that make this ratio. They are in the report too. Look at them and it will explain itself.

It would mean you spent a lot of time waiting to parse.

Parse CPU to Parse Elapsed % is quite low

Kshitij, December 02, 2012 - 4:41 am UTC

I am executing a pl/sql in 256 parallel sessions, on 11G r2 DB (RAC 2 nodes), on a 42core IBM P7 Machine.
PL/sql function opens a cursor on a huge table with around 20M rows and does further processing.
Work-load is equally divided into 256 parallel sessions. 256 parallel sessions are opened by a middle-ware application and each session processes data based on an identifier (there are 256 distinct identifier values which divides data equally across sessions).

From AWR : 
<code>
Instance Efficiency Percentages (Target 100%) 

Buffer Nowait %:     99.97   Redo NoWait %: 99.99 
Buffer Hit %:      99.24   In-memory Sort %: 100.00 
Library Hit %:      100.03   Soft Parse %: 98.79 
Execute to Parse %:    99.93   Latch Hit %: 99.47 
Parse CPU to Parse Elapsd %:  10.07   % Non-Parse CPU: 99.99

Top 5 Timed Foreground Events

Event Waits     Time(s)  Avg wait (ms) % DB time Wait Class 
DB CPU        185,871        13.43   
db file sequential read  14,109,453  15,426 1   1.11  User I/O 
gc current block 2-way   10,600,361  9,269 1   0.67  Cluster 
gc cr block busy    1,724,504  5,776 3   0.42  Cluster 
gc cr block 2-way    4,345,152  3,270 1   0.24  Cluster

Parse CPU to Parse Elapsed % is quite low, which indicate some bottleneck is there related to parsing.
could you please give me some heads-up about where to and what to look for to resolve?
Please let me know if any extra information is required.
</code>

December 03, 2012 - 8:45 am UTC

how many parses do you do? If this is all plsql, you are probably hardly parsing at all - in which case - percentages are meaningless.

Your non-parse cpu is 99.99% - meaning not much time is spent parsing.

plsql is excellent for avoiding parsing, you are probably not parsing much at all. so - basically - the % could be 0.000001% and you still wouldn't care.

look elsewhere for your performance tuning thoughts, I don't think parsing is it.

Very High Parse CPU to Parse Elapsd %

Charles, November 16, 2016 - 9:21 am UTC

What does the following mean?

Parse CPU to Parse Elapsd %: 133,130,230.65%

Also look at the DB time vs Elapsed Time:

Elapsed: 60.12 (mins)
DB Time: 293,035,498.94 (mins)

Header:

Platform CPUs Cores
AIX-Based Systems (64-bit) 80 20

Load Profile:

Per Second PerTrans Per Exec Per Call

DB Time(s):4,874,038.8 130,201.4 4,546.35 2,057.80
DB CPU(s): 4,874,019.1 130,200.8 4,546.33 2,057.79

November 19, 2016 - 3:53 am UTC

That looks like a "bug". I use quotes because with AIX, lpars, hyperthreading etc etc, the definition of "cpu" is more and more obtuse.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent

From % to seconds

Low Parse CPU to Parse Elapsed

Parse CPU to Parse Elapsed is zero

Parse CPU to Parse Elapsed % is quite low

Very High Parse CPU to Parse Elapsd %

More to Explore

Live SQL

Dev Gym

Parse CPU to Parse Elapsd has horrible ratio.. but others not too bad?

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Excellent

From % to seconds

Low Parse CPU to Parse Elapsed

Parse CPU to Parse Elapsed is zero

Parse CPU to Parse Elapsed % is quite low

Very High Parse CPU to Parse Elapsd %

More to Explore

Live SQL

Dev Gym