如何调整session_cached_cursors:
这是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
—
— 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
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’
)
/
‘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
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’ )
/
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#
/
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
———————————— ———– ——————————
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 50 100%
open_cursors 300 67%
應該繼續增大session_cached_cursors 參數,instance級的修改需要重啟DB
» 转载保留版权:老陈 » 《如何调整session_cached_cursors:》» 如果喜欢可以: 点此订阅本站