8 Replies Latest reply: Apr 25, 2013 9:24 AM by user627885 RSS

    EMCA Metrics "Current Open Cursors Count" is at 1482

    yxes2013
      Hi all,

      11.2.0.1
      OEL 6.4
      I got warning alerts from our EMCA console for open cursor metrics.

      https://www.box.com/s/veyw21q9awkaa2gshchv

      Do I need to worry about this?

      How do you manage your open cursors?

      Why value should I make as "treshhold" for our Open Cursors?


      Thanks
        • 1. Re: EMCA Metrics "Current Open Cursors Count" is at 1482
          Salman Qureshi
          Hi,
          Why value should I make as "treshhold" for our Open Cursors?
          You mean "what value" ?. Well there is no hard and fast rule. It depends on your environment. If this is your requirement that your application sessions are cumulatively opening this many cursors, you should not worry about this. If this the value you normally see, you can consider it a "normal" value and increase your threshold value for alert (or you can disable this alert).
          If your memory consumption on the server is not increasing suddenly, you can also ignore it as it would look like normal thing.

          What is value of open_cursors set in your environment?
          http://docs.oracle.com/cd/E18283_01/server.112/e17110/initparams159.htm

          Salman
          • 2. Re: EMCA Metrics "Current Open Cursors Count" is at 1482
            Kh$n
            for more details regarding child cursor have look into below link.

            https://blogs.oracle.com/optimizer/entry/whydo_i_have_hundreds_of_child_cursors_when_cursor_sharing_is_set_to_similar_in_10g
            • 3. Re: EMCA Metrics "Current Open Cursors Count" is at 1482
              yxes2013
              I thank you all :)

              Our db server is OEL 6.4
              Oracle 11.2.01
              16G RAM
              24 CPUcore

              When I run free, I got:
              [oracle@oraclelinux odbc]$ free
                           total       used       free     shared    buffers     cached
              Mem:      16416712   15397096    1019616          0     328064   10320520
              -/+ buffers/cache:    4748512   11668200
              Swap:     10239996     134424   10105572
              Why is that it is eating almost all memory when there is no user yet?

              If I set process=2000 does it all take memory on the spot?
              • 4. Re: EMCA Metrics "Current Open Cursors Count" is at 1482
                User477708-OC
                look at the cursors and see what they are and whats generating them. are you using bind variables with correctly declared table and col types?

                11.2.0.1 was/IS notorious for generating thousands of child cursors with bugs (not just the old bind peeking). 1 days correct development could save you months of performance troubleshooting.
                • 5. Re: EMCA Metrics "Current Open Cursors Count" is at 1482
                  yxes2013
                  How to look at the cursors :(
                  • 6. Re: EMCA Metrics "Current Open Cursors Count" is at 1482
                    JohnWatson
                    yxes2013 wrote:
                    When I run free, I got:
                    [oracle@oraclelinux odbc]$ free
                    total       used       free     shared    buffers     cached
                    Mem:      16416712   15397096    1019616          0     328064   10320520
                    -/+ buffers/cache:    4748512   11668200
                    Swap:     10239996     134424   10105572
                    Why is that it is eating almost all memory when there is no user yet?
                    The second line of your free output says that you have about 16G RAM, of which 15G is in use and 1G is free. Of the 15G used, 10G is used for the file system cache. This is normal and a Good Thing: any Unix will use "spare" RAM for caching.
                    Or oif you want a different way of looking at it, read the third line. This says that nearly 5G is is being used by programmes, and another 11G is available to them whenever they want it.
                    • 7. Re: EMCA Metrics "Current Open Cursors Count" is at 1482
                      Vishnusivathej
                      Start by checking which sessions have the most cursors that are open,
                      what is cursor_sharing value?
                      Check which SQL statements have the highest versions count. (V$SQLAREA VERSION_COUNT)
                      Check why there are so many versions. using the V$SQL_SHARED_CURSOR....

                      thanks,
                      Vishnu