Announcement

Collapse
No announcement yet.

5.5.5 Bug 8507644 - ORA-01000 Maximum Open Cursors Exceeded

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • 5.5.5 Bug 8507644 - ORA-01000 Maximum Open Cursors Exceeded

    FYI,

    Using 5.5.5.2 we experienced an "ORA-01000 Maximum Open Cursors Exceeded" error during order integration and were able to trace the problem to the following SQL

    select distinct order_release_gid from order_release where order_base_gid=:1

    After about an hour this lead to an out of memory condition on the app server necessitating a restart.

    We reported this to Oracle and they quickly replied that this is a known issue and provided a patch for bug 8507644 (which we are now testing).

    If you experience a similar cursor leak the following SQL's may be helpful in finding the problem.

    -- Monitor OTM Cursor usage
    select c.user_name, c.sid, count(*), sql.SQL_TEXT, o.object_name Package_name,
    sql.PROGRAM_ID, sql.PROGRAM_LINE# Line#
    from v$open_cursor c, v$sql sql, dba_objects o,
    (select s.sid 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'
    -- More than 50 cursors open in a session
    and a.value > 50 and s.username = 'GLOGDBA') t1
    where c.sql_id = sql.sql_id
    and o.object_id(+) = sql.PROGRAM_ID
    and c.sid = t1.sid
    group by c.user_name, c.sid, sql.SQL_TEXT, o.object_name, sql.PROGRAM_ID, sql.PROGRAM_LINE#
    having count(*) > 20
    order by sid, count(*) desc;

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

    Thanks,
    Alan

  • #2
    Re: 5.5.5 Bug 8507644 - ORA-01000 Maximum Open Cursors Exceeded

    Alan,

    Thanks a lot for sharing this. I ran into a client recently who experienced the same issue. Much appreciated!

    --Chris

    Comment


    • #3
      Re: 5.5.5 Bug 8507644 - ORA-01000 Maximum Open Cursors Exceeded

      You are lucky.
      I spent almost two weeks convincing Oracle that this is a bug.
      According to Development, the bug was tied around the mediator and the OBModFullImpact did not close the sql query.
      For Version 5.5 CU5: it is fixed in RU5
      For Version 6.0: it will be fixed in RU2
      --
      Joseph Liang
      MavenWire APAC
      http://www.mavenwire.com/

      Comment


      • #4
        Re: 5.5.5 Bug 8507644 - ORA-01000 Maximum Open Cursors Exceeded

        There is another similar bug relates DirectSqlUpdate
        BUG 8672390 - Client with high integration volume using a lot of direct SQL updates encountered Ora-01000: maximum open cursors exceeded errors during integration
        It is fixed in OTM 5.5 CU5 RU6 patchset.
        --
        Joseph Liang
        MavenWire APAC
        http://www.mavenwire.com/

        Comment


        • #5
          Re: 5.5.5 Bug 8507644 - ORA-01000 Maximum Open Cursors Exceeded

          According to Metalink Note 866873.1 - "ORA-01000: Maximum Open Cursors Exceeded" Errors Are Occurring During Integration
          the issue affects OTM Version: 5.5.04 to 6.1
          Version 5.5 CU4 - A Patch is available upon request, please open a MetaLink SR for a patch for Bug 8668288
          Version 5.5 CU5 - Download and Install RU6 available on MetaLink (includes fix for Bug 8672390)
          Version 5.5 CU6 - Download and Install RU1 (includes fix for Bug 8672391) - Approximate Availability is End of October, 2009.
          Version 6.0 - Download and Install RU2 (includes fix for Bug 8672392 - Approximate Availability is End of August, 2009
          Version 6.0 CU1 - CU1 includes fix for Bug 8672394 - Approximate Availability is End of 2009
          --
          Joseph Liang
          MavenWire APAC
          http://www.mavenwire.com/

          Comment


          • #6
            Re: 5.5.5 Bug 8507644 - ORA-01000 Maximum Open Cursors Exceeded

            I just encountered this issue with 5.5 and I just upgraded to CU6RU3.

            Has anyone else seen this post CU5 patch?

            I uploaded the glog.exception log showing the open cursors listed out.

            Rick

            Comment

            Working...
            X