首页 > 互联网技术 > 如何调整session_cached_cursors:

如何调整session_cached_cursors:

2008年1月17日 7,161 views 发表评论 阅读评论

这是oracle某超级牛人写的.转一个脚本session_cursor_cache.sql

——————————————————————————-

— Script: session_cursor_cache.sql
— Purpose: to check if the session cursor cache is constrained
— For:  8.0 and higher

— Copyright: (c) Ixora Pty Ltd
— Author: Steve Adams

— Description: If ‘session cursor cache count’ = session_cached_cursors, then
—  session_cached_cursors should be increased.
—  If ‘opened cursors current’ + ‘session cursor cache count’ =
—  open_cursors, then open_cursors should be increased.

——————————————————————————-
@save_sqlplus_settings
column parameter format a29
column value     format a5
column usage     format a5
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
sys.v_$statname  n,
sys.v_$sesstat  s
where
n.name = ‘session cursor cache count’ and
s.statistic# = n.statistic#
),
( select
value
from
sys.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
sys.v_$statname  n,
sys.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
sys.v_$parameter
where
name = ‘open_cursors’
)
/
column cursor_cache_hits format a17
column soft_parses format a11
column hard_parses format a11
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 value calls from sys.v_$sysstat where name = ‘parse count (total)’ ),
( select value hard from sys.v_$sysstat where name = ‘parse count (hard)’ ),
( select value sess from sys.v_$sysstat where name = ‘session cursor cache hits’ )
/
column max_cacheable_cursors format 99999999999999999999
select
max(count(*))  max_cacheable_cursors
from
( select
p.kglobt18  schema#  — parsing schema number
from
sys.x_$kglcursor  p
where
p.kglobt12 > 2   — enough parse_calls
union all
select
s.kglntsnm  schema#  — authorized schema number
from
sys.x_$kglcursor  c,
sys.x_$kglsn  s
where
c.kglobt12 > 2 and
s.kglhdadr = c.kglhdadr
)
group by
schema#
/
@restore_sqlplus_settings

========

查看一个生产系统.

SQL> show parameter session_cache
NAME                                 TYPE        VALUE
———————————— ———– ——————————
session_cached_cursors               integer     50
SQL> @session_cursor_cache.sql
PARAMETER                     VALUE USAGE
—————————– —– —–
session_cached_cursors           50  100%
open_cursors                    300   67%
應該繼續增大session_cached_cursors 參數,instance級的修改需要重啟DB
 » 如果喜欢可以: 点此订阅本站
分类: 互联网技术 标签: ,
  1. 本文目前尚无任何评论.
  1. 本文目前尚无任何 trackbacks 和 pingbacks.