1 2 Previous Next 26 Replies Latest reply: Oct 16, 2012 2:33 AM by 916079 Go to original post RSS
      • 15. Re: maximum open cursors exceeded
        916079
        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
          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
            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
              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
                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
                  JustinCave
                  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
                    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
                      "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
                        JustinCave
                        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
                          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
                            [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
                              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