open_cursor session_cached_cursors 优化
--当前数据库连接的会话数和进程数
select count(*) from v$session
union
select count(*) from v$process;
-- 查看当前session_cached_cursors 的使用情况:
select e.* from v$sesstat e, v$statname s
where s.NAME = 'session cursor cache count' and e.STATISTIC# = s.STATISTIC# order by e.VALUE desc;
-----session cursor cache hits就是系统在高速缓存区中找到相应cursors的次数,parse count(total)就是总的解析次数,二者比值越高,性能越好。如果比例比较低,并且有较多剩余内存的话,可以考虑加大该参数。
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%'
union
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';
select (select v.VALUE from V$SYSSTAT v where v.NAME='session cursor cache hits')/(select a.VALUE from V$SYSSTAT a where a.NAME='parse count (total)') from dual;
/*解释一下v$sesstat中相关取值的重要概念
1 > opened cursors current (当前单个session打开的cursors数量)
2 > parse count (hard) (当前session硬解析次数)
3 > parse count (total) (当前session解析总次数)
4 > session cursor cache count (设置session_cached_cursors参数后,"相同"sql被解析3次后被cache在session cursor cache中的数量
5 > session cursor cache hits (软软解析的次数 a "softer" soft parse )
6 > 手工计算一下软解析次数parse count (total) - session cursor cache hits - parse count (hard) = parse count(soft)*/
---每个会话打开的游标数
select sid, count(*) from v$open_cursor group by sid order by count(*) desc;
-----使用下面的sql判断'session_cached_cursors' 的使用情况。如果使用率为100%则增大这个参数值
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');
--pga实际的总使用量
select sum(m.ALLOCATED)/1024/1024 from v$process_memory m;
--先备份spfile
-- alter system set session_cached_cursors=500 scope=spfile sid='*';
--当前数据库连接的会话数和进程数
select count(*) from v$session
union
select count(*) from v$process;
-- 查看当前session_cached_cursors 的使用情况:
select e.* from v$sesstat e, v$statname s
where s.NAME = 'session cursor cache count' and e.STATISTIC# = s.STATISTIC# order by e.VALUE desc;
-----session cursor cache hits就是系统在高速缓存区中找到相应cursors的次数,parse count(total)就是总的解析次数,二者比值越高,性能越好。如果比例比较低,并且有较多剩余内存的话,可以考虑加大该参数。
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%cursor%'
union
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME LIKE '%parse%';
select (select v.VALUE from V$SYSSTAT v where v.NAME='session cursor cache hits')/(select a.VALUE from V$SYSSTAT a where a.NAME='parse count (total)') from dual;
/*解释一下v$sesstat中相关取值的重要概念
1 > opened cursors current (当前单个session打开的cursors数量)
2 > parse count (hard) (当前session硬解析次数)
3 > parse count (total) (当前session解析总次数)
4 > session cursor cache count (设置session_cached_cursors参数后,"相同"sql被解析3次后被cache在session cursor cache中的数量
5 > session cursor cache hits (软软解析的次数 a "softer" soft parse )
6 > 手工计算一下软解析次数parse count (total) - session cursor cache hits - parse count (hard) = parse count(soft)*/
---每个会话打开的游标数
select sid, count(*) from v$open_cursor group by sid order by count(*) desc;
-----使用下面的sql判断'session_cached_cursors' 的使用情况。如果使用率为100%则增大这个参数值
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');
--pga实际的总使用量
select sum(m.ALLOCATED)/1024/1024 from v$process_memory m;
--先备份spfile
-- alter system set session_cached_cursors=500 scope=spfile sid='*';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29519108/viewspace-1435387/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29519108/viewspace-1435387/