确定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) ***
请使用浏览器的分享功能分享到微信等