5 Replies Latest reply: Jun 10, 2007 11:48 PM by 561782 RSS

    maximum open cursor exceeded

    532564
      Hi Apps dba,

      Recently we often get the complaints that the development/end users get the maximum open cursor exceeded error.
      I checked our database configuration:

      SQL> show parameters cursor

      NAME TYPE VALUE
      ------------------------------------ ----------- ------------------------------
      cursor_sharing string EXACT
      cursor_space_for_time boolean FALSE
      open_cursors integer 600
      session_cached_cursors integer 200


      I am wondering how this configuration is. They are already big enough or too few.

      Your input will be appreciated.

      Thanks,

      Lily
        • 1. Re: maximum open cursor exceeded
          Hussein Sawwan-Oracle
          What is the version of (Apps/DB/OS) you are running?
          • 2. Re: maximum open cursor exceeded
            532564
            11.5.10.2
            • 3. Re: maximum open cursor exceeded
              mdtaylor
              Open Cursors is a per session parameter. It does not matter if you have 600 or 60,000 open cursors in the instance as long as no single session has more than 600 based on your current setting. If you have a lot of long running processes that are hitting this limit and erroring out, then increase it to 2000 or 4000. I have had to set it to 6000 in big production order management systems a couple of years back when the code was a little sloppier than it is today.

              Increasing open cursors is generally considered required when running suboptimal code. If the code is Oracle's, you can create a TAR, or increase the value. If it is your own, you can tune/redesign the process, or increase the value. Most production environments have a tight schedule where many processes depend on successful completion of earlier stages, so we usually end up increasing the value to get production flowing, then tune the process at your leisure.
              • 4. Re: maximum open cursor exceeded
                Hussein Sawwan-Oracle
                In addition to the above, SESSION_CACHED_CURSORS has no effect on ORA-1000 maximum open cursors exceeded.

                You need to tune OPEN_CURSORS to avoid this error, just set it high enough so you will not get ORA-1000 error but you need to consider the following two issues:

                Bug 3221125 - Very slow import with open_cursors = 100000
                Bug 3177461 - Catpatch.sql becomes very slow when open_cursors set to a big value (i.e. 5120000)

                The range of values of OPEN_CURSORS can vary from 1 to 4294967295 (4 GB - 1)

                You can use the following query to monitor OPEN_CURSORS:

                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 = 'opened cursors current';

                You may also try the following query to monitor open cursors by username and machine:

                SQL> 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;

                One more thing, do not forget to ask your developers to go thru their code and close all the cursors which are being left open.
                • 5. Re: maximum open cursor exceeded
                  561782
                  Hi,

                  Although increasing open_cursor parameter do no harm. but if it is a dev instance , I suggest to track the program which are causing this.. and pinpoint the sql which require to open more then open_cursor (600 in your case)..
                  if we do not tune code later it can pose serious performance problems.