Resolving ORA-01000 open cursor error

We are frequently getting the Maximum Open Cursors exceeded error, and we want to resolve the error.
Solution:

One of the first things we need to do when we receive the ORA-01000: “maximum open cursors exceeded” error is to check the value of the initialization parameter open_cursors.

We can view the current limit for open cursors by issuing the following command:

SQL> sho parameter open_cursors
NAME TYPE VALUE
------------------------------------ ----------- ---------
open_cursors integer 300

The parameter OPEN_CURSORS sets the maximum number of cursors a session can have open at once. We 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 we expect all sessions to simultaneously max out their cursors, which is unlikely), so we can usually resolve cursor-related errors simply by raising the parameter value
to a large number. However, we 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 s.sid=a.sid
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. We 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 we specify. This way, we can limit the query’s output and focus just on the sessions that have opened, parsed, or cached a large number of cursors.

we 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. we 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 wer application is not closing open cursors, then setting the OPEN_CURSORS parameter to a higher value won’t really help we. we may momentarily resolve the issue, but we’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. we must fix the application logic so it closes the cursors—the problem isn’t really in the database. If we’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. we 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 we 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 we 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.

we 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. we 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 we 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. we can check if the database is bumping against the maximum limit for sessioncached cursors by issuing the following statement:

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

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 we’ve set) for the SESSION_CACHED_CURSORS parameter, we must set the parameter's value to a larger number. Session cursor caches use the shared pool. If we’re using automatic memory management, there’s nothing for we to do after we reset the SESSION_CACHED_CURSORS parameter—the database will bump up the shared pool size if necessary. we 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#
2 from v$sesstat a, v$statname b,v$session s
3 where a.statistic#=b.statistic# and s.sid=a.sid
4* and b.name='session cursor cache count';