Open Cursor Queries

show parameter open_cursors;

which processes are using the open cursors by issuing the following query

select a.value, s.username,s.sid,s.serial#,s.program,s.inst_id
from gv$sesstat a,gv$statname b,gv$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name='opened cursors current';

You can issue the following query to identify the sessions with a high number of opened and parsed or cached cursors.

select saddr, sid, user_name, address,hash_value,sql_id, sql_text
from gv$open_cursor
where sid in
(select sid from v$open_cursor
group by sid having count(*)  > &threshold);

show parameter session_cached_cursors

You can check if the database is bumping against the maximum limit for session-cached cursors by issuing the following statement.

select max(value) from v$sesstat where statistic# in (select statistic# from v$statname where name='session cursor cache count');

You can find out how many cursors each session has in its session cursor cache by using the following query.


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';