autoknowledge
asked on
Oracle 8i -- Cursor count increased even after closing resultset/statement/connection
We are running a single database query with different variables each time. Everytime we run the query with a different set of variables, the cursor count increases. If we run the same query again later, the cursor count does not increase.
To count the cursors, we're using:
select count(*) from v$open_cursor;
We are closing the resultset, statement and connection after each execute.
My questions are:
Does Oracle remove the closed cursors right away or is the increasing count a sign that we're doing something wrong?
The select statement to count the cursors is checking for open cursors, so I don't understand how the cursor can still be open even after closing the rs, stmt and conn.
(I keep reading about the java garbage collection with regards to this issue, so I'm wondering if Oracle has a similar issue)
(Could caching be an issue?)
If this is a sign that there is a problem with something that we are doing, what are some possible fixes/references that I can look into? Again, people keep referring to changes that should be made to the init.ora file, but I'm not sure exactly what I should be looking for.
Sorry to say, I don't understand too much about dealing with databases, so any tips would be great.
If you have any questions, I should be able to fill you in with any information you need tomorrow (tuesday).
Thanks
To count the cursors, we're using:
select count(*) from v$open_cursor;
We are closing the resultset, statement and connection after each execute.
My questions are:
Does Oracle remove the closed cursors right away or is the increasing count a sign that we're doing something wrong?
The select statement to count the cursors is checking for open cursors, so I don't understand how the cursor can still be open even after closing the rs, stmt and conn.
(I keep reading about the java garbage collection with regards to this issue, so I'm wondering if Oracle has a similar issue)
(Could caching be an issue?)
If this is a sign that there is a problem with something that we are doing, what are some possible fixes/references that I can look into? Again, people keep referring to changes that should be made to the init.ora file, but I'm not sure exactly what I should be looking for.
Sorry to say, I don't understand too much about dealing with databases, so any tips would be great.
If you have any questions, I should be able to fill you in with any information you need tomorrow (tuesday).
Thanks
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, all posts are helpful, but I'm still a bit confused.
jrb1, we are building a string and hence a new cursor is generated each time.
Sujit, I haven't looked at the references yet, but I guess what is confusing me is, how exactly do I close the cursor after the operation is over?
Also, what exactly is a session and when is it considered to be ALIVE and DEAD?
schwertner, we are closing the connection after every database query. In our queries, we open a new connection, query the database, get the resultset, copy those results to a container, close the resultset, close the statement and close the connection.
Also, how do you make the resultset/cursor and statements go out of scope after the close() method is invoked. Actually, I'm not too clear on what "out of scope" means in this case.
We are explicitly closing the statement, resultset and connection everytime we run the query though, so I'm not too sure why the cursor count is increasing by one if the cursor should be getting released after closing the objects.
jrb1, we are building a string and hence a new cursor is generated each time.
Sujit, I haven't looked at the references yet, but I guess what is confusing me is, how exactly do I close the cursor after the operation is over?
Also, what exactly is a session and when is it considered to be ALIVE and DEAD?
schwertner, we are closing the connection after every database query. In our queries, we open a new connection, query the database, get the resultset, copy those results to a container, close the resultset, close the statement and close the connection.
Also, how do you make the resultset/cursor and statements go out of scope after the close() method is invoked. Actually, I'm not too clear on what "out of scope" means in this case.
We are explicitly closing the statement, resultset and connection everytime we run the query though, so I'm not too sure why the cursor count is increasing by one if the cursor should be getting released after closing the objects.
You can build a string and still use bind variables. That way, you would reuse the same cursors. As to your other problem, I'll bet it is a phantom issue. From AskTom:
v$open_cursor can be misleading -- it is cursors that have been opened at some
point and may (or may not be) still open. It is useful in helping to track down
cursor leaks -- but it shows you more then just "really truely open" cursors.
Instead, run this query. It will tell you how many cursors are open:
select a.value, b.name
from v$mystat a
, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:553222846752
v$open_cursor can be misleading -- it is cursors that have been opened at some
point and may (or may not be) still open. It is useful in helping to track down
cursor leaks -- but it shows you more then just "really truely open" cursors.
Instead, run this query. It will tell you how many cursors are open:
select a.value, b.name
from v$mystat a
, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:553222846752
Connections are closed by the Garbage Collector of Java which works unpredictable - nobody knows when it will do the job.
Many developers use the following tricky method - they close many time the connection in loop to fill over some stacks
of the Garbage Collector and to awake him. Java is a mystery.
Many developers use the following tricky method - they close many time the connection in loop to fill over some stacks
of the Garbage Collector and to awake him. Java is a mystery.
ASKER
jrb1,
I used that query, and it is telling us that we only have 1 open cursor. This number never seems to increase. I thought that was a good sign until I decided to completely remove the rs.close, stmt.close, and conn.close from our database query function. This was in order to make sure the cursor counter query was actually working and that it would count up for every query that we ran. Well the number of open cursors still didn't increase.
Without removing the rs/stmt/conn .close:
Using the v$open_cursor query, when I run the query for the first time, it will count up. However, if I run the exact same query, it will not count up. I guess this could be caused by cursor cacheing... but wouldn't that mean the cursor was still open? At the same time, I am still only getting 1 open cursor using the v$mystat query no matter how many different queries I run.
This seems a bit odd. Again, I'm still a little confused about most of this DB stuff, so if you could explain this for me, that would be great.
Schwertner,
By using conn.close( ), what exactly is happening? Isn't the cursor on the DB server side? conn.close() doesn't tell the DB server that the connection has been closed which would thereby close all cursors?
-------------------------
I will also be raising the point value of this question so it's more fair.
I used that query, and it is telling us that we only have 1 open cursor. This number never seems to increase. I thought that was a good sign until I decided to completely remove the rs.close, stmt.close, and conn.close from our database query function. This was in order to make sure the cursor counter query was actually working and that it would count up for every query that we ran. Well the number of open cursors still didn't increase.
Without removing the rs/stmt/conn .close:
Using the v$open_cursor query, when I run the query for the first time, it will count up. However, if I run the exact same query, it will not count up. I guess this could be caused by cursor cacheing... but wouldn't that mean the cursor was still open? At the same time, I am still only getting 1 open cursor using the v$mystat query no matter how many different queries I run.
This seems a bit odd. Again, I'm still a little confused about most of this DB stuff, so if you could explain this for me, that would be great.
Schwertner,
By using conn.close( ), what exactly is happening? Isn't the cursor on the DB server side? conn.close() doesn't tell the DB server that the connection has been closed which would thereby close all cursors?
-------------------------
I will also be raising the point value of this question so it's more fair.
Here's what I did to test:
declare
v_cursor_cnt number;
cursor c1 is select table_name from user_tables;
cursor c2 is select index_name from user_indexes;
begin
select a.value into v_cursor_cnt
from v$mystat a
, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;
dbms_output.put_line('curs
open c1;
select a.value into v_cursor_cnt
from v$mystat a
, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;
dbms_output.put_line('curs
open c2;
select a.value into v_cursor_cnt
from v$mystat a
, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;
dbms_output.put_line('curs
end;
/
This gives results of:
cursor count 1: 2
cursor count 2: 4
cursor count 3: 6
So, the cursors are obviously being opened (and the query is returning the expected info). Then after the procedure has ended, I run the query, and we're back to 1. That's because all of the cursors are closed at the end.
declare
v_cursor_cnt number;
cursor c1 is select table_name from user_tables;
cursor c2 is select index_name from user_indexes;
begin
select a.value into v_cursor_cnt
from v$mystat a
, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;
dbms_output.put_line('curs
open c1;
select a.value into v_cursor_cnt
from v$mystat a
, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;
dbms_output.put_line('curs
open c2;
select a.value into v_cursor_cnt
from v$mystat a
, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;
dbms_output.put_line('curs
end;
/
This gives results of:
cursor count 1: 2
cursor count 2: 4
cursor count 3: 6
So, the cursors are obviously being opened (and the query is returning the expected info). Then after the procedure has ended, I run the query, and we're back to 1. That's because all of the cursors are closed at the end.
8i suffers from delay by clossing Java cursors. Try to migrate to 8.7.1.4
Also see if TIMED_STATISTICS = FALSE will help. This is in init.ora.
Also see if TIMED_STATISTICS = FALSE will help. This is in init.ora.
Any other questions?
I will suggest you to migrate ASAP to Oracle 9i (9.2.0.6).
You will enjoy not only the new features of the instance and underlying DB.
You will also get new enhanced versions of ODBC and JDBC.
8i is a "Gold Boy" of Oracle, but it grew up and now is called 9i and 10g.
You will enjoy not only the new features of the instance and underlying DB.
You will also get new enhanced versions of ODBC and JDBC.
8i is a "Gold Boy" of Oracle, but it grew up and now is called 9i and 10g.
ASKER
Out of sheer luck, we seemed to find that by creating the PS with this:
ps = conn.prepareStatement(sql,
the cursor would be removed as soon as the ps/rs/conn were closed.
We were previously using:
ps = conn.prepareStatement(sql)
Sorry for the late reply.
ps = conn.prepareStatement(sql,
the cursor would be removed as soon as the ps/rs/conn were closed.
We were previously using:
ps = conn.prepareStatement(sql)
Sorry for the late reply.
Yes Oracle does remove INACTIVE connections. But the OPEN curson will remain OPEN as long as the session is ALIVE.
>>If this is a sign that there is a problem with something that we are doing, what are some possible fixes/references that I can look into?
Look into your code, Are you closing the cursor after your operation is over? if not then do it.
>> Again, people keep referring to changes that should be made to the init.ora file, but I'm not sure exactly what I should be looking for.
See, http://gennick.com/open_cursors.html
http://www.theserverside.com/discussions/thread.tss?thread_id=33039
http://www.unix.org.ua/orelly/oracle/prog2/ch06_08.htm
Sujit