cancel
Showing results for 
Search instead for 
Did you mean: 

How to analyse the number of open cursors being used by each program?

Former Member
0 Kudos

Hi,

I am getting an error "ORA:1000 Maximum Open Cursors Exceeded" in my

application. I want to analyse and monitor that how many cursors are

being used by each program running in a particular session. I have

already set the Open Cursors Count double of the recommended for my

application, yet I am getting this error.

Is there any tool to analyse and monitor the number of cursors being

used by each DB call?

I have installed SAP NW 7.0 and Using Oracle 10g as DB.

Your immediate help will be greatly appreciated.

Best Regards,

Smriti.

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

You can list the open cursors per session and check which one uses the most with sqlplus:

SQL> 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 = 'opened cursors current';

Values over 300 are unusual.

It is quite possible, that you see several workprocesses using a lot, because workprocesses are kind of shared sessions.

Cheers Michael

Edit: ah, our posts crossed, obviously there is no workprocess in the java world

Former Member
0 Kudos

Hi ,

You can look for oracle wait events related to oracle sessions in ST04 or ST04old transaction and look for the following wait events if they are occuring quite frequently.

cursor pin X

cursor pin S

etc.

Also look for V$views (v$open_cursor & V$SESSTAT) in ST04 or if there are some new views in Oracle 10g and check open cursors and SQL statements that are causing this.(sql_text field) and its count( count field)

Hope this is of some help

Regards

Amit

Edited by: Amit Bangotra on Jan 13, 2010 1:13 PM

Former Member
0 Kudos

Hi Amit,

Thanks for your reply.

Sorry i forgot to mention my application runs on Java Stack and not ABAP. Open SQL has been used to query the DB.

Can you please give some info how to find it on Java Stack?

Best Regards,

Smriti.

Former Member
0 Kudos

Java Stack only,I came across this link .Check this,if its again of any help to you

http://oracle.ittoolbox.com/groups/technical-functional/oracle-db-tuning-l/ora1000-maximum-open-curs...

Regards ,

Amit

Edited by: Amit Bangotra on Jan 13, 2010 1:26 PM