Resolving Open Cursor Errors

You are frequently getting the Maximum Open Cursors exceeded error, and you want to resolve the error.

One of the first things you need to do when you receive the ORA-01000: “maximum open cursors
exceeded” error is to check the value of the initialization parameter open_cursors. You can view the
current limit for open cursors by issuing the following command:

SQL> show parameter open_cursors

The parameter OPEN_CURSORS sets the maximum number of cursors a session can have open at once.You specify this parameter to control the number of open cursors. Keeping the parameter’s value too low will result in a session receiving the ORA-01000 error. There’s no harm in specifying a very large value for the OPEN_CURSORS parameter (unless you expect all sessions to simultaneously max out their cursors, which is unlikely), so you can usually resolve cursor-related errors simply by raising the parameter value to a large number. However, you may sometimes find that raising the value of the open_cursors parameter doesn’t “fix” the problem. In such cases, investigate which processes are using the open cursors by issuing the following query:

SQL> select a.value, s.username,s.sid,s.serial#,s.program,s.inst_id
from gv$sesstat a,gv$statname b,gv$session s
where a.statistic# = b.statistic# and 
and b.name='opened cursors current';

The GV$OPEN_CURSOR (or the V$OPEN_CURSOR) view shows all the cursors that each user session has currently opened and parsed, or cached. You can issue the following query to identify the sessions with a high number of opened and parsed or cached cursors.

SQL> select saddr, sid, user_name, address,hash_value,sql_id, sql_text
from gv$open_cursor
where sid in
(select sid from v$open_cursor
group by sid having count(*) > &threshold);

The query lists all sessions with an open cursor count greater than the threshold you specify. This way, you can limit the query’s output and focus just on the sessions that have opened, parsed, or cached a large number of cursors.

You can get the actual SQL code and the open cursor count for a specific session by issuing the
following query:

SQl> select sql_id,substr(sql_text,1,50) sql_text, count(*)
from gv$open_cursor where sid=81
group by sql_id,substr(sql_text,1,50)
order by sql_id;

The output shows the SQL code for all open cursors in the session with the SID 81. You can examine
all SQL statements with a high open cursor count, to see why the session was keeping a large number of cursors open.

How It Works
If your application is not closing open cursors, then setting the OPEN_CURSORS parameter to a higher value won’t really help you. You may momentarily resolve the issue, but you’re likely to run into the same issue a little later. If the application layer never closes the ref cursors created by the PL/SQL code, the database will simply hang on to the server resources for the used cursors. You must fix the application logic so it closes the cursors—the problem isn’t really in the database. If you’re using a Java application deployed on an application server such as the Oracle WebLogic Server, the WebLogic Server’s JDBC connection pools provide open database connections for applications. Any prepared statements in each of these connections will use a cursor. Multiple application server instances and multiple JDBC connection pools will mean that the database needs to support all the cursors. If multiple requests share the same session ID, the open cursor problem may be due to implicit cursors. The only solution then is to close the connection after each request. A cursor leak is when the database opens cursors but doesn’t close them. You can run a 10046 trace for a session to find out if it’s closing its cursors:

SQL> alter session set events '10046 trace name context forever, level 12';

If you notice that the same SQL statement is associated with different cursors, it means that the application isn’t closing its cursors. If the application doesn’t close its cursors after opening them, Oracle assigns different cursor numbers for the next SQL statement it executes. If the cursor is closed, instead, Oracle will re-use the same cursor number for the next cursor it assigns. Thus, if you see the item PARSING IN CURSOR #nnnn progressively increase in the output for the 10046 trace, it means that the application is not closing the cursors. Note that while leaving cursors open may be due to a faulty application design, developers may also intentionally leave cursors open to reduce soft parsing, or when they use the session cursor cache. You can use the SESSION_CACHED_CURSORS initialization parameter to set the maximum number of cached closed cursors for each session. The default setting is 50. You can use this parameter to prevent a session from opening an excessive number of cursors, thereby filling the library cache or forcing excessive hard parses. Repeated parse calls for a SQL statement leads Oracle to move the session cursor for that statement into the session cursor cache. The database satisfies subsequent parse calls by using the cached cursor instead of re-opening the cursor.
When you re-execute a SQL statement, Oracle will first try to find a parsed version of that statement in the shared pool—if it finds the parsed version in the shared pool, a soft parse occurs. Oracle is forced to perform the much more expensive hard parse if it doesn’t find the parsed version of the statement in the shared pool. While a soft parse is much less expensive than a hard parse, a large number of soft parses can affect performance, because they do require CPU usage and library cache latches. To reduce the number of soft parses, Oracle caches the recent closed cursors of each session in a local session cache for that session—Oracle stores any cursor for which a minimum of three parse calls were made, thus avoiding having to cache every single session cursor, which will fill up the cursor cache.
The default value of 50 for the SESSION_CACHED_CURSORS initialization parameter may be too low for many databases. You can check if the database is bumping against the maximum limit for session cached cursors by issuing the following statement:

SQL> select max(value) from v$sesstat
where statistic# in (select statistic# from v$statname
where name = 'session cursor cache count');
MAX(VALUE)
----------
49
SQL>

The query shows the maximum number of session cursors that have been cached in the past. Since this number (49) is virtually the same as the default value (or the value you’ve set) for the SESSION_CACHED_CURSORS parameter, you must set the parameter's value to a larger number. Session cursor caches use the shared pool. If you’re using automatic memory management, there’s nothing for you to do after you reset the SESSION_CACHED_CURSORS parameter—the database will bump up the shared pool size if necessary. You can find out how many cursors each session has in its session cursor cache by issuing the following query:

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 b.name='session cursor cache count';