What is Execute to Parse ratio OR Execute to Parse %?
Every query needs to be parsed before it gets executed. If some queries are quite frequently used, a good design will be to reuse the parsed query. That means it will not parse the same query again. In an ideal world, execution should be more than the parse. More parsing requires more CPU. That’s why the concept of query reuse came into play.
When execute is more than parse, the figure will be positive. The ratio goes down if there are more parses than the executes. Some of the queries are parsed, but less or never executed leads the ratio to negative.
Hence, the ‘Execute to Parse Ratio’ indicates the frequency with which parsed SQL statements are utilized again without the need for re-parsing.
How do you interpret the ‘Execute to Parse Ratio’?
In the ideal scenario, the ‘Execute to Parse’ ratio should be very high. The ratio will move higher as the number of executes goes up, while the number of parses either goes down or remains the same. The ratio will be close to zero if the number of executes and parses are almost equal. The ratio will be negative if executes are lower but the parses are higher.
| Execute to Parse % | Meaning |
|---|---|
| 100% | *Best |
| 99.9% to 1 | Drift from Good to OK (need thorough investigation) |
| 0 to Negative | *Worst (Problematic) |
Interpreting the ratios in this section can be more complex than it appears. While high values for the ratios are generally good, indicating high efficiency, such values can be misleading as the system may be doing something efficiently that it would be better off not doing at all. Similarly, low values aren’t always bad. For example, a low In-memory Sort ratio, indicating a low percentage of sorts performed in memory, would not necessarily be a cause for concern in a decision-support system (DSS) environment, where user response time is less critical than in an online transaction processing (OLTP) environment.
When evaluating the instance efficiency percentages, you need to consider your application’s characteristics, such as whether it is query-intensive or update-intensive, whether it involves lots of sorting, and so on.
Formula
Execute to Parse % = round(100*(1-(No. of Parse/No. of Execute),2)
As per Figure 02, the average ‘Execute to Parse %’ is 61.82 showing that 3818 SQL statements are parsed and executed 10000 times.
Execute To Parse = 100 X (1-(3818/10000))
= 100 X (1-0.3818)
= 100 X 0.6162
Output: 61.62
What are the possible reasons for the low ‘Execute to Parse Ratio’?
The following is the reason for the low ‘Execute to Parse %’:
- Bad bind variable usage
- Insufficient memory
- It will also be co-indicated by a low percentage of memory for multiple SQL execution
- A small size allocated to the shared pool causes queries to age out of the shared pool and need to be reparsed. This is a form of thrashing that significantly degrades performance.
- Also, check if both the ‘buffer hit ratio’ and the ‘execute to parse ratios’ are negative. If the ‘buffer hit ratio’ is negative, the buffer cache is too small, and the data is aged out before it can be used, so it must be retrieved again. This is another form of thrashing that degrades performance immensely.
You may be interested: