Kasthuri -- Thanks for the question regarding "Ref cursors as O/P params in SP- when are they closed?", version 8.1.5

Submitted on 15-Aug-2000 12:59 Central time zone

Tom's latest followup | Bookmark | Bottom

Last updated 4-Sep-2007 14:19

You Asked

Hi Tom,
 
 When we use ref cursors as output params in a stored proc,
 when does it get closed from the java app? 
 does the cursor remain opened till they close the connection 
 or gets closed if they close the resultset or their prepared
 statement? We have a # of procs returning cursors and an internet
 based app which is of concern with the max cursor init parameter. 
 with minimal testing in our devpt site,i could see a min of 128 cursors 
opened when i check the v$open_cursor. It is our concern to know
if this could become an issue when we move to production. we are still
under development.

Thanks in advance for you suggestion.
Regards
J.Kasthuri

and we said...

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.

A better query is something like:


     ResultSet rset =
          stmt.executeQuery
          ( "select a.value, b.name "+
              "from v$mystat a, v$statname b "+
             "where a.statistic# = b.statistic# "+
               "and a.statistic#= 3");

That'll tell you how many cursors you really truely have open... Consider this example 
(get_cur opens "select * from emp" in the stored procedure as a ref cursor)

import java.sql.*;
import java.io.*;
import oracle.jdbc.driver.*;


class curvar
{
  public static void showOpenCnt( Connection conn, String msg)
  throws SQLException
  {
      Statement stmt = conn.createStatement();
      ResultSet rset =
          stmt.executeQuery
          ( "select a.value, b.name "+
              "from v$mystat a, v$statname b "+
             "where a.statistic# = b.statistic# "+
               "and a.statistic#= 3");
      System.out.println( msg );

      while( rset.next() )
        System.out.println( rset.getString(1)+ " " +
                            rset.getString(2) );
      System.out.println( "-----------------------" );
      rset.close();
      stmt.close();
  }


  public static void main (String args [])
  throws SQLException, ClassNotFoundException
  {
      String query  =
           "begin demo_pkg.get_cur( :1 ); end;";

      DriverManager.registerDriver
          (new oracle.jdbc.driver.OracleDriver());

      Connection conn=
        DriverManager.getConnection
        ("jdbc:oracle:oci8:@ora8idev",
          "scott", "tiger");

      showOpenCnt( conn, "Before Anything" );

      CallableStatement cstmt = conn.prepareCall(query);
      cstmt.registerOutParameter(1,OracleTypes.CURSOR);
      cstmt.execute();

      showOpenCnt( conn, "After prepare and execute" );

      ResultSet rset = (ResultSet)cstmt.getObject(1);
      showOpenCnt( conn,
             "After prepare and execute and getObject" );

      for(int i = 0;  rset.next(); i++ )
      {
        if ( i == 10 )
           showOpenCnt(conn,"After fetching 10 rows...");
      }

      showOpenCnt( conn, "After but before any close" );

      rset.close();
      showOpenCnt( conn, "After rset.close" );

      cstmt.close();
      showOpenCnt( conn, "After cstmt.close" );
  }
}


When I run it (with thick or thin, tried both) I get:

$ java curvar
Before Anything
1 opened cursors current
-----------------------
After prepare and execute
3 opened cursors current
-----------------------
After prepare and execute and getObject
3 opened cursors current
-----------------------
After fetching 10 rows...
3 opened cursors current
-----------------------
After but before any close
2 opened cursors current
-----------------------
After rset.close
2 opened cursors current
-----------------------
After cstmt.close
1 opened cursors current
-----------------------

I'll ALWAYS have 1 cursor open (the query itself to show the count) but note how we get 2 
cursors (3) after the prepare and execute.  1 for the begin ... end; block and one for 
the select we open in the procedure.  As we are fetching rows -- we still have 2 
application cursors plus 1 for the v$mystat query.  

