Execute to Parse%是AWR报告中Instance Efficiency Percentages部分中重要的一个性能指标，反应了数据库SQL解析和执行的比率。这个比率值同时也涉及到了与cursor相关的参数以及硬解析，软解析，软软解析等。本文是围绕这个比率进行展开及描述。
一、什么是Execute to Parse%
--下面是来自AWR报告的相关信息
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.60 Redo NoWait %: 100.00
Buffer Hit %: 99.99 In-memory Sort %: 100.00
Library Hit %: 99.96 Soft Parse %: 99.98
Execute to Parse %: -8.46 Latch Hit %: 98.39
Parse CPU to Parse Elapsd %: 90.79 % Non-Parse CPU: 97.35
Statistic Total per Second per Trans
-------------------------------- ----------- -------------- -------------
index crx upgrade (prefetch) 0 0.0 0.0
opened cursors cumulative 2,296,221 91.0 780.5
parse count (describe) 3 0.0 0.0
parse count (failures) 5 0.0 0.0
parse count (hard) 512 0.0 0.2
parse count (total) 2,272,639 90.1 772.5
parse time cpu 16,934 0.7 5.8
parse time elapsed 18,651 0.7 6.3
Tom大师关于Execute to Parse的描述：
the only way to influence that number is to either change
a) the number of times you parse. b) the number of times you execute.
The formula used:
Execute to Parse %: dscr , round(100*(1-:prse/:exe),2) pctval
Execute to Parse %：
一个语句执行和分析了多少次的度量。计算公式为：Execute to Parse =100 * (1 - Parses/Executions)。如果系统Parses > Executions，就可能出现该比率小于 0 的情况。该值<0 通常说明 shared pool 设置或者语 句效率存在问题，造成反复解析，reparse 可能较严重,或者是可能同 snapshot 有关，通常说明数据库性能存在一定问题。
If the number of parse calls is near the number of execute calls, then this ratio drifts towards zero (as yours is). As the number of execute calls increases (while holding parse calls constant), this number drifts towards 100%. That means you have parsed a statement ONCE and executed it MANY TIMES (that is good, that is best)
cursor sharing = similar MIGHT change a hard parse into a soft parse (take a very very very bad thing and make it simply very very bad). cursor sharing similar CANNOT change the number of times parse is invoked however.
There is precisely, exactly and only ONE person that can do that. That is the application developer.
When they say “parse this”, we parse it - it matters not what the value of cursor sharing is (if you have a hard parse problem, if your soft parse percent is below 99%, you need to have the coders FIX that, you have (in addition to performance, memory, scalability issues) a HUGE security risk if you are not using binds).
The developers must cache open cursors they know will be used over and over. The easiest way (to me) to accomplish this is to move all SQL into plsql, plsql automagically caches statements for us, it is the most efficient method to interface with the database.
Alternatively, they can program it, or they can see if the API they are using can do it magically for them (search for jdbc statement caching on google for example if you are using jdbc)
But it will have to be done in the application, there is nothing we can do outside of the application to influence how often it parses.
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1594740500346667363