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.