Re: too many cursors open

On 14 Mar 2007, deanbrown3d_at_yahoo.com wrote:
> Oracle 10g, Windows.

Is the app java?

> We are getting a periodic error in an application that says we are
> opening too many cursors,

This usually means you are not closing callable statements and result sets. I'm assuming then, you don't have a common method that takes a callable or prepared statement and closes the resources? If you do, instrument it and instrument where you get the connection from the pool and line these things up and see if something logically is not right.

> and I am trying to debug this. Is there a SQL call to get a count of
> this number of cursors, so that one can see exactly which part of the
> app is doing the mischief? Or is there another approach?

One thing is to turn sqltrace on in the Oracle box, ratchet the connection pool down to 1 in the pool, run the suite of functionality paths, and then line this up with tkprof output from the oracle trace files. This assumes that you have instrumented the app well enough to be able to do the above.

> We don't see the error in our test machines, only at a client site.

One thing you can do on your test machines is ratchet your #connections on your test boxes to one connection in the pool. Then, make the app deal with everything through one connection. You can usually find the source of your type of issues doing this.

One other thing. From this point forward, make each developer put only one connection (or the minimum needed to be able to function) in their connection pool. There is no need for each developer to have 15 connections in their pool, which is usually the default # connections.

-- 
Galen Boyer