topic Re: Oracle cursor issue in Operating System - HP-UX

Oracle cursor issue

Praveen Bezawada — Tue, 04 Sep 2001 09:55:14 GMT

Hi All
I know this is not the correct place to post this question, but i am hopeful that i can get some lead.
We are running oracle 8.0.5 on HPUX1100 with June 2001 Patch Bundle. We have an java application that uses JDBC Thin driver 1.1 to connect to the database.
When the application is performing some batch jobs we get the error
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
When checked in v$open_cursor view the maximum cursors any session is using is 58 and we have configured the values of OPEN_CURSOR as 250. I am confused why this is happening.
What is going wrong...
Thanks in advance
...BPK...

Re: Oracle cursor issue

T G Manikandan — Tue, 04 Sep 2001 10:44:34 GMT

Hello,
The closing of the cursor change based on the tool you use:
In JDBC, preparedStatement.close() does closes the cursor.
Please check whether you have this in your application.
For ex;
Original Code:
// Detect lost or closed connection
rtmp.connection.sqlconn.prepareCall("select * from dual");
.....
Replaced by:
// Detect lost or closed connection
PreparedStatement st = rtmp.connection.sqlconn.prepareStatement("select * from dual");
st.execute();
st.close();
Thanks
G Manikandan

Re: Oracle cursor issue

Alexander M. Ermes — Tue, 04 Sep 2001 10:47:16 GMT

Hi there.
Try to set the open_cursors in the init.ora to 2000. This should help you.
Rgds
Alexander M. Ermes

Re: Oracle cursor issue

Praveen Bezawada — Tue, 04 Sep 2001 10:48:23 GMT

Hi
I am positive that we are closing the prep statements.
I am wondering if there is another parameter apart from open_cursor which decides the number of cursors that can be opened.

Re: Oracle cursor issue

Praveen Bezawada — Tue, 04 Sep 2001 10:52:59 GMT

Hi Alexander,
Increasing open_cursor may help but I want to know what all these cursor are. If there is some kind of leak then even 1000 may not be enough....

Re: Oracle cursor issue

Andreas D. Skjervold — Wed, 05 Sep 2001 05:14:08 GMT

Hi
This is from Oracle:

OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors. This parameter also constrains the size of the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as statements are reexecuted by a user.

It is important to set the value of OPEN_CURSORS high enough to prevent your application from running out of open cursors. The number will vary from one application to another. Assuming that a session does not open the number of cursors specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.

Andreas

Re: Oracle cursor issue

Osa Jousou_1 — Wed, 05 Sep 2001 13:45:21 GMT

If you increase open cursors you will consume more memory. Perhaps you want to include the parameter: close_cached_open_cursors = true in the init file. It can help you to close the used cursors. This parameter controls whether cursors opened and cached in memory by PL/SQL are automatically closed at each COMMIT. A value of FALSE signifies that cursors opened by PL/SQL are held open so that subsequent executions need not open a new cursor. If PL/SQL cursors are reused frequently, setting the parameter to FALSE can cause subsequent executions to be faster.
A value of TRUE causes open cursors to be closed at each COMMIT or ROLLBACK. The cursor can then be reopened as needed. If cursors are rarely reused, setting the parameter to TRUE frees memory used by the cursor when the cursor is no longer in use.

Re: Oracle cursor issue

Praveen Bezawada — Wed, 05 Sep 2001 14:14:24 GMT

Hi Jousou,
We cannot set
close_cached_open_cursors to true as this would cause the sql's to open a new cursor every time it gets executed. This would be severely degrade the performance of our application. We have stick to false.
I have one question..
Can we be 100% sure that the values shown in v$open_cursor are correct. ??

Re: Oracle cursor issue

Osa Jousou_1 — Wed, 05 Sep 2001 14:25:31 GMT

According to Oracle, even when you explicitly close the cursor, in the background, it's cancelled or put on hold not actually closed. Unless you set the parameter I mentioned previously. Your other choice is to increase open_cursors and make sure you have enough memory to handle the new number. Flushing the shared pool is another idea but it may not be an option for you. That's Oracle for you.