确定SESSION_CACHED_CURSORS的效果，以及查询其与SOFT/HARD PARSE的比例

目标：输出下面这样的结果

   CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
   ----------------- ----------- -----------
               3.08%      90.77%       6.15%

注意：从10.2开始统计 值 "session cursor cache hits"的计算方法有所变化，有可能上述三值加起来超过100%。
10.2中由统计 值 "session cursor cache hits"的增加量来确定SESSION_CACHED_CURSORS的效果

*** for all over instance (Begin) ***
select
  to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,
  to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,
  to_char(100 * hard / calls, '999990.00')||'%' hard_parses
from
  ( select sum(value) calls from v$sysstat
      where name in ('parse count (total)', 'parse count (failures)') ),
  ( select value hard from v$sysstat where name = 'parse count (hard)' ),
  ( select value sess from v$sysstat where name = 'session cursor cache hits' )
/

*** for all over instance (End) ***

*** for current session (Begin) ***

select
to_char(100 * sess / calls, '999999999990.00')||'%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00')||'%' soft_parses,
to_char(100 * hard / calls, '999990.00')||'%' hard_parses
from
( select sum(value) calls from v$mystat m, v$statname s
where m.statistic# = s.statistic# and
name in ('parse count (total)', 'parse count (failures)') ),
( select value hard from v$mystat m, v$statname s
where m.statistic# = s.statistic# and name = 'parse count (hard)' ),
( select value sess from v$mystat m, v$statname s
where m.statistic# = s.statistic# and name = 'session cursor cache hits' )
/

*** for current session (End) ***

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/317003/viewspace-591123/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/317003/viewspace-591123/