14 Replies Latest reply: Jul 12, 2012 11:15 AM by jgarry RSS

    open cursor

    user88831
      Hi Guys,

      Any idea how much memory will an open cursor consume?

      My current open cursor is set to 300. Due to a particular job, i will need to set to 400. (meaning each session can use 400 if needed)

      Understand that by setting it to a higher value, it simply only allocates a fixed number of slots but does not allocate memory for these slots unless it is utilized.

      Just want to see if there is way to "predict" how much memory will an open cursor utilize for impact analysis.


      thanks
        • 1. Re: open cursor
          sb92075
          dbaing wrote:
          Hi Guys,

          Any idea how much memory will an open cursor consume?

          My current open cursor is set to 300. Due to a particular job, i will need to set to 400. (meaning each session can use 400 if needed)

          Understand that by setting it to a higher value, it simply only allocates a fixed number of slots but does not allocate memory for these slots unless it is utilized.

          Just want to see if there is way to "predict" how much memory will an open cursor utilize for impact analysis.


          thanks
          you won't notice any difference
          • 2. Re: open cursor
            user88831
            thanks

            Any dictionary table that i can find out how much utilization a cursor utilizing?
            Is it store inside SGA or PGA? Assuming the cursor is utilize in a PL/SQLthank
            • 3. Re: open cursor
              sb92075
              dbaing wrote:
              thanks

              Any dictionary table that i can find out how much utilization a cursor utilizing?
              what is a "cursor"?
              Is it store inside SGA or PGA? Assuming the cursor is utilize in a PL/SQLthank
              PGA
              • 4. Re: open cursor
                Girish Sharma
                There is similar thread on below link :
                http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1089433252941

                and here are couple of similar questions and answer by Tom :

                Q.How can i know how much memory is used by each cursor.
                Ans: v$sql can show you the shared resource -- the "parsed query itself".

                v$sql_cursor has the memory associated with an instance of a cursor (session viewable only)

                Q.If the open_cursors are increased then what will be the performance impact on the db server and the memory usage?
                Ans:if you are not hitting ora-1000, it will change nothing (since you are not using the cursors you currently have)

                In addition to that below note may also be of your interest:
                http://www.orafaq.com/node/758

                Regards
                Girish Sharma
                • 5. Re: open cursor
                  Billy~Verreynne
                  From the Oracle® Database Reference guide - my emphasis:
                  <i>
                  OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.
                  </i>

                  Setting it to 400!? What on earth for??

                  Why would an application need to open 400 unique and distinct SQL cursors AT THE SAME TIME and keep them open AT THE SAME TIME?

                  Such an application, if it exists, qualifies as the Very Worse Piece Of Code Using Oracle Ever!.

                  If open_cursors need to be set - it should be lowered to 50. Perhaps more. As that is the number of cursors that an application should typically have open at the same time. Note some client language environments create implicit cursors, like commit and rollback, that are kept open for the entire duration of the client app's lifetime. So setting it to below 10 for example, may cause problems for such client run-time environments.
                  • 6. Re: open cursor
                    rp0428
                    >
                    If open_cursors need to be set - it should be lowered to 50. Perhaps more.
                    >
                    What? A large financial institution I consulted for had a Java/TIBCO/Oracle based app whose main procedure did nothing but open 50+ cursors for each user that logged on and returned all 50+ cursors as OUT parameters to the Java middle tier. Of course they did wonder why the performance was so bad but couldn't figure out where all the resources were being used.

                    This app was a GUI that would show a list of top-level items (e.g. projects) with one cursor. The proc would then open each and every cursor for ALL sub-screens that could ever be visited by the user so that IF the user ever decided to traverse a particular subchain the DB cursors would already be opened and the performance would be great.

                    Had trouble convincing them that the multi-second initial response for each user was directly related to all of those cursors being opened. They thought that just opening cursors didn't really do any 'work' so what did it hurt? They didn't seem to understand my analogy of the librarian bringing a copy of every book in the library to everyone that walked in the door just on the chance that they might want to browse through it.
                    • 7. Re: open cursor
                      jgarry
                      Billy  Verreynne  wrote:
                      From the Oracle® Database Reference guide - my emphasis:
                      <i>
                      OPEN_CURSORS specifies the maximum number of open cursors (handles to private SQL areas) a session can have at once. You can use this parameter to prevent a session from opening an excessive number of cursors.
                      </i>

                      Setting it to 400!? What on earth for??

                      Why would an application need to open 400 unique and distinct SQL cursors AT THE SAME TIME and keep them open AT THE SAME TIME?

                      Such an application, if it exists, qualifies as the Very Worse Piece Of Code Using Oracle Ever!.
                      I'm using one like that, and the issues tend to be other than the fact that it, as rp almost put it, opens every book that might be read. Actually, it runs pretty good, even without considering that it's early '90's technology. I see two common performance issues: developers writing code that would be silly in any language, and the optimizer choosing a bad plan (especially with views, and I can't just hint to fix). The problem I'm watching just now is the former (inappropriate use of in memory arrays flogging the cpu, DSS program on OLTP system), the 190 open cursors are no issue at all. When it comes down to it, enterprise apps really don't have to be the most technically efficient whiz-bang modern dba 2.0 cloudy etc etc. They just have to get the job done for the users with a minimum of fuss and bugs. A major reason we are replacing it is Larry being such a jerkface about Itanium. Performance and projected growth are not.

                      My open_cursors is 5000 and session_cached_cursors is 500. So there.

                      >
                      If open_cursors need to be set - it should be lowered to 50. Perhaps more. As that is the number of cursors that an application should typically have open at the same time. Note some client language environments create implicit cursors, like commit and rollback, that are kept open for the entire duration of the client app's lifetime. So setting it to below 10 for example, may cause problems for such client run-time environments.
                      • 8. Re: open cursor
                        Billy~Verreynne
                        jgarry wrote:

                        My open_cursors is 5000 and session_cached_cursors is 500. So there.
                        Damn! I just threw up in my mouth. A little bit.
                        • 9. Re: open cursor
                          sybrand_b
                          Why?

                          300 is usually insufficient for COTS. You should be aware how much incompetent developers there are on the globe. Way too many that is!
                          Please note recursive SQL also requires cursors!!!
                          --------------
                          Sybrand Bakker
                          Senior Oracle DBA
                          • 10. Re: open cursor
                            Billy~Verreynne
                            rp0428 wrote:
                            >
                            If open_cursors need to be set - it should be lowered to 50. Perhaps more.
                            >
                            What? A large financial institution I consulted for had a Java/TIBCO/Oracle based app whose main procedure did nothing but open 50+ cursors for each user that logged on and returned all 50+ cursors as OUT parameters to the Java middle tier. Of course they did wonder why the performance was so bad but couldn't figure out where all the resources were being used.
                            I have long ago ceased to be amazed with the amount, level and intensity of database abuse from Java code monkeys. In fact, I'm supprised these days when not seeing Java abusing the database.... +<sigh>+
                            Had trouble convincing them that the multi-second initial response for each user was directly related to all of those cursors being opened. They thought that just opening cursors didn't really do any 'work' so what did it hurt? They didn't seem to understand my analogy of the librarian bringing a copy of every book in the library to everyone that walked in the door just on the chance that they might want to browse through it.
                            I've often wondered about that. Why is it that I never had a problem (wearing the DBA hat) explaining to Cobol programmers in how to use the database optimally - with them grasping these database fundamentals and applying these to their code. Yet, with most Java developers it is the exact opposite. Come on!! Cobol programmers being more technically savvy when it comes to the database, then Java developers that deal (and supposedly understand) the more complex paradigm of o-o development..???

                            And then I realised that Java is not a programming language. It has none of the characteristics I've grown accustom to in writing code in a dozen or so languages. None of interaction with developers in these language, and these developers comprehending what is conveyed.

                            Java developers exhibit all the characteristics of religious fundamentalist nutcases. So that makes Java a religion - not a programming language.
                            • 11. Re: open cursor
                              Billy~Verreynne
                              sybrand_b wrote:

                              300 is usually insufficient for COTS.
                              That sounds like "+kots+" - not sure if the Afrikaans word rings a Dutch language bell, but it means vomiting. Which pretty much explains it. :-)

                              Had a look on a 12 node RAC (busiest database I have with about 40,000+ row inserts/sec). The top session with regards open cursors is on node 3. 80 cursors. System process OEM.SystemPool.

                              I think what the COTS folk do not realise is that performance is synonymous with keeping the number of moving parts down to as few as possible, and as simple as possible. Adhoc creating of cursors because this may just benefit performance, is a very unsound, illogical and unhealthy approach to performance and scalability.
                              • 12. Re: open cursor
                                sybrand_b
                                Afrikaans stems from Dutch: kots means the same in Dutch ;)
                                Barring this Flemish is much easier to understand for Dutchies than Afrikaans, even if 'Apartheid' is almost the only Dutch word, which made it into English ;)

                                -------------
                                Sybrand Bakker
                                Senior Oracle DBA
                                • 13. Re: open cursor
                                  jgarry
                                  wikipedia has a list of Dutch words that made it to English, has a cite that 1% of English words are from Dutch. kots nearly got my screen a coffee shower.
                                  • 14. Re: open cursor
                                    jgarry
                                    You are of course perfectly correct. But you need to remember, when evaluating total performance of a system, whether Oracle or thermodynamics, localized non-optimal tuning may improve the entire system. It's often a matter of shifting costs around, whether not re-inventing the wheel, precalculating aggregate values, materializing views, or evolving sentient life forms.

                                    I share your opinion on, say, java developers, but that doesn't stop me from using em rather than writing my own performance scripts, or even using the free available ones, aside from special cases requiring deeper investigation. I just cuss at the java processes being on the unix top screen when em is the only thing using java. kots, indeed.