Notice what happens when we exhaust the result set though -- the cursor for the query 
seems to have disappeared (it did but don't count on it) before we closed it.  After we 
close it the cursor count does not change but after we close the begin ... end; block -- 
we get back to no cursors open.

follow to comment one

I cannot find any note 48871.996 (that doesn't look like valid note id?) in meta link but 
no worry.

You say
"This is because the statistic 'opened cursors current' displays the TOTAL number of 
opened cursors for the session, not necessarily what is currently 'open.'"


but if that were true, my example would show a continously INCREASING number wouldn't it? 
 (yes, that was a rhetorical question).


Rather, my example shows the number of cursors opened going up and down (as we expect).  


The issue is v$open_cursor will show you MORE open cursors then you really have (due to 
some cursor caching done by plsql and other environments).  You need to goto v$sessstat 
to get the REAL currently opened number of cursors.

There may be cursors in v$open_cursor that are not counted in the v$sesstat view for stat 
#3, they will be really closed if we need those slots again.

I stand by this example and what it shows.  If you want to know if you have a cursor 
leak, use the code above.

Reviews

Statistic# 3 is not as accurate as it seems! July 18, 2001 - 10am Central time zone

Bookmark | Bottom | Top

Reviewer: Klaas from The Netherlands

This query might work for your example, but it is not as accurate as you say it is.

See Doc ID: 48871.996 on Metalink called
 V$OPEN_CURSOR and V$SESSTAT Stat #3 doesn't match 

Statistic# 3 might results in a even higher number of open cursors than shown in v$open_cursor.
This is because the statistic 'opened cursors current' displays the TOTAL number of opened cursors 
for the session, not necessarily what is currently 'open.' 

If you have access to Metalink you can check 
Doc ID: 48871.996 called 'V$OPEN_CURSOR and V$SESSTAT Stat #3 doesn't match' for some more detail.

Ref cursor or Output parameters which is better? July 10, 2002 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: Nagaraju from USA

Tom,

thanks for the explanative example.
if have procedure that returns 5 output parameters.
is it a good way passing all the five with a single out put ref cursor
open myrefcur as select param1,param2,param3,param4,param5 from dual.

such that in my application when the front needs one more parameter to added as an output without 
any change in the middle tier, i can add one more
parameter say param6..

is this a good way or it is better to have 6 different parameter.

pleas suggest.

thanks

Followup July 10, 2002 - 2pm Central time zone:

tell me this -- if the middle tier DOESN'T change -- what is the purpose of this 6th output 
parameter?  I mean, why go to the bother of providing it if they cannot use it (as they are not 
aware of it)

Ref cursor or Output parameters which is better? July 10, 2002 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: nagaraju

yeah, i know what you mean. for thie pecular procedure. middle get everything from middle tier pass 
it to the front end(i.e to ASP page), so we handle it at the ASP without changing the 
component(where this component would have been called by someother ASP)

Followup July 10, 2002 - 3pm Central time zone:

well, a ref cursor will be more "flexible" (you can change the size and shape and not change the 
guy getting it in the middle).  It is overhead however - something that if you can reasonable avoid 
doing, I would avoid doing it.

Ref cursor or Output parameters which is better? July 10, 2002 - 2pm Central time zone

Bookmark | Bottom | Top

Reviewer: nagaraju

yeah, i know what you mean. for this pecular procedure. middle gets everything 
from database pass it to the front end(i.e to ASP page), so we handle it at 
the ASP on this 6th parameter without changing the component(where this component would have been 
called by someother ASPs)

What if my connection if from connection pool.. January 3, 2004 - 2am Central time zone

Bookmark | Bottom | Top

Reviewer: Sankar from India

Tom,
Thanks for your nice explaination on REF cursor closure. But in my Java application, I'm getting 
the connection from connection pool. So closing of connection in the application returns the 
connection back to the pool and the pool does not closes the connection physically. Then the same 
connection is handed over to the next client. So the number of cursors keep on increasing in the 
system. 

So what will be better way to handle this 'Maximium cursor exceded' problem in case of connection 
pools?

Thanks!

Warm Regards,
Sankar

Followup January 3, 2004 - 9am Central time zone:

umm, say you opened a file in your jsp or whatever after you grabbed a connection and you did not 
close that file.

What would you do to avoid getting "max files open in the operating system"

why -- you would CLOSE Them wouldn't you?  

close the ref cursors (result sets) just as you close your prepared and callable statements (no 
need to close a statement since we know the best java programmers ignore the existence of the 
statement class alltogether -- he says tongue in cheek)

refe cursors as output parameters May 26, 2004 - 7am Central time zone

Bookmark | Bottom | Top

Reviewer: sheikh abdullah from bangalore, india

it was really nice and exhaustive.

Ref Cursor June 8, 2004 - 12pm Central time zone

Bookmark | Bottom | Top

Reviewer: jay from India, Pune

hi Tom

pls tell me the diff in between ref cursor and output paramete, in the terms of speed and 
performance.

Followup June 8, 2004 - 1pm Central time zone:

that would be like comparing an elephant to a rock.

a ref cursor is an out parameter, that happens to be a result set.


you need an out parameter to have a ref cursor. an out parameter can be a ref cursor.

Still have problem with open cursors January 4, 2005 - 9pm Central time zone

Bookmark | Bottom | Top

Reviewer: Ed Fung from Melbourne, Australia

Kasthuri's original query sums up our current situation with our application. We having increasing 
numbers of 'open cursors' as we execute an Oracle Function that returns a REF_CURSOR. We are 
closing the results set, statement and closing the connection in all cases. The close of the 
connection really returns the connection to the connection pool.

Has there been any other reports and/or fixes for this ?

We know that the Open Cursors are real open cursors as we are not using v$open_cursor but the SQL:

select a.sid, a.value, b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and sid = <whatever>

We are running JBoss 3.2.3 and there is a suggestion that the size of the 
prepared-statement-cache-size might help but we need to know if this is an Oracle issue or not..

TIA

Followup January 5, 2005 - 9am Central time zone:

You are not closeing the ref cursors then.  Really -- you are not.  

tell you what, write a simple subroutine that does nothing more than query dual via a ref cursor.

Call it over and over again -- say 1,000,000 times or until it fails.

if it fails, post all of the code (should be teeny tiny) and we'll have a look see for "what is 
wrong"

opened cursors current August 11, 2005 - 10pm Central time zone

Bookmark | Bottom | Top

Reviewer: Bin Wang from Alice Springs, Australia

> v$open_cursor can be misleading 
> A better query is something like:
>     ResultSet rset =
>          stmt.executeQuery
>          ( "select a.value, b.name "+
>              "from v$mystat a, v$statname b "+
>             "where a.statistic# = b.statistic# "+
>               "and a.statistic#= 3");

I find both are misleading. I create a cursor to test.
create or replace procedure test_cursor
is
cursor a
is
select 1 cursor1 from dual;

cursor b
is
select 2 cursor2 from dual;

begin
    open a;
    close a;

    open b;
    close b;

end;
/

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

monitor@MONITOR> select a.value, b.name
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current';

     VALUE NAME
---------- ------------------------------
         1 opened cursors current

monitor@MONITOR> select SQL_TEXT
  2  from v$open_cursor
  3  where sid = 165;


SQL_TEXT
------------------------------------------------------------
select SQL_TEXT from v$open_cursor where sid = 165

monitor@MONITOR> monitor@MONITOR> exec test_cursor;

PL/SQL procedure successfully completed.

monitor@MONITOR> select a.value, b.name
  2  from v$mystat a, v$statname b
  3  where a.statistic# = b.statistic#
  4  and b.name = 'opened cursors current';

     VALUE NAME
---------- ------------------------------
         3 opened cursors current

monitor@MONITOR> select SQL_TEXT
  2  from v$open_cursor
  3  where sid = 165;

SQL_TEXT
------------------------------------------------------------
SELECT 1 cursor1 from dual
SELECT 2 cursor2 from dual
select SQL_TEXT from v$open_cursor where sid = 165

Both v$mystat and v$open_cursor include the closed cashed cursor. So how many cursors really truely 
have opened?

Basically we have a java application which don't close cursor in some of there code. I wonder if it 
is possible to see which cursors are truely open.(A strong requirement than how many they are)

Thanks,
Bin

Followup August 12, 2005 - 8am Central time zone:

you have 3 open.

they are closed in the PLSQL (logical) sense.  They are open in the Oracle sense.


You should be able to identify the plsql cursors easily, they'll have lots of good bind variables 
in them, consistently named :b0, :b1 and so on.

a few questions October 6, 2006 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from India

<quote>
they are closed in the PLSQL (logical) sense.  They are open in the Oracle 
sense.
</quote>

1. Do these cursors, which are closed in the logical sense, also count when oracle looks for the 
currently opened cursors and when ora-1000 error occurs?

2. We use GTTs in our application, and the most common usage is like "INSERT INTO GTT SELECT 1,2 
FROM TABLE"; I see many of these insert statements in v$open_cursor.SQL_TEXT. I also see queries 
like "SELECT CEIL ((p_tot_rec / p_page_rows)) INTO p_tot_pages FROM DUAL;"(This is the PL/SQL Query 
of course, I see "SELECT CEIL ((:B2 / :B1 )) FROM DUAL" in SQL_TEXT).
 

I have run the following queries, one on v$sesstat, the second on v$open_cursor. Both of them give 
the same count. 

SQL> select SUM(a.Value)open_cursors_current
  2    from v$sesstat a, v$statname b
  3   where a.statistic# = b.statistic#
  4     and b.name = 'opened cursors current'
  5     And a.SID In (Select sid
  6                     From V$session
  7                    Where SCHEMANAME = 'abc'
  8                      And MACHINE In ('lin2','lin5'));

OPEN_CURSORS_CURRENT
--------------------
                 157

Select Count(*) From v$open_cursor  Where SID In (Select sid
  2  From V$session
  3  Where SCHEMANAME = 'abc'
  4  And MACHINE In ('lin2','lin5'))
  5  /

  COUNT(*)
----------
       157

(I have used lin2& lin5 because they are the two clustered weblogic linux servers hosting our 
application.)

I have run these queries as you have said that v$open_cursor can contain a few entries of cursors 
which might have already been closed. You also said that v$sesstat is the view that must be queried 
to find out the currently open cursors. I am not trying to dispute this, but since both the counts 
are the same and also the count increases equally whenever I perform any operation in the 
application, I am assuming that both contain same data corresponding to the same cursors in this 
case. 

So, my question is what could be the reason for (all) the implicit cursors & GTT inserts showing up 
in v$open_cursor.SQL_TEXT ?

3. Also, why do I see the refcursors which have been passed as OUT parameters in v$open_cursor. I 
have double checked and that particular resultset in our front end language, Java, is closed. This 
is on both development & production systems. On production,the value of session_cached_cursors is 
set to 20, whereas on development, it is 0. Does connection pooling have a role to play here ?

SQL> select * from V$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Prod
PL/SQL Release 10.1.0.4.0 - Production
CORE    10.1.0.4.0    Production

TNS for Linux: Version 10.1.0.4.0 - Production
NLSRTL Version 10.1.0.4.0 - Production


Thanks as always for your patience. I have learnt countless things from this site!

Followup October 6, 2006 - 9am Central time zone:

1) no, we will close them for real before signaling open cursors exceeded.


2) ARG.  Why why why would you do this:

