诊断ORA-01000打开游标过多

2018-01-03 11:02:52来源:oschina作者:易野人点击

分享
set linesize 140 pagesize 1400--cursor_cache_hits soft_parses hard_parses 使用百分比
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 v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' )
/--查看使用情况
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')
and s.statistic# = n.statistic#
group by s.sid),
(select value from v$parameter where name = 'open_cursors') ;

-- 查看session cursor cache hits
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%cursor ca%'
/
select sum(a.value), b.name,a.sid
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by rollup (b.name,a.sid)
order by 1
/
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#and s.sid=a.sid
and b.name = 'session cursor cache count'
order by 1
/--导致游标漏出的会话
--应用程序是否打开了游标,却没 有在它完成工作后没有及时关闭

SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL# ,b.NAME
FROM V$SESSTAT A, V$STATNAME B, V$SESSION S
WHERE A.STATISTIC# = B.STATISTIC#
AND S.SID = A.SID
AND B.NAME = 'opened cursors curent';
select sid, count(*) from v$open_cursor group by sid
order by 2
/
Exec DBMS_WORKLOAD_REPOSITORY.create_snapshot();
exec dbms_lock.sleep(300);
Exec DBMS_WORKLOAD_REPOSITORY.create_snapshot(); @?/rdbms/admin/awrrpt
upload the awr report
or
select dbms_workload_repository.awr_report_text(l_dbid => dbid,
l_inst_num => instance_number,
l_bid=> mid - 1,
l_eid=> mid)
from (select vd.dbid, vi.instance_number, mid
from v$database vd,
v$instance vi,
(select max(snap_id) mid from dba_hist_snapshot dhs))
/

最新文章

123

最新摄影

闪念基因

微信扫一扫

第七城市微信公众平台