Thursday, February 25, 2016

Solving Maximum Open Cursors in Oracle


o   Exception
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded ORA-00604: error occurred at recursive SQL level 1

o    Introduction
For every SQL statement execution in Oracle Database, certain area in the memory is allocated. Oracle PL/SQL allows you to name this area. This private SQL area is called context area or cursor. These cursors take up space in the shared pool, which is essential memory component of Oracle Database, specifically in the library cache. To keep a renegade session from filling up the library cache or clogging the CPU with millions of parse requests, the OPEN_CURSORS database parameter must be set to limit the cursors.
The OPEN_CURSORS parameter sets the maximum number of cursors that each session can have open, per session. For example, if the value of OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.

o    Troubleshooting 
1.    Get the session id having high number of open cursors count using following SQL.
  SELECT s.username,
         s.sid,
         s.serial#,
         a.VALUE open_cursor_count
    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' AND s.username IS NOT NULL
ORDER BY a.VALUE DESC;

2.    Get the SQL which opens most cursors for the top session in above query.
  SELECT a.sql_id, b.sql_fulltext, a.open_cursors
    FROM (  SELECT sql_id, COUNT (1) open_cursors
              FROM v$open_cursor
             WHERE sid = 913
          GROUP BY sql_id) a,
         gv$sqlarea b
   WHERE a.sql_id = b.sql_id
ORDER BY a.open_cursors DESC;
            We will get the top queries which are opened but not closed. Usually implicit cursors are closed immediately by Oracle itself. But the cursors which are opened explicitly through PL/SQL or java Statement object should be closed by program itself. So we need to verify that we are closing the SQL statements from the above query output properly.

3.    Also monitor the current higher value and configured value through following query.
    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;
                If the configured value is not adequate then increase the open_cursor_count with following SQL statement.

ALTER SYSTEM SET open_cursors=1500;

No comments:

Post a Comment