"SELECT CEIL ((p_tot_rec / 
p_page_rows)) INTO p_tot_pages FROM DUAL;"

when you can just code:  p_tot_pages := ceil(p_tot_rec/p_page_rows); 
??????????????????????????????????????

that is a bug in your code, fix it, code will henceforth run faster, consume less resources. Even 
in 10g when we try to "fix that" behind the covers for you (but cannot fix it 100%)



3) write us a small tiny test program that shows this.  It would

a) connect
b) run a procedure to get a ref cursor
c) process ref cursor
d) close it
e) query v$open_cursor to show it still there.

use scott/tiger and the dept table.

thanks - a few questions October 7, 2006 - 4am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from India

<quote>
2) ARG.  Why why why would you do this:

"SELECT CEIL ((p_tot_rec / 
p_page_rows)) INTO p_tot_pages FROM DUAL;"

when you can just code:  p_tot_pages := ceil(p_tot_rec/p_page_rows); 
??????????????????????????????????????

that is a bug in your code, fix it, code will henceforth run faster, consume 
less resources. Even in 10g when we try to "fix that" behind the covers for you 
(but cannot fix it 100%)
</quote>

Thank you for pointing out the obvious mistake there! It was written by someone else and I just did 
not realise the hidden mistake in the pile of the code. I have changed it (it was used at more than 
one place :) ).

