Execute to Parse及Soft Pars

The ratios quoted can be derived from the above as follows:

Execute to Parse %:  100 * (Executes - Parses) / ExecutesSoft Parse %:        100 * (Parses - Hard Parses) / Parses

So why not look at Parses, Hard Parses, and Executes instead of confusing yourself with ratios:

参看链接：

https://jonathanlewis.wordpress.com/2011/11/13/irrational-ratios/

另外Tom把parse分为几类：

with session cached cursors there are now what I will call 4 types of parses:hard parse (library cache miss, do the entire thing)soft parse (library cache hit, do less work then hard)softer soft parse (session cursor cache hit, less work then soft parse)NO PARSE (well written program that reused cursors -- NO work performed)A "softer" soft parse still incurrs (avoidable) overhead.  I would say the support note is a little aggresive in its math.  Maybe something like:soft parse = parse count(total) - parse count(hard) - 0.5 * session cursor cache hits(0.5 is arbitrary, illustrative)....Consider -- we'll do 10,000 softer soft parses vs no parse and see that the softer soft parse still lots of extra (avoidable) work:ops$[e-mail]> create table t ( x int primary key ) organization index;Table created.ops$[e-mail]> insert into t values ( 1 );1 row created.ops$[e-mail]> commit;Commit complete.ops$[e-mail]>ops$[e-mail]> alter session set session_cached_cursors = 100;Session altered.ops$[e-mail]>ops$[e-mail]> declare  2      l_start number;  3      l_run1  number;  4      l_run2  number;  5  6      l_cnt   number;  7  begin  8      insert into run_stats select 'before', stats.* from stats;  9 10      l_start := dbms_utility.get_time; 11      for i in 1 .. 10000 12      loop 13          execute immediate 'select count(*) from t' into l_cnt; 14      end loop; 15      l_run1 := (dbms_utility.get_time-l_start); 16      dbms_output.put_line( l_run1 || ' hsecs' ); 17 18      insert into run_stats select 'after 1', stats.* from stats; 19      l_start := dbms_utility.get_time; 20 21      for i in 1 .. 10000 22      loop 23          select count(*) into l_cnt from t; 24      end loop; 25      l_run2 := (dbms_utility.get_time-l_start); 26      dbms_output.put_line( l_run2 || ' hsecs' ); 27      dbms_output.put_line 28      ( 'run 1 ran in ' || round(l_run1/l_run2*100,2) || '% of the time' ); 29 30      insert into run_stats select 'after 2', stats.* from stats; 31  end; 32  /279 hsecs206 hsecsrun 1 ran in 135.44% of the timePL/SQL procedure successfully completed.ops$[e-mail]> select a.name, b.value-a.value run1, c.value-b.value run2,  2         ( (c.value-b.value)-(b.value-a.value)) diff  3    from run_stats a, run_stats b, run_stats c  4   where a.name = b.name  5     and b.name = c.name  6     and a.runid = 'before'  7     and b.runid = 'after 1'  8     and c.runid = 'after 2'  9     and (c.value-a.value) > 0 10     and (c.value-b.value) <> (b.value-a.value) 11   order by abs( (c.value-b.value)-(b.value-a.value)) 12  /NAME                                 RUN1       RUN2       DIFF------------------------------ ---------- ---------- ----------...STAT...session cursor cache hi      10000          3      -9997tsSTAT...opened cursors cumulati      10005          6      -9999veSTAT...parse count (total)          10005          6      -9999LATCH.shared pool                   10117        108     -1000934 rows selected.so that shows parse count does get incremented for each cursor cache hit -- and that there is some latching going on....It is better then a soft parse for sure - but not as good as NO parse.