This discussion is archived
1 2 Previous Next 26 Replies Latest reply: Oct 16, 2012 12:33 AM by 916079 Go to original post RSS
  • 15. Re: maximum open cursors exceeded
    916079 Newbie
    Currently Being Moderated
    HI,

    The v$session table exist but fields like blocking_session,prev_sql_id,event,second_in_wait are not available in 9i. So thats the reason i had said that the query is not working.

    Regards,
    CV
  • 16. Re: maximum open cursors exceeded
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    Then Use the First query and get SID from it , and Do you own query searching On SID it will be simple . as the other post said even you increase open_course maybe you will have same issue so check with developer team about this issue .
  • 17. Re: maximum open cursors exceeded
    Another_user Explorer
    Currently Being Moderated
    You can increase the parameter open_cursors to a few thousand in prod. That may help but not fix your issue. I would keep the parm lower in dev to help them identify the problem. You can possibly also help mitigate the leak by cycling your connection pool periodically.

    Run a query like this to help find the problem statements:

    select count(*), c.sql_text
    from v$sesstat a, v$statname b, v$open_cursor c, v$session d
    where a.statistic# = b.statistic#
    and b.name = 'opened cursors current' and
    c.sid = a.sid and
    d.sid = a.sid and
    d.username = <YOUR_APP_USER>
    group by c.sql_text having count(*) > 50
    order by 1 desc;


    This is likely from the developers not writing JDBC correctly. They are leaving prepared statements and result sets open.
  • 18. Re: maximum open cursors exceeded
    916079 Newbie
    Currently Being Moderated
    Hi,

    I had checked with the developers team but they say that do not have any result open-set open. I had also checked the status of the SID which shows that it is INACTIVE and also checked for the locks(TADDR) on the SID but didnt find any any. But still the count for the cursors is increasing for that SID. So could you provide any other way to overcome it.

    Thanks & Regards,
    CV
  • 19. Re: maximum open cursors exceeded
    916079 Newbie
    Currently Being Moderated
    HI,

    I had increased the count of OPEN_CURSORS to 3000 but still we are facing the same issue of Maximum Cursors exceeded and users are not able to login.
    Could you explain on how to move forward with this procedure "Mitigate the leak by cycling your connection pool periodically".

    Regards,
    CV
  • 20. Re: maximum open cursors exceeded
    Justin Cave Oracle ACE
    Currently Being Moderated
    dba_fresher wrote:
    I had checked with the developers team but they say that do not have any result open-set open.
    If the number of open cursors is constantly increasing, Oracle disagrees with your developers. When Oracle disagrees with me, I generally assume that I'm the one who has made a mistake so I would strongly suspect that your developers are missing situations where they are incorrectly failing to close their result sets.
    I had also checked the status of the SID which shows that it is INACTIVE and also checked for the locks(TADDR) on the SID but didnt find any any.
    A status of INACTIVE simply means that it is not, at this instant, actively executing a SQL statement. And queries would not acquire any locks
    But still the count for the cursors is increasing for that SID. So could you provide any other way to overcome it.
    Take a look at V$OPEN_CURSOR and track down why the application is leaving those particular cursors open.

    Justin
  • 21. Re: maximum open cursors exceeded
    916079 Newbie
    Currently Being Moderated
    Hi,

    Thanks for your prompt reply.

    [A status of INACTIVE simply means that it is not, at this instant, actively executing a SQL statement. And queries would not acquire any locks]

    I agree with what you say but in some cases there might be child process running under a parent SID and that point of time that particular parent SID might acquire a lock until the child process completes its execution. So i had worked in that point of view.

    [Take a look at V$OPEN_CURSOR and track down why the application is leaving those particular cursors open.]

    When i had a look at the v$OPEN_CURSOR i found no rows under the SID which is taking the most open_cursors.

    Regards,
    CV
  • 22. Re: maximum open cursors exceeded
    Another_user Explorer
    Currently Being Moderated
    "Mitigate the leak by cycling your connection pool periodically" just means to terminate the connections from your application server to the database at some reasonable intervals, perhaps daily or hourly. The total count you get up to your max limit is tied to the connection - refresh the connections. Again though, this is a crutch and not a fix. And it may not even work at all if your hit your limit immediately on one pass through the code. This would be helpful if it is a slow leak accumulating over time.

    The problem is in your prepared statements and result sets. My best guess is that you are preparing a PreparedStatement in a loop. This is bad. Prepare once and execute many times.
  • 23. Re: maximum open cursors exceeded
    Justin Cave Oracle ACE
    Currently Being Moderated
    dba_fresher wrote:
    [Take a look at V$OPEN_CURSOR and track down why the application is leaving those particular cursors open.]

    When i had a look at the v$OPEN_CURSOR i found no rows under the SID which is taking the most open_cursors.
    How are you identifying the SID that has the most open cursors if not V$OPEN_CURSOR?

    Justin
  • 24. Re: maximum open cursors exceeded
    sb92075 Guru
    Currently Being Moderated
    dba_fresher wrote:
    Hi,

    Thanks for your prompt reply.

    [A status of INACTIVE simply means that it is not, at this instant, actively executing a SQL statement. And queries would not acquire any locks]

    I agree with what you say but in some cases there might be child process running under a parent SID and that point of time that particular parent SID might acquire a lock until the child process completes its execution. So i had worked in that point of view.

    [Take a look at V$OPEN_CURSOR and track down why the application is leaving those particular cursors open.]

    When i had a look at the v$OPEN_CURSOR i found no rows under the SID which is taking the most open_cursors.

    Regards,
    CV
    post results from SQL below

    select user_name, count(*) from v$open_cursor group by user_name;

    Handle:     dba_fresher
    Status Level:     Newbie (5)
    Registered:     Feb 7, 2012
    Total Posts:     56
    Total Questions:     18 (14 unresolved)

    WHY so many unanswered questions?
  • 25. Re: maximum open cursors exceeded
    916079 Newbie
    Currently Being Moderated
    [post results from SQL below  select user_name, count(*) from v$open_cursor group by user_name;]

    user_name count
    --------------- --------
    test 2
    system 1


    Regards,
    CV
  • 26. Re: maximum open cursors exceeded
    916079 Newbie
    Currently Being Moderated
    Hi,

    Below is the query which i am using to identify the SID which is using the most open_cursor.

    SQL> select * from ( select ss.value, sn.name, ss.sid from v$sesstat ss,v$statname sn where ss.statistic#=sn.statistic# and sn.name like '%opened cursors current%' order by value desc) where rownum < 11;

    VALUE NAME SID
    ---------- ---------------- ----------
    601 opened cursors current 49
    7 opened cursors current 22
    1 opened cursors current 41
    1 opened cursors current 14
    1 opened cursors current 39
    1 opened cursors current 23


    SQL> select osuser,username,status from v$session where ssid=49;

    osuser username status
    -------- ----------- ------
    oracle test INACTIVE


    SQL> select * from v$open_cursor where sid=49;

    no rows selected


    Could you help and guide me on how to move forward with this.

    Regards,
    CV
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points