|
To allow the DBA/Analysts to check whether the sessions cursor cache or open cursors are really a constraint and then increase the parameter session_cached_cursors or open_cursors accordingly.
SESSION_CACHED_CURSORS lets you specify the number of session cursors to cache.
Repeated parse calls of the same SQL statement cause the session cursor for that statement to be moved into the session cursor cache.
Subsequent parse calls will find the cursor in the cache and do not need to reopen the cursor and even do a soft parse.
The session cursor cache can be constrained by either the session_cached_cursors parameter, or the open_cursors parameter. This script reports the current maximum usage in any session with respect to these limits.
If either of the Usage column figures approaches 100%, then the corresponding parameter should normally be increased.
正确设置open_cursors和session_cached_cursors' 可以减少sql解析，提高系统性能，那么，如何正确设置session_cached_cursors' 这个参数？
我们可以把握下面的原则：
1、'session_cached_cursors' 数量要小于open_cursor
2、要考虑共享池的大小
3、使用下面的sql判断'session_cached_cursors' 的使用情况。如果使用率为100%则增大这个参数值。
|
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' )
/
这个sql用了max(s.value)，也就是只要有个session cache了最大的cursor，就会报100%。 使用以下方法可能更好：
select value,count(*) from v$sesstat where statistic#=(select statistic# from v$sysstat where name ='session cursor cache count') and sid in (select sid from v$session where type='USER') group by value;
最主要的调优原则还是从需求而来，如果Library Cache Latch Contention并没有什么问题，那就没什么要调。
扩展阅读：http://www.orafaq.com/node/758
(责任编辑：OracleTT) |