Just curious about this statement though
"Even in 10g when we try to "fix that" behind the covers for you 
(but cannot fix it 100%)"

How does 10g "fix" that? Artificial intellegence ? :)

<quote>
3)write us a small tiny test program that shows this.  
</quote>

This was a cursor leak. Even though it appeared that the cursor was being closed, it wasn't.
It was coded something like this in java 

try
{
   connection  = getConnection();
   csmt           = connection.prepareCall("{CALL proc(?,?)}");
   csmt.setString(1,"aString");
   csmt.registerOutParameter(2,OracleTypes.CURSOR);
   csmt.execute();
   if(someCondition==true)//When this condition fails, the ResultSet object remains null & hence is 
not closed, even though it was opened in the procedure.
   {
    rs = (ResultSet)csmt.getObject(2);
   }
}
catch(Exception e)
{
   //throw new Exception
}
finally
{
  if(rs!=null)//Since it is null, it was not closed
    rs.close();
  if(csmt!=null)
     csmt.close();
  if(connection!=null)
   connection.close();
}

Of course, this is just a snippet of the code. I have changed this entire logic. I apologise for 
posting this question before investigating it thoroughly.

<quote>
use scott/tiger and the dept table.
</quote>

Would have never dumped the whole code. I don't understand why people do that!! You should probably 
include another warning in here saying "Asktom is not responsible for Copyright violation & damage 
to intellectual property arising out of posting your code." or something like that!! 
I can't imagine that people actually put up the same whole code which a company might be trying to 
protect through all the paperwork!! 
And of course, the most obvious fact that anyone else other than those people would not understand 
a word of what is written there!!

Followup October 7, 2006 - 9am Central time zone:

1) "fast dual", no IO's

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

ops$tkyte%ORA9IR2> set linesize 1000
ops$tkyte%ORA9IR2> set autotrace traceonly
ops$tkyte%ORA9IR2> select ceil(5/34) from dual;


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'DUAL'




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
...

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

ops$tkyte%ORA10GR2> set linesize 1000
ops$tkyte%ORA10GR2> set autotrace traceonly
ops$tkyte%ORA10GR2> select ceil(5/34) from dual;


Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          0  consistent gets
...


3) :)  That is why I always ask for a small reproducible test case!!  

9999999999999999 times out of
10000000000000000  I find my own mistake when constructing them.

Thanks a lot October 9, 2006 - 6am Central time zone

Bookmark | Bottom | Top

Reviewer: A reader from India

Just one more question Tom,

Is there a way to find out only the actual open cursors(excluding the implicit cursors) which are 
open currently other than looking at SQL_TEXT column of v$open_cursor? 

Thanks a lot for all the help so far!

Followup October 9, 2006 - 8am Central time zone:

not that I am aware of.

Getting REF CURSORS August 28, 2007 - 11am Central time zone

Bookmark | Bottom | Top

Reviewer: Santo from INDIA

Hi,This inf. is too good.
Can u plz tel me how to retrieve records from REF CURSOR(Weak Type) variable?

Can i take it into Any recordset or collections??

Followup September 4, 2007 - 2pm Central time zone:

"U" isn't available.

PLZ - what do German Postal codes have to do with anything?

A 'weak' ref cursor in a client application is treated exactly the same way a strongly typed one is - there is no difference

Write a Review