?
Solved

Oracle 8i -- Cursor count increased even after closing resultset/statement/connection

Posted on 2005-04-11
12
Medium Priority
?
2,489 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:autoknowledge
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 25

Assisted Solution

by:jrb1
jrb1 earned 750 total points
ID: 13759301
Before you can execute SQL, Oracle has to parse it.  If you execute the same SQL again, the system doesn't need to parse it again.  So, when you run the same twice, it's good the SQL is resused.  So, depending on how your queries are being built, you should be able to reuse the queries with different parameters.  But how are you building them?  Something like this:

select .... from table where col1 = :1 and col2 = :2....

or are you building a string:

select .... from table where col1 = 'string1' and col2 = 'string2'...

If you use the first type above, it should reuse the SQL fine, but the latter will generate a new cursor each time it is different.
0
 
LVL 11

Expert Comment

by:sujit_kumar
ID: 13759354
>>Does Oracle remove the closed cursors right away or is the increasing count a sign that we're doing something wrong?

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
0
 
LVL 48

Accepted Solution

by:
schwertner earned 750 total points
ID: 13759610
To close DEAD connections and free the cursors add to SQLNET.ORA file
the line SQLNET.EXPIRE_TIME = 20 to close dead sessions every 20 minutes.

About opened cursors - it is a nasty problem.

Some people tried closing all result sets before. And I still got the max cursors exceeded errors. As mentioned, bumping up the cursor
limit is only a temporary solution. There is only one solution that I found - that is to close your connections periodically.
When a connection is closed, all the associated resources (such as cursors) are also closed.

I've experimented a bit
        with this - you can set an arbitrary "chunk" size. When you hit this chunk size you can close all connections and
       restart. When you close a connection, it will close all associated resultsets, cursors etc which will free up some
        resources.
           eg.
       // Loop counter
       int count = 0;
      // To indicate when to clean up connection and start afresh
      int chunks = 20; // although this size is aribtrary, but found to be a good setting

       for (int i=0; i<100;i++)
      {
       count++;
       if ((count % chunks) == 0)
        {
            if (conn != null && !conn.isClosed()) conn.close();
            conn = ConnectionManager.connect(database_url_string);
       }  
      }



1. You should not use the finally block to reliably close cursors.
2. You should design your code to ensure that the resultSet/cursor and staements go out of scope after the close() method is invoked. This is necessary in order to execute garbage collection and reclaim the memory allocated for cursors

If you receive messages that you are running out of cursors or that you are running out of memory, make sure that all your Statement and ResultSet objects are explicitly closed. The Oracle JDBC drivers do not have finalizer methods. They perform cleanup routines by using the close() method of the ResultSet and Statement classes. If you do not explicitly close your result set and statement objects, significant memory leaks can occur. You could also run out of cursors in the database. Closing a result set or statement releases the corresponding cursor in the database.

Similarly, you must explicitly close Connection objects to avoid leaking and running out of cursors on the server side. When you close the connection, the JDBC driver closes any open statement objects associated with it, thus releasing the cursor objects on the server side.

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 

Author Comment

by:autoknowledge
ID: 13769042
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.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13769577
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
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13769868
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.
0
 

Author Comment

by:autoknowledge
ID: 13794576
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.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13794928
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('cursor count 1: ' || to_char(v_cursor_cnt));
   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('cursor count 2: ' || to_char(v_cursor_cnt));
   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('cursor count 3: ' || to_char(v_cursor_cnt));
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.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13797873
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.
0
 
LVL 25

Expert Comment

by:jrb1
ID: 13840242
Any other questions?
0
 
LVL 48

Expert Comment

by:schwertner
ID: 13840603
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.
0
 

Author Comment

by:autoknowledge
ID: 14125446
Out of sheer luck, we seemed to find that by creating the PS with this:

ps = conn.prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

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.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question