I'm getting this error alot in our Test environment. We are using Oracle 8.1.5, Delphi 5.1, and DOA. When I view v_$open_cursors I see that most of the sql is from our MIDAS app server using DOA. I thought that once the data was retrieved and sent to the client, that the cursor would be closed. We are not using FetchOnDemand. Is there something I need to set to control the closing of the cursors? I currently have the open_cursors parameter set to 400.
maximum open cursors
- Thread starter jimpoe
- Start date
If the open_cursors value of 400 is enough for your application, then maybe you have a 'cursor leak' somewhere. This can occur in 2 situations:
[*]You forget to free database access components that you dynamically create.
[*]You are using cursor variables and an 'older' version of Net8 8.0.x. There is a bug in old Net8 versions which could lead to this problem when using cursor variables. I'm not sure in which version it is fixed.
[/list]
------------------
Marco Kalter
Allround Automations
[*]You forget to free database access components that you dynamically create.
[*]You are using cursor variables and an 'older' version of Net8 8.0.x. There is a bug in old Net8 versions which could lead to this problem when using cursor variables. I'm not sure in which version it is fixed.
[/list]
------------------
Marco Kalter
Allround Automations
When my client is started, I create a datamodule that contains our lookup clientdatasets. All queries resemble : "SELECT A.* FROM PDCR_TERR A ORDER BY RANK"
After I open the app and it is 'idling' I check v$open_cursor and find that all the queries for the lookup tables are still open. I would expect the cursors to be closed by this time.
Thanks
After I open the app and it is 'idling' I check v$open_cursor and find that all the queries for the lookup tables are still open. I would expect the cursors to be closed by this time.
Thanks
I just tried a simpler example. A form, a grid, a datasource, two buttons, an OracleDataSet and an OracleSession. SQL is 'SELECT * FROM ADDR_TYPE'.
Button 1 sets OracleSession.Connected = True and OracleDataSet.Open.
v$open_cursor now contains 'SELECT * FROM ADDR_TYPE'
Button 2 calls OracleDataSet.Close.
v$open_cursor still contains 'SELECT * FROM ADDR_TYPE'
Shut down app and session and v$open_cursor does not contain the sql
I would think that closing the dataset would be enough to close the cursor.
Thanks
Button 1 sets OracleSession.Connected = True and OracleDataSet.Open.
v$open_cursor now contains 'SELECT * FROM ADDR_TYPE'
Button 2 calls OracleDataSet.Close.
v$open_cursor still contains 'SELECT * FROM ADDR_TYPE'
Shut down app and session and v$open_cursor does not contain the sql
I would think that closing the dataset would be enough to close the cursor.
Thanks
I too am getting this problem with a simple query open. It looks as though when you close the TOracleDataSet it is not closing it's interal TOracleQuery. Turns out if you use Closeall() instead of close this problem goes away. All Closeall does is this....
Active := False;
if FQuery nil then FQuery.Close;
DataEvent(dePropertyChange, 0);
The only difference between the two (other than the event firing) is FQuery being closed.
[This message has been edited by Dean (edited 11 October 2000).]
Active := False;
if FQuery nil then FQuery.Close;
DataEvent(dePropertyChange, 0);
The only difference between the two (other than the event firing) is FQuery being closed.
[This message has been edited by Dean (edited 11 October 2000).]
Your DBA is right, it is an optimization that allows the dataset to reuse the same parsed representation of the cursor when it is subsequently reopened. I guess the CloseAll procedure should have been a Boolean property instead...
The "right" solution is to increase the OPEN_CURSORS initialization parameter.
------------------
Marco Kalter
Allround Automations
The "right" solution is to increase the OPEN_CURSORS initialization parameter.
------------------
Marco Kalter
Allround Automations
Is this possibly a multiuser issue? Does OPEN_CURSORS apply to each session or to all sessions?
My errors never occured in my development environment where I was the only user. The problem always occured in our test environment where multiple users were hitting the database at the same time.
My errors never occured in my development environment where I was the only user. The problem always occured in our test environment where multiple users were hitting the database at the same time.
OPEN_CURSORS is an optional setting in the initialization file.
It's only a multiuser issue if OPEN_CURSORS applies to all sessions. If it does, you need to set the OPEN_CURSORS value to max_cursors_for_app * number_of_users.
If it only applies to a session, then setting OPEN_CURSORS larger than the actual number of cursors used by your application should fix the problem.
If the OPEN_CURSORS only applies to a single session then what you and I were seeing with multiuser environments was more of the applications cursors being used than what is used in our development environment and in that case, increasing the OPEN_CURSORS to exceed the application's cursor count is appropriate.
Jim
It's only a multiuser issue if OPEN_CURSORS applies to all sessions. If it does, you need to set the OPEN_CURSORS value to max_cursors_for_app * number_of_users.
If it only applies to a session, then setting OPEN_CURSORS larger than the actual number of cursors used by your application should fix the problem.
If the OPEN_CURSORS only applies to a single session then what you and I were seeing with multiuser environments was more of the applications cursors being used than what is used in our development environment and in that case, increasing the OPEN_CURSORS to exceed the application's cursor count is appropriate.
Jim
I have been following this thread as I work with Dean and thought I would let you all know that the OPEN_CURSOR applies at the session level and is not the total number of allowed cursors across the entire Oracle instance.
By setting this parameter too high allows for the possibility of poorly written SQL code to generate an inordinate number of cursors and could result in the private memory allocated to the user to be chewed up - forcing disk swapping. A good balance between number of cursors allowed and size of memory allocated results in faster executing code.
If the same SQL statement is executed repeatedly with only the parameter values changing it may a good idea to leave the cursor open, but if the SQL is executed only once then close the cursor.
The maximum value for OPEN_CURSORS is dependant upon the operating system that the Oracle server is running on (eg. NT, AIZ, Solaris).
Rick
By setting this parameter too high allows for the possibility of poorly written SQL code to generate an inordinate number of cursors and could result in the private memory allocated to the user to be chewed up - forcing disk swapping. A good balance between number of cursors allowed and size of memory allocated results in faster executing code.
If the same SQL statement is executed repeatedly with only the parameter values changing it may a good idea to leave the cursor open, but if the SQL is executed only once then close the cursor.
The maximum value for OPEN_CURSORS is dependant upon the operating system that the Oracle server is running on (eg. NT, AIZ, Solaris).
Rick
It's obvious that an SQL statement that is executed inside a loop should be left open, but does this apply to an SQL statement that a user will use twice in a session or may use in random intervals?
Most of my sql is set up in datasets on a MIDAS server. In most cases it is only the parameters that change, but this sql is executed as the user navigates through the application and so may be executed frequently or not at all depending on where the user goes. Would this type of sql be a candidate for being left open?
Thanks
Jim
If you are not sure if the SQL is going to be executed, say 50/50 chance, I would suggest that you close the cursor. If over the long run it turns out that it is really executed 70% of the time then keep the cursor open.
Typically SQL execution is normally evaluated at development time or when there are problems in production (eg. slow SQL after a table has grown in size). What really needs to be done in the first few months in production is to re-evaluate the SQL and monitor the statistics. By doing this the DBA can determine if indexes need to be added or dropped, SQL cursors be left open or vice-versa, etc, etc.
But, since this is the real world, there are always new challenges and we seldomn get the chance to go back and make things better.
Sorry, this is not a definative answer to your question but rather an indication that a judgement call is required when there is no clear cut answer.
Rick
Typically SQL execution is normally evaluated at development time or when there are problems in production (eg. slow SQL after a table has grown in size). What really needs to be done in the first few months in production is to re-evaluate the SQL and monitor the statistics. By doing this the DBA can determine if indexes need to be added or dropped, SQL cursors be left open or vice-versa, etc, etc.
But, since this is the real world, there are always new challenges and we seldomn get the chance to go back and make things better.
Sorry, this is not a definative answer to your question but rather an indication that a judgement call is required when there is no clear cut answer.
Rick
A memory bug has been reported in Oracle 8i. This is bug 600083, ORA-04030: out of process memory. This bug may also manifest itself as an open cursors problem.
The problem is twofold. First Oracle may not free up user memory correctly during a session. Second, even if you explicitly close cursors during a session, the cursors will remain cached.
If you know that you are kicking off a session where memory or open cursor problems may occur, you should consider using the following:
Fire the following procedure on logon to oracle:
dbms_session.set_close_cached_open_cursors (true);
In your PL/SQL code use the following procedure to explicitly free memory:
dbms_session.free_unused_user_memory;
Also, you can set optimizer_percent_parallel parameter to 0.
[This message has been edited by techstar (edited 03 January 2001).]
The problem is twofold. First Oracle may not free up user memory correctly during a session. Second, even if you explicitly close cursors during a session, the cursors will remain cached.
If you know that you are kicking off a session where memory or open cursor problems may occur, you should consider using the following:
Fire the following procedure on logon to oracle:
dbms_session.set_close_cached_open_cursors (true);
In your PL/SQL code use the following procedure to explicitly free memory:
dbms_session.free_unused_user_memory;
Also, you can set optimizer_percent_parallel parameter to 0.
[This message has been edited by techstar (edited 03 January 2001).]
We struggled with that OPEN_CURSOR problem for the past year or so. It was solved once and for all by using 8.1.6 Client. Against any database, I might add. The clients 8.0.5 through 8.1.5 all exhibit the problem. Don't actually know exactly what's different, but it works, and that's enough for me!
Dave
Dave
Similar threads
- Discussion
