prompt == Start of check_cursor.sql
prompt ====================================================
prompt ====================================================
prompt == Monitor cursor usage
prompt == Due to bug 21135007, the session_cached_cursor usage might be out
prompt == number due to parallel execution.
prompt ====================================================
set lines 300
col value format a30
col usage format a30
SELECT
'session_cached_cursors' PARAMETER,
lpad(VALUE, 5) VALUE,
decode(VALUE, 0, 'n/a', to_char(100 * used / VALUE, '990') || '%') usage , used
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') || '%', used
FROM(SELECT MAX(SUM(s.VALUE)) used
FROM v$statname n, v$sesstat s
WHERE n.name ='opened cursors current'
AND s.statistic# = n.statistic#
GROUP BY s.sid
),
( SELECT VALUE
FROM v$PARAMETER
WHERE name = 'open_cursors'
);
prompt ====================================================
prompt == Top 10 cursor hungry sql statements
prompt ====================================================
select *
from (select t.value ,s.sid, s.sql_id
from v$sesstat t, v$statname n, v$session s
where t.sid=s.sid and n.name= 'session cursor cache count' and t.statistic# = n.statistic# order by 1 desc) where rownum <=10;
prompt ====================================================
prompt == Total cursors open, by username & machine
prompt ====================================================
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, s.username, s.machine
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 current'
group by s.username, s.machine
order by 1 desc;
prompt ====================================================
prompt == Show sqls having high child cursor count > 100
prompt ====================================================
select inst_id, sql_id, sql_profile, plan_hash_value, max(child_number)
from gv$sql group by inst_id, sql_id, sql_profile, plan_hash_value
having max(child_number) >100;
prompt ====================================================
prompt == End of check_cursor.sql
prompt ====================================================
