9 Replies Latest reply on Apr 24, 2019 10:03 AM by Jonathan Lewis

    session_cached_cursors

    3510875

      Dear Experts,

       

      Oracle says to parse once execute many.Can this be achieved using session_cached_cursors?

       

      Thank you

        • 1. Re: session_cached_cursors
          John Thorton

          3510875 wrote:

           

          Dear Experts,

           

          Oracle says to parse once execute many.Can this be achieved using session_cached_cursors?

           

          Thank you

          Parse once & use many can be achieved by using BIND variables.

           

          Is application 3-tier?

           

          Does  application utilize Connection Pooling?

          • 2. Re: session_cached_cursors
            Mark D Powell

            user3510875, no.  To parse once and execute many you need for the code to be written using bind variables rather than constants.  That is if the application creates the SQL statement by concatenating strings together and passes the result to Oracle as a query, for example:

            select * from part_master where part_no = '1234' followed by

            select * from part_master where part_no = '5678' ....

            then each of these is a unique statement to Oracle.  What you want is to pass a statement that looks like

            select * from part_master where part_no = :variable

            then Oracle will hard parse this statement once and soft parse it each time a new value is received in the :variable.

            - -

            Every statement has to be parsed every time however what you want is one hard parse the first time the statement is submitted and soft parses from then on out.

            - -

            HTH -- Mark D Powell --

            • 3. Re: session_cached_cursors
              Jonathan Lewis

              3510875 wrote:

               

              Dear Experts,

               

              Oracle says to parse once execute many.Can this be achieved using session_cached_cursors?

               

              Thank you

               

              Others have pointed out that "use bind variables" is a strategy for minimising the costs of parsing but your question allows a lot of room for interpretation, partly because the phrase "parse once execute many" doesn't define its terms clearly.

               

              A parse call is a specific call made by the client code to the server - if your client issues a parse call you will see your session records a "parse".

              However, the consequences of a parse call may (in simplfied terms) be one of:

              a) create a cursor area in the SGA for the supplied SQL text, do the syntactic and semantic checks, then optimise and generate the plan

              b) search the library cache for an existing cursor that could be used for the statement

              c) use a state object that points directly to a suitable previously used cursor for the statement (avoiding the search).

               

              If your client code has a statement that it has used in the recent past then a parse call will (by default) be following (b) - which is why you have had comments about "use bind variables, not literals" as the use of bind variables means you will probably keep reusing the same statement even though the bound values change.

               

              If you have enabled the session cursor cache (which is enabled with a cache size of 50 by default) then a parse call will (after the 2nd or 3rd usage) result in the creation of a state object which means you will subsequently be following (c).

               

              ---

               

              If your client code is written to issue a single parse call and then hold the cursor for subsequent executions it will (in effect) be following a slightly more efficient version of (c) - bypassing some of the peripheral activitiy associated with a parse call.

               

              In fact you may find that some DRIVERS have a cunning client-side cache which holds cursors even when the client code thinks it has closed the cursors, so you may find some cases where the client code appears to be issuing repeated parse calls, but those calls never reach the server.

               

              Coincidentally (though perhaps not) Cary Millsap has just tweeted out a link to an article he's written about bind variables vs. parse calls: https://www.quora.com/How-do-you-fix-a-non-bind-variable-usage-in-Oracle/answer/Cary-Millsap?srid=uKTz

               

              Regards

              Jonathan Lewis

              • 4. Re: session_cached_cursors
                3510875

                Thanks for all the updates.

                 

                Dear Jonathan,

                If your client code is written to issue a single parse call and then hold the cursor for subsequent executions it will (in effect) be following a slightly more efficient version of (c) - bypassing some of the peripheral activitiy associated with a parse call.

                 

                Confused with above statement.what kind of activity associated with parse call is avoided?

                 

                Thank you

                • 5. Re: session_cached_cursors
                  3510875

                  upto my knowledge:

                  1)syntactic and semantic checks are avoided with session_cached_cursors...

                  2)searching the library cache for the sql is avoided using session_cached_cursors

                   

                  Will oracle takes any latches with session_cached_cursors?If yes for what activities it may take the latches for?

                  what kind of activity associated with parse call is avoided?

                   

                  Thank you

                  • 6. Re: session_cached_cursors
                    Jonathan Lewis

                    3510875 wrote:

                     

                    Thanks for all the updates.

                     

                    Dear Jonathan,

                    If your client code is written to issue a single parse call and then hold the cursor for subsequent executions it will (in effect) be following a slightly more efficient version of (c) - bypassing some of the peripheral activitiy associated with a parse call.

                     

                    Confused with above statement.what kind of activity associated with parse call is avoided?

                     

                    Thank you

                     

                    The only one I can think of right now is adaptive cursor sharing - if you continually issue parse calls then the code path for adaptive cursor sharing becomes available.

                     

                    Regards

                    Jonathan Lewis

                    • 7. Re: session_cached_cursors
                      Jonathan Lewis

                      3510875 wrote:

                       

                      upto my knowledge:

                      1)syntactic and semantic checks are avoided with session_cached_cursors...

                      2)searching the library cache for the sql is avoided using session_cached_cursors

                       

                      Will oracle takes any latches with session_cached_cursors?If yes for what activities it may take the latches for?

                      what kind of activity associated with parse call is avoided?

                       

                      Thank you

                      Second question answered in response to the previous post.

                       

                      First queston - most of the library cache activity is now done on mutexes rather than latches so it's very hard to see any difference in latching when the session cursor cache is in place.  There is probably some (insignificant) effect due to the way that Oracle precreates arrays of spare library cache locks and library cache pins and then allows sessions to reserve a limited number of them that might become observable at high levels of concurrency when the number of different statements every session keeps calling is larger than the session_cached_cursors and the number of concurrent sessions is high. (In other words if the effect does exist then very few people are likely to see it.)

                       

                      If you want to understand more of how this type of stuff works then you need to get a copy of Oracle Core: https://www.amazon.co.uk/Oracle-Core-Essential-Internals-Developers/dp/1430239549 - but bear in mind that Oracle Corp. has had nearly 8 years to change the code since I wrote the book.

                       

                      Regards

                      Jonathan Lewis

                      • 8. Re: session_cached_cursors
                        3510875

                        Thanks for the update.

                         

                        Please correct us if our understanding is wrong:

                         

                        1)We cannot avoid the parse calls with session_cached_cursors.

                        2)The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times

                        3)If we want to reduce the parse calls,that should be done from application end

                         

                        Thank you

                        • 9. Re: session_cached_cursors
                          Jonathan Lewis

                          3510875 wrote:

                           

                          Thanks for the update.

                           

                          Please correct us if our understanding is wrong:

                           

                          1)We cannot avoid the parse calls with session_cached_cursors.

                          2)The obvious advantage to caching cursors by session is reduced parse times, which leads to faster overall execution times

                          3)If we want to reduce the parse calls,that should be done from application end

                           

                          Thank you

                          That is a reasonable summary.

                          Two points - both about item (2)

                           

                          a) session cached cursors also reduce the scope for losing time to contention due to concurrent activity

                          b) there are times when it is important to be very fussy about the choice of words "execution" is what happens after parsing - an expression like "overall response time" would have avoided any ambiguity.

                           

                          Regards

                          Jonathan Lewis