Execute to Parse及Soft Pars

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:

参看链接：

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$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int primary key ) organization index;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );
1 row created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> commit;
Commit complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter session set session_cached_cursors = 100;
Session altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> 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 hsecs
206 hsecs
run 1 ran in 135.44% of the time

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> 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      -9997
ts

STAT...opened cursors cumulati      10005          6      -9999
ve

STAT...parse count (total)          10005          6      -9999
LATCH.shared pool                   10117        108     -10009

34 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.