Oracle Cursor management

[David Keith]

We have an application that opens a lot of cursors in Oracle. We are getting Alerts from Oracle that we are exceeding MAXOPENCURSORS.

I found that Oracle has a parameter called RELEASE_CURSOR and another HOLD_CURSOR. Here's an excerpt fromhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0:::11_QUESTION_ID:1041031921901:

To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and
RELEASE_CURSOR=YES. This way, after the cursors are used, Oracle will free up
the memory for other cursors.

I'm researching how best to approach our use of cursors. Since we use DOA I wonder if you could please point me to a resource that describes how DOA handles cursor management?

Do you have recommendations for how we use cursors with TOracleQuery? For example if I call Close() on a TOracleQuery, what is the process that occurs in regards to the open cursor? We're not using Stored Procs, so this really only applies to SELECT statements.

Thanks in advance for any reply.

David Keith

 

[Marco Kalter]

It depends on your Oracle Server version and on the Direct Oracle Access version. Can you let me know your versions?

 

[David Keith]

The Oracle Server is 11g, the version of DOA is 4.11.

Thanks.

 

[Marco Kalter]

In that case cursors are implicitly closed when possible. Cursors for select statements are closed when the last record has been fetched, and cursors for all other statements are closed immediately after execution.

 

[David Keith]

Marco -

Since '...Cursors for select statements are closed when the last record has been fetched...' does that mean that you are caching all of the data for the query in the TOracleQuery component (dataset/delta)? Could settings such as IsolationLevel , OptimizerGoal, or any others affect this?

Do you have any idea why we would see a large number of open cursors, since we are only using TOracleQuery? I have seen some of our code (for reports) that fetches large amounts of data in 10k chunks, performing sequential fetches in a loop until all data is collected. In my experience with Oracle fetching data in a loop is a surefire way to kill your Oracle server.

Thanks.

 

[Marco Kalter]

Since '...Cursors for select statements are closed when the last record has been fetched...' does that mean that you are caching all of the data for the query in the TOracleQuery component (dataset/delta)?

The TOracleQuery component does not cache records. Only one record is accessible at a time, and after EOF no record is accessible. The TOracleDataSet does indeed cache all records.

Could settings such as IsolationLevel , OptimizerGoal, or any others affect this?

Only TOracleQuery.Scrollable affects this. Scrollable queries are never implicitly closed.

Do you have any idea why we would see a large number of open cursors, since we are only using TOracleQuery?

What is the SQL for these open cursors? Do you recognize the SQL as your application code?

 

[David Keith]

In our setup, we have one user accessing the database, so aside from system queries all cursors will be generated from one user having 1 login for each application instance.

In looking at the cursors for the biggest offender - one of our common username sessions - all of the queries were system queries. Other sessions with the common username are all queries from our system.

Our sessions typically will have 30 - 50 open cursors. I've looked at the data from Oracle, Tom Kyte, and other pros, and the advice sometimes seems ambiguous/unclear.

Our SESSION_CACHED_CURSORS is set to 20, and our OPEN_CURSORS is set to 300. We routinely go over 2500 open cursors, and get alerts from Oracle in the DB Console.

I'm beginning to wonder if the problem isn't that since we use only one user login for all sessions Oracle is adding together the cursor count from all sessions and coming up with it's numbers.