了解Maclean Liu|向Maclean Liu提问 Oracle ALLSTARS 全明星(群内有多位Oracle高级售后support,N位OCM和ACE) QQ群 # QQ群号:23549328 # 已经升级到 2000人群,空位多多。欢迎有一定基础的Oracle骨友加入,现在入群需要经过Maclean的技术面试,欢迎面试,请加QQ号:47079569 为好友参加面试 2群基础群 适合刚入门的同学,会共享最佳入门实践和资料 QQ群 # QQ群号:171092051 # 已经升级到 500人的超级群,空位多多,无需面试

SCRIPT - to Tune the 'SESSION_CACHED_CURSORS' and 'OPEN_CURSORS' Parameters

Script:

select
  'session_cached_cursors'  parameter,
  lpad(value, 5)  value,
  decode(value, 0, '  n/a', to_char(100 * used / value, '990') || '%')  usage
from
  ( select
      max(s.value)  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name = 'session cursor cache count' and
      s.statistic# = n.statistic#
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'session_cached_cursors'
  )
union all
select
  'open_cursors',
  lpad(value, 5),
  to_char(100 * used / value,  '990') || '%'
from
  ( select
      max(sum(s.value))  used
    from
      v$statname  n,
      v$sesstat  s
    where
      n.name in ('opened cursors current', 'session cursor cache count') and
      s.statistic# = n.statistic#
    group by
      s.sid
  ),
  ( select
      value
    from
      v$parameter
    where
      name = 'open_cursors'
  )
/

Sample Output:

PARAMETER              VALUE      USAGE
---------------------- ---------- -----
session_cached_cursors    20       100%
open_cursors             300        16%

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' )
/

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' )
/

posted on 2013-03-19 00:32  Oracle和MySQL  阅读(197)  评论(0编辑  收藏  举报

导航