Skip to content

Max open cursor problem

February 13, 2008

show parameter open_cursors

Check who open too many cursors

set line 140
col username format a16
col machine format a26
SELECT vs.username, vs.sid,vs.serial#, vs.machine,Count(*) “Open_Cursors”
FROM v$open_cursor voc, v$session vs
WHERE voc.sid= vs.sid
GROUP BY vs.username, vs.sid,vs.serial# ,vs.machine
ORDER BY 5;

Get the entire queries

SELECT vst.HASH_VALUE, vst.SQL_TEXT, vst.PIECE, Count(vst.SQL_TEXT)
FROM v$open_cursor voc, v$sqltext vst, v$session vs
WHERE voc.sid= vs.sid
AND vs.username=’CMGWAOE’
and voc.HASH_VALUE=vst.HASH_VALUE
and voc.ADDRESS=vst.ADDRESS
GROUP BY vst.HASH_VALUE,vst.SQL_TEXT ,vst.PIECE

Close all PreparedStatements or Statements etc after each use – if you use them for another query without doing so, thats another cursor you are leaving open.

OraclePreparedStatement ocs = //created that. = 1 cursor created.
ocs.setI/P( —)
ocs.setO/P( — )
then ocs.execute ( ) // Per execution 1 cursor created.

So after 700 x execution cursor exceeded problem so close ocs and create again.

http://www.p6spy.com/
this can be reset in the INI file with “max_cursors=xxxx”.
this is a shared variable for the complete instance

select q.sql_text
2 from v$open_cursor o, v$sql q
3 where q.hash_value=o.hash_value and o.sid = 217;

select o.sid, osuser, machine, count(*) num_curs

2 from v$open_cursor o, v$session s
3 where user_name = ‘SCOTT’ and o.sid=s.sid
4 group by o.sid, osuser, machine
5 order by num_curs desc;

Advertisements
No comments yet

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: