Monitoring Open and Cached Cursors

Natalka Roshak's picture
articles: 

Just about every DBA has had to deal with ora-1000 errors, "Maximum open cursors exceeded." This article will discuss initialization parameters that affect open cursors, the difference between open and cached cursors, closing cursors, and monitoring open and cached cursors.

Open cursors

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. web applications that have dozens to hundreds of users sharing a pool of sessions. Tom Kyte recommends setting it around 1000.

Session cached cursors

There are two main initialization parameters that affect cursors, and many folks get them confused. One is OPEN_CURSORS, and the other is SESSION_CACHED_CURSORS.

SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have. You can set SESSION_CACHED_CURSORS to higher than OPEN_CURSORS, lower than OPEN_CURSORS, or anywhere in between. This parameter has no effect on ora-1000's or on the number of cursors a session will have open. Conversely, OPEN_CURSORS has no effect on the number of cursors cached. There's no relationship between the two parameters.

If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session. (Your cursors will still be cached in the shared pool, but your session will have to find them there.) If it is set, then when a parse request is issued, Oracle checks the library cache to see whether more than 3 parse requests have been issued for that statement. If so, Oracle moves the session cursor associated with that statement into the session cursor cache. Subsequent parse requests for that statement by the same session are then filled from the session cursor cache, thus avoiding even a soft parse. (Technically, a parse can't be completely avoided; a "softer" soft parse is done that's faster and requires less CPU.)

In the session cursor cache, Oracle manages the cached cursors using a LRU list. Once more than SESSION_CACHED_CURSORS closed cursors are cached, Oracle starts dropping cached cursors off the LRU end of the list whenever it needs to make room to cache a new cursor.

Why cache cursors?

The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times. This is especially so for applications like Oracle Forms applications, where switching from one form to another will close all the session cursors opened for the first form. Switching back then opens identical cursors. So caching cursors by session really cuts down on reparsing.

There's another advantage, though. Since a session doesn't have to go looking in the library cache for previously parsed SQL, caching cursors by session results in less use of the library cache and shared pool latches. These are often points of contention for busy OLTP systems. Cutting down on latch use cuts down on latch waits, providing not only an increase in speed but an increase in scalability.

Monitoring open cursors

I believe a lot of the confusion about open cursors vs. cached cursors comes from the names of the Oracle dynamic performance views used to monitor them. v$open_cursor shows cached cursors, not currently open cursors, by session. If you're wondering how many cursors a session has open, don't look in v$open_cursor. It shows the cursors in the session cursor cache for each session, not cursors that are actually open.

To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session:

--total cursors open, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current';

If you're running several N-tiered applications with multiple webservers, you may find it useful to monitor open cursors by username and machine:

--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur, 
s.username, s.machine
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current' 
group by s.username, s.machine
order by 1 desc;

Tuning OPEN_CURSORS

The best advice for tuning OPEN_CURSORS is not to tune it. Set it high enough that you won't have to worry about it. If your sessions are running close to the limit you've set for OPEN_CURSORS, raise it. Your goal in tuning this parameter is to set it high enough that you never get an ora-1000 during normal operations.

If you set OPEN_CURSORS to a high value, this doesn't mean that every session will have that number of cursors open. Cursors are opened on an as-needed basis. And if one of your applications has a cursor leak, it will eventually show up even with OPEN_CURSORS set high.

To see if you've set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS.

SQL> select max(a.value) as highest_open_cur, p.value as max_open_cur
  2> from v$sesstat a, v$statname b, v$parameter p
  3> where a.statistic# = b.statistic# 
  4> and b.name = 'opened cursors current'
  5> and p.name= 'open_cursors'
  6> group by p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ------------
            1953         2500

After you've increased the value of OPEN_CURSORS, keep an eye on v$sesstat to see if opened cursors current keeps increasing for any of your sessions. If you have an application session whose opened cursors current always increases to catch up with OPEN_CURSORS, then you've likely got a cursor leak in your application code: your application is opening cursors and not closing them when it's done.

There is nothing you, as a DBA, can do to fix a cursor leak. The application developers need to go through the code, find the cursors that are being left open, and close them. As a stopgap, the most you can do is raise OPEN_CURSORS very high and schedule times when all the application sessions will be closed and reopened (eg. by kicking the webserver).

How not to tell if you're closing all your cursors

Frustratingly for developers, the session statistic 'currently open cursors' can include some cursors that the application has closed. When application code calls for a cursor to be closed, Oracle actually marks the cursor as "closeable". The cursor may not actually be closed until Oracle needs the space for another cursor.

So it's not possible to test to see if a complex application is closing all its cursors by starting a session, running a test, and then checking to see if currently open cursors has gone down to 1. Even if the application is closing all its cursors properly, currently open cursors may report that some "closeable" cursors are still open.

One way for application developers to tell if an application is closing all its cursors is to do a single test run, on a dedicated development box, while monitoring "opened cursors cumulative" in v$sesstat for the session that's running the test. Then set OPEN_CURSORS to a value a little bit higher than the peak cursors open during your test run, start a new session, and run through multiple iterations of the same test run. If your application still has a cursor leak, you will see the value of OPEN_CURSORS going up, and you may hit an ORA-1000 after a reasonable number of iterations. (Don't set OPEN_CURSORS too low or it may be used up by recursive SQL; if your single test run opens very few cursors, consider making your test run longer rather than setting OPEN_CURSORS unreasonably low.)

Monitoring the session cursor cache

v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.

--session cached cursors, by session
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' ;

You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID, and includes the first few characters of the statement and the sql_id, so you can actually tell what the cursors are for.

select c.user_name, c.sid, sql.sql_text
from v$open_cursor c, v$sql sql
where c.sql_id=sql.sql_id  -- for 9i and earlier use: c.address=sql.address
and c.sid=&sid
;

Tuning SESSION_CACHED_CURSORS

If you choose to use SESSION_CACHED_CURSORS to help out an application that is continually closing and reopening cursors, you can monitor its effectiveness via two more statistics in v$sesstat. The statistic "session cursor cache hits" reflects the number of times that a statement the session sent for parsing was found in the session cursor cache, meaning it didn't have to be reparsed and your session didn't have to search through the library cache for it. You can compare this to the statistic "parse count (total)"; subtract "session cursor cache hits" from "parse count (total)" to see the number of parses that actually occurred.

SQL> select cach.value cache_hits, prs.value all_parses, 
  2> prs.value-cach.value sess_cur_cache_not_used
  3> from v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
  4> where cach.statistic# = nm1.statistic#  
  5> and nm1.name = 'session cursor cache hits' 
  6> and prs.statistic#=nm2.statistic#
  7> and nm2.name= 'parse count (total)'
  8> and cach.sid= &sid and prs.sid= cach.sid ;

Enter value for sid: 947
old   8: and cach.sid= &sid and prs.sid= cach.sid
new   8: and cach.sid= 947 and prs.sid= cach.sid

CACHE_HITS ALL_PARSES SESS_CUR_CACHE_NOT_USED
---------- ---------- -----------------------
       106        210                     104

Monitor this in concurrence with the session cursor cache count.

--session cached cursors, for a given SID, compared to max
select a.value curr_cached, p.value max_cached, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s, v$parameter2 p
where a.statistic# = b.statistic#  and s.sid=a.sid and a.sid=&sid
and p.name='session_cached_cursors'
and b.name = 'session cursor cache count' ;

If the session cursor cache count is maxed out, session_cursor_cache_hits is low compared to all parses, and you suspect that the application is re-submitting the same queries for parsing repeatedly, then increasing SESSION_CURSOR_CACHE_COUNT may help with latch contention and give a slight boost to performance. Note that if your application is not resubmitting the same queries for parsing repeatedly, then session_cursor_cache_hits will be low and the session cursor cache count may be maxed out, but caching cursors by session won't help at all. For example, if your application is using a lot of unsharable SQL, raising this parameter won't get you anything.

Conclusion


We've covered the difference between open cursors and session cached cursors, their initialization parameters, and how to monitor and tune them.

About the author


Natalka Roshak is a senior Oracle and Sybase database administrator, analyst, and architect. She is based in Kingston, Ontario. More of her scripts and tips can be found in her online DBA toolkit at http://toolkit.rdbms-insight.com/.

Comments

Sometimes you will need to get a full stack trace of the problem by setting a trace for ORA-1000 as in:

alter system set events = '1000 trace name ERRORSTACK level 3'

or...
alter session set events = '1000 trace name ERRORSTACK level 3'

Then set open_cursors to a low enough number to catch the open cursor voilator in your net.

Hi Orjan,

Good tip! Thanks for posting it.

-Natalka

Nice article, but for me (personally), I prefer to put the open_cursor somewhere between 50 and 80. I don't actually, let the developers flood the cursor pool. If the code is executing more than 80 cursors, hmmmmm, time to change the logic of your code... ;)

Hi AKV,

Nice to hear your developers are so good at closing their cursors! Someday, however, you may find yourself administering a DB that's running an already developed application... in that case, it's hard to justify a code rewrite just to keep open_cursors below 80.

-Natalka

Thanks Natalka. I've been searching the web for a good explanation of this. My own testing verfies the "closeable" issue. Testing with a small Java program using JDBC, I couldn't figure out why the cursors weren't being closed until the connection was closed. Now it all makes sense. Even Tom Kyte has some confusing posts on this subject.

In my experience, the new Java/JDBC programmers are so used to Java doing all the cleanup, it's difficult to get them to understand about closing some of their JDBC objects explicitly. I was having a hard time creating a test case that demonstrated this problem so I could show them exactly what was happening.

So, V$OPEN_CURSOR doesn't contain info about open cursors and the statistic "currently open cursors' counts some cursors that are actually are their way to being closed. Hmmmmm....it's just so....ORACLE!

Appreciate the explanation.

Joe

I suggest you follow this up with Oracle Support. And, please let me know what the resolution is! casino

Hi Natalka,

Thanks for the tips... but i am looking for one specific scenerio in cursors, please clarify the same....

Lets say i have a cursor which takes in 2 parameters.

We open the cursor and in the loop for first parameters we insert the data in interface table.
so when the 2nd time the loop begins with the new parameter value , the cursor logic has a chck on this interface table, and retrieves the value based on that...

So my doubt is will it validate the data which were inserted by first parameter passed.

Or is it that the cursor loop first will fetch the data for ech parameter in the loop and then insert it..

Every thing is happening in same session.. so ideally it should validate the interface table records which were inserted by the first loop....

I tested the scenerio and chcked that , the 2nd loop is also inserting the records in the interface without validating the interface table...

Please let me know.. if this is standard functionality

Kind Regards
Anil

Hi Natalka,

Thanx for the good explanation. I have not yet read such information from any oracle source.

Regards,
Suhas

Natalka,

I executed the SQL provided by you to monitor open_cursors in our databases. In some of our databases the HIGHEST_OPEN_CUR (17542) is greater than the OPEN_CURSORS limit (300) set in the init.ora. And users do not get any errors. Any idea and suggestions?

Thanks in advance - SQL and the result below:

Ping

select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name= 'open_cursors'
group by p.value;

HIGHEST_OPEN_CUR MAX_OPEN_CUR
---------------- ----------------------------------------
           17542 300

Hi, Ping,

I checked on Metalink's forums, and saw that a few other users have run into this problem, but I didn't see any solutions posted from Oracle Support. I'm not sure what version of Oracle you're using, but I wonder if what you're seeing is related to this bug:
https://metalink.oracle.com/metalink/plsql/f?p=130:3:9915976918476316875::::p3_database_id,p3_docid,p3_show_header,p3_show_help,p3_black_frame,p3_font:NOT,3926058.8,1,0,1,helvetica
(Bug 3926058 V$SYSSTAT "opened cursors current" can be incorrect).

My understanding is that the value of "opened cursors current" for each session in v$sesstat should definitely be <= OPENED_CURSORS . I suggest you follow this up with Oracle Support. And, please let me know what the resolution is!

"
Hi, Ping,

I checked on Metalink's forums, and saw that a few other users have run into this problem, but I didn't see any solutions posted from Oracle Support. I'm not sure what version of Oracle you're using, but I wonder if what you're seeing is related to this bug:
https://metalink.oracle.com/metalink/plsql/f?p=130:3:9915976918476316875::::p3_database_id,p3_docid,p3_show_header,p3_show_help,p3_black_frame,p3_font:NOT,3926058.8,1,0,1,helvetica claro
(Bug 3926058 V$SYSSTAT "opened cursors current" can be incorrect).

My understanding is that the value of "opened cursors current" for each session in v$sesstat should definitely be <= OPENED_CURSORS . I suggest you follow this up with Oracle Support. And, please let me know what the resolution is!"

Nice hint too, thanks!

Hi Natalka,

I opend a tar with Oracle, their reply is that is due to the bug 3472564. The resolution is upgrade.

The bug 3472564:
The problem typically occurs when job queue processes are
being used and either:
RESOURCE_LIMIT is set to TRUE
or
The fix for bug 3472564 ("CPU used by this session" wrong when using job queue processes) is
installed.

This problem has been fixed in the following versions:
* 9.2.0.8 (Server Patch Set)
* 10.1.0.5 (Server Patch Set)
* 10.2.0.1 (Base Release)

Thanks for your help!
Ping

But I have to say that I think that when session cached cursors is set to 0, Oracle will cache 50
betsson
casino

Hi Natalka,

Thanks for this great article.

Would you know a way to find out how much memory a cursor is holding on? The statement cache of the JDBC drivers keep cursors opened in the database in order to reuse them quickly but it's hard to tell how much memory one particular cursor is using. Is there a way? Thanks!

--
Jean

I'm sory,

But I have to say that I think that when session cached cursors is set to 0, Oracle will cache 50.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:17989406187750

Hi zemerli,

Yes, that is what "The default is value for OPEN_CURSORS is 50" means.

-Natalka

Dear Natalka,

While on my system:

SQL> show parameter session_cached_cursors

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
session_cached_cursors               integer 0

And,

SQL> select count(*) from v$open_cursor;
  COUNT(*)
----------
      6166

Why are there so many cached cursors?

Even as mentioned above, "when session cached cursors is set to 0, Oracle will cache 50."

The following output is still confusing for the value is greater than 50(more close to "current open cursor"):

SQL> select count(*) from v$open_cursor group by sid,saddr;
  COUNT(*)
----------
         1
       217
       501
       501
         1
       501
        45
         1
         1
        29
       114
...

Although the default value of OPEN_CURSORS is 50, when i create a database without specifying this parameter, it sets OPEN_CURSORS to 300. Why is it not taking 50 (the default value)?

PS: I'm using 10gR2 on SUSE Linux 10.

What does it say in your init.ora ? OPEN_CURSORS defaults to 50 if it is not set at all in your init.ora or spfile. However, Oracle comes presupplied with a default INIT.ORA, which may be setting OPEN_CURSORS to 300.

Great article!

I do agree with AKV's comment that it is better to force developers to close their cursors rather than having a DBA trying to resolve the problem by tweaking Oracle.

David Litchfield recently made a very strong case that dangling cursors are a security concern; procedures/packages with open cursors may leak (potentially sensitive) information in an unexpected manner. Not only can attackers reuse the old cursor and check what it would have returned, you can also change the value of any bind variable used by it. Not only may you be able to reuse SELECT statements, but also INSERT statements. So dangling cursors could be a great problem for an application with a security model based on AUTHID DEFINER.

The article is available at: cursor-snarfing.pdf

Thanks for the explanation.
But then why don't you change your article?

"If SESSION_CACHED_CURSORS is not set, it defaults to 0 and no cursors will be cached for your session" Is not correct.
I believe if it is set to 1 this is the case. 0 results back in the default (30 or 50?).

Another Question:
Oracle can close cursors automatic. If cursors are opened in a procedure. The procedure ends then all cursors are closed.
A cursor opened in a package remains open the whole session I suppose so can be a thread.
What can be other theats?

In other words: what are possible open cursors (which one can't oracle close automatic?)

thanks a lot
Hank, NJ

Hi Natalka, good information, thanks.

In the section, 'Monitoring the session cursor cache"
======
v$sesstat also provides a statistic to monitor the number of cursors each session has in its session cursor cache.
You can also see directly what is in the session cursor cache by querying v$open_cursor. v$open_cursor lists session cached cursors by SID,
======

What I understood from above is that they report similar/related information, one tells you the number of cached cursors and the other shows the details of the cached cursors.

So, am thinking that the value from v$sesstat should match the # of rows from v$open_cursor, if I were to follow the above description/explanation. Please correct me, if am wrong in my analysis/understanding.
===================
SQL> show parameter session_cached_cursors
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
session_cached_cursors integer 100

SQL> select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and s.sid=2043
and b.name = 'session cursor cache count'
order by 1 desc;

VALUE USERNAME SID SERIAL#
---------- ------------------------------ ---------- ----------
100 LIVE_WRITE 2043 21247

SQL> select count(*)
from v$open_cursor c, v$sql sql
where c.address=sql.address
and c.sid=2043

COUNT(*)
----------
180
=======================

Why does v$open_cursor have 180 cached cursors whereas I was expecting to see 100 (which coincides with the value from v$sesstat).

Am running 9.2.0.7 on Solaris E2900.

Thanks

Hello Natalka

super explanation of how to handle cursor issues.

found this site today whilst researching for a client

keep up the good work

hi Natalka,

I am getting the error of ORA-01000-Maximun cursor opened.
how do i correct the error
please respond

How to calculate Closeable cursors count?
How to query that?

Dear All,

We have a strange issue for a common error. We are running a PLSQL Package and we are hitting "ORA-01000: maximum open cursors exceeded error". When I checked the gv$open-cursor, I found that 90% of the open cursors has a sql termed "SELECT NULL FROM DUAL;"

Kindly please let us know how to move forward on this issue? Is this a cursor leak in the package or is there anything that needs to be set in the DB side?

Regards,
DK

Thanks for great article.

You mentioned that "v$open_cursor shows cached cursors, not currently open cursors, by session".
This is true. However, it also contains all currently opened cursors.

I have a database with OPEN_CURSORS set to 300 (per session).
My application suffered from ORA-01000 due to cursor leak.
The application is using dozens of JAVA classes that executes hundreds of different SQL statements.
Reviewing the code did not help.

I have used the following query to identify the problematic session:
SELECT a.value, a.sid, b.name
FROM v$sesstat a, v$statname b
WHERE a.statistic# = b.statistic# and a.statistic#= 3
ORDER BY 1 desc

Then I used the following query to identify possible leaking cursors:
SELECT SQL_ID, max(SQL_TEXT), count(*)
FROM v$open_cursor
WHERE sid = /* the sid identified by the previous query */
GROUP BY SQL_ID
HAVING COUNT(*) > 1
ORDER BY 3 DESC

I am not an Oracle expert. So, my findings are:
1. Whenever a JAVA (JDBC) application uses Statement (or PreparedStatement or CallableStatement), one row is added to v$open_cursor.
2. The row added to v$open_cursor will not be deleted unless using Statement.close or Connection.close.
3. Closed cursors might be cached in v$open_cursor according to SESSION_CACHED_CURSORS (in my case the problematic session had 350 rows in v$open_cursor while only 299 of then where still open).
4. When using JAVA ConnentionPool, invoking Connection.close will only return the connection to pool. The session plus all its opened cursors will stay open.
5. When application that does not close PreparedStatements is called again and again, multiple rows with same SQL_ID will be piled up in v$open_cursor.

It would be great if an expert can validate my findings and formulate them as a more deterministic procedure helping to identify application's cursor leaks.

Thank you, ehud.eshet. Your advice was very helpful in identifying the cursor leaks in my application.

The surprise for me was that CallableStatement's must be closed even if the stored procedure is not returning a cursor. In my case, the stored procedures were merely inserting a row or deleting one or more rows.

You may not have been an ORACLE expert in March 2010 but you may well be now.

In any case, your observations seem to be correct.

Thanks again,

Martin

I have an application that uses quite a few different users to connect and they are using .NET to connect using connection pooling. The problem I have is that Oracle keeps telling me in the ADDM reports to increase the SESSION_CACHED_CURSORS and OPEN_CURSORS parameters. They are set pretty high (7000, 500) but there is not enough repetition of queries in a session I believe because of connection pooling. I think that the sessions expire fast enough that only a handful of queries are executed 3+ times in a session so there is a lot of re-parsing. Is this a common problem? Should I be pinning some queries to cache to increase the number of queries that are re-used? Should I lower the number of open connections that the front end make so that more SQL is run 3+ times?

Hello Natalka,
Thank you very much for good explanation.
I have a similar question which Mr.Huuub has asked.
As Huuub says
'Oracle can close cursors automatic.
If cursors are opened in a procedure. The procedure ends then all cursors are closed.
A cursor opened in a package remains open the whole session' (I think ref cursors)
Also most of the cases in the application development cursor FOR loops are used which are equipped with automatic closing of the cursor.
Then where is the chance for cursor leakage.
May I know the difference between a connection leak from the application and cursor leak from the database.

Regards,
Sreenadh

Quite often, when ORA-01000 is encountered, a blame game begins between DBAs and developers, with each producing statistics, forum messages etc to buttress their point. However, the need is really to help developers identify root cause of the high cursor usage as well as help the DBAs set value of open_cursors to a reasonable number, based on usage.
This write-up is helpful in the effort,
Thank You.

Hello Natalka,

Thanks for you explanation about cursors.

I need one clarification on the Open Cursors and Sessions. As we are setting up the Open Cursors at the session level, all the open cursor count we need to consider by session or db user level. If the session got close all the open cursors will get close or it will remain open until a db restart.

I'm facing an issue with the Open Cursors limit like I'm opening a cursor explicitly and not closing it so this cursor will get close when ever the session which is using this cursor got ended or it remain open and it will considered as open cursor only.

SELECT NAME,VALUE,DESCRIPTION FROM v$parameter where name like '%_cursors'