Cursor Cache Hit Ratio超過100%

SQL> select m.*, n.NAME

  2        from v$mystat m, v$statname n

  3       where m.STATISTIC# = n.STATISTIC#

  4         and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;

       SID STATISTIC#      VALUE     CON_ID NAME

---------- ---------- ---------- ---------- ------------------------------

         1        643          0          0 session cursor cache hits

         1        644          1          0 session cursor cache count

         1        694          3          0 parse count (total)

   declare

    c number ;

    begin

    for i in 1 .. 10000 loop

    execute immediate 'select count(*) from dba_objects' into c  ;

    end loop ;

    end ;

/

PL/SQL procedure successfully completed.

select m.*, n.NAME

      from v$mystat m, v$statname n

     where m.STATISTIC# = n.STATISTIC#

       and (n.name in ('session cursor cache hits','session cursor cache count','parse count (total)')) ;

       SID STATISTIC#      VALUE     CON_ID NAME

---------- ---------- ---------- ---------- ------------------------------

         1        643      10000          0 session cursor cache hits

         1        644          3          0 session cursor cache count

         1        694          7          0 parse coun

select a.value cache_hits,

       b.value total_parses,

       c.value hard_parses,

       (b.value - c.value) soft_parses,

       round((a.value / (b.value - c.value))*100,2)||'%' ratio

  from v$sysstat a, v$sysstat b, v$sysstat c

where a.name = 'session cursor cache hits'

   and b.name = 'parse count (total)'

   and c.name = 'parse count (hard)';

CACHE_HITS TOTAL_PARSES HARD_PARSES SOFT_PARSES RATIO

---------- ------------ ----------- ----------- -----------------------------------------

4333516791   1444991516    68350276  1376641240 314.79%