Script:Diagnostic ORA

jydg

以下脚本可以用于诊断ORA-01000打开游标过多错误:

set linesize 140 pagesize 1400
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 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 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))
/