After going through AWR reports (Instance Efficiency Percentages) I observed they have low Execute to Parse % but high Soft Parse %.
Please share if you had faced such issue and any suggestions to solve this
The first problem with this question is that ratios hide information.
The second problem is that we don’t know what values the OP considers to be “low” or “high” in this context.
The third is that there’s no reason why any such juxtaposition of ratios should implicitly mean there’s a problem.
The silly thing about the question is that the ratios are derived values, and actually share a common component, so it would be much easier to look at the absolute figures to see if they mean anything. Fortunately someone asked for more information and we got the following extract from an AWR. This was from a typical report (one hour snapshot interval) on a machine with 8 CPUs.
Load Profile Per Second Per Transaction --------------- --------------- Redo size: 11,685.79 3,660.98 Logical reads: 71,445.74 22,382.86 Block changes: 70.89 22.21 Physical reads: 58.63 18.37 Physical writes: 2.80 0.88 User calls: 652.93 204.55 Parses: 48.39 15.16 Hard parses: 0.33 0.10 Sorts: 6.90 2.16 Logons: 0.23 0.07 Executes: 52.71 16.51 Transactions: 3.19 % Blocks changed per Read: 0.10 Recursive Call %: 30.48 Rollback per transaction %: 2.57 Rows per Sort: 29.66 Instance Efficiency Percentages (Target 100%) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Buffer Nowait %: 100.00 Redo NoWait %: 100.00 Buffer Hit %: 99.92 In-memory Sort %: 100.00 Library Hit %: 98.47 Soft Parse %: 99.32 Execute to Parse %: 8.19 Latch Hit %: 99.63 Parse CPU to Parse Elapsd %: 89.90 % Non-Parse CPU: 99.62
(Forgetting the ratios for a moment – what’s the strangest thing about the Load Profile ? )
The ratios quoted can be derived from the above as follows:
Execute to Parse %: 100 * (Executes - Parses) / Executes Soft Parse %: 100 * (Parses - Hard Parses) / Parses
So why not look at Parses, Hard Parses, and Executes instead of confusing yourself with ratios:
Better still, you could just about squeeze some information from the % Non-Parse CPU figure namely: 99.62% of your CPU is spent on doing things other than parsing, so IF your largest time component is CPU, and IF you eliminated all the parsing costs then you would make virtually no difference to the performance. (And the first IF is why you wouldn’t really bother with the ratio, you should be looking elsewhere for CPU time spent.)
Coming back to the Parses – we see about 48 per second (which doesn’t sound a lot, really, for a system that’s been engineered with 8 CPUs) and 0.33 hard parses per second (i.e. virtually none). If we look at the Executes it’s about 53 per second (again, not a lot, but slightly more than Parses). So where’s the problem ? There isn’t one – not in the parses and executes, at least.
Let’s go back to the strangest thing in the Load Profile – how do you do 48 Parses, 53 Executes but manage to total 653 User Calls per second ? (… to be continued)