ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 确定SESSION_CACHED_CURSORS的效果,以及查询其与SOFT/HARD PARSE的比例

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

原创 Linux操作系统 作者:yexichang 时间:2009-04-21 11:10:54 0 删除 编辑
目标:输出下面这样的结果
   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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-08-30

  • 博文量
    32
  • 访问量
    35223