1 2 Previous Next 22 Replies Latest reply: Oct 23, 2011 12:11 AM by Paulie RSS

    low Execute to Parse % and high soft parse %

    807722
      Hello Folks


      I am working on oracle 10g release2 on HP-UX

      After going through awr reports observed it have low Execute to Parse % but high soft parse % (Instance Efficiency Percentages)

      so cannot say issue with less use of bind variables,then what is cause of Execute to Parse %

      searched sites like ask.tom,burselon counsulting etc as usual they had given generic/diplomatic(escaping) replies on this
      like due to problem in application code,ineffecient sharing ,due to problem in database parameters etc
      without any clear indication for cause and solution like if some database parameters not set properly then should say which database parameters can be checked,cause due to more parsing and less execution

      please share if you had faced such issue and any suggestions to solve this

      examples why this could happen ,like possibilities in application code

      Thanks
        • 1. Re: low Execute to Parse % and high soft parse %
          sb92075
          804719 wrote:
          Hello Folks


          I am working on oracle 10g release2 on HP-UX

          After going through awr reports observed it have low Execute to Parse %
          at what value does "low" become acceptable?

          but high soft parse % (Instance Efficiency Percentages)
          at what value does it become "high"?



          Handle:      804719
          Status Level:      Newbie
          Registered:      Oct 24, 2010
          Total Posts:      73
          Total Questions:      24 (23 unresolved)
          • 2. Re: low Execute to Parse % and high soft parse %
            807722
            Soft Parse %: 99.01
            Execute to Parse %: 8.09

            This is almost same most of time
            • 3. Re: low Execute to Parse % and high soft parse %
              Chinar
              804719 wrote:
              Soft Parse %: 99.01
              Execute to Parse %: 8.09

              This is almost same most of time
              Post here also Load Profile and Instance Efficiency Percentages sections here
              • 4. Re: low Execute to Parse % and high soft parse %
                807722
                Load Profile
                ~~~~~~~~~~~~ Per Second Per Transaction
                --------------- ---------------
                Redo size: 11,685.79 3,660.98
                Logical reads: 71,445.74 22,382.86
                Block changes: 70.89 22.21
                Physical reads: 58.63 18.37
                Physical writes: 2.80 0.88
                User calls: 652.93 204.55
                Parses: 48.39 15.16
                Hard parses: 0.33 0.10
                Sorts: 6.90 2.16
                Logons: 0.23 0.07
                Executes: 52.71 16.51
                Transactions: 3.19

                % Blocks changed per Read: 0.10 Recursive Call %: 30.48
                Rollback per transaction %: 2.57 Rows per Sort: 29.66

                Instance Efficiency Percentages (Target 100%)
                ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %: 100.00 Redo NoWait %: 100.00
                Buffer Hit %: 99.92 In-memory Sort %: 100.00
                Library Hit %: 98.47 Soft Parse %: 99.32
                Execute to Parse %: 8.19 Latch Hit %: 99.63
                Parse CPU to Parse Elapsd %: 89.90 % Non-Parse CPU: 99.62
                • 5. Re: low Execute to Parse % and high soft parse %
                  Chinar
                    Load Profile
                                                            Per Second       Per Transaction
                                                      ---------------       ---------------
                                 Redo size:             11,685.79              3,660.98
                                 Logical reads:             71,445.74             22,382.86
                                 Block changes:                 70.89                 22.21
                                 Physical reads:                 58.63                 18.37
                                 Physical writes:                  2.80                  0.88
                                 User calls:                652.93                204.55
                                 Parses:                 48.39                 15.16
                                 Hard parses:                  0.33                  0.10
                                 Sorts:                  6.90                  2.16
                                 Logons:                  0.23                  0.07
                                 Executes:                 52.71                 16.51
                                 Transactions:                  3.19
                   
                              % Blocks changed per Read:    0.10    Recursive Call %:    30.48
                              Rollback per transaction %:    2.57       Rows per Sort:    29.66
                   
                      Instance Efficiency Percentages (Target 100%)
                   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                               Buffer Nowait %:  100.00       Redo NoWait %:  100.00
                               Buffer  Hit   %:   99.92    In-memory Sort %:  100.00
                               Library Hit   %:   98.47        Soft Parse %:   99.32
                               Execute to Parse %:    8.19         Latch Hit %:   99.63
                               Parse CPU to Parse Elapsd %:   89.90     % Non-Parse CPU:   99.62
                  There rdbms performs approximately 48 soft parse per second ,Soft Parse % and Library Hit very close to 99 it means there main part of sql are shared.Also here user calls similar high per second but executions less,however you should be try minimizing soft parsing.I do not know exactly for which interval you get this report but Execute to Parse % indicate that when executing query oracle can not find early cursor handles(open or closed) but it can find sql text and plan information from shared pool according hash values,in this case oracle perform soft parse again.In your case you also investigate shared pool size/fragmentation.To avoid little Execute to Parse % you can increase SESSION_CACHED_CURSORS or implement CURSOR_SPACE_FOR_TIME.So refer documentation and find how to use these parameters.
                  • 6. Re: low Execute to Parse % and high soft parse %
                    807722
                    thanks chinar for quick reply

                    Make sense,close to what i was thinking

                    I was planning if i can increase SESSION_CACHED_CURSORS ,but mention try to minimize soft parse
                    from db side is it possible to lower soft parse

                    one question about SESSION_CACHED_CURSORS its value will be for whole instance/all sessions
                    or will it be for each session ,say i configure SESSION_CACHED_CURSORS =100 will that means
                    each sessions can cache 100 cursors or it means all sessions can cache 100 cursors
                    • 7. Re: low Execute to Parse % and high soft parse %
                      Chinar
                      As you can see this is session/same server process related parameter ,but properly setting this parameter will effect instance life time(minimizing soft parse).
                      • 8. Re: low Execute to Parse % and high soft parse %
                        Billy~Verreynne
                        A soft parse means what? The client issues something like a OCIStmtPrepare(). There is already a cursor in the Shared Pool for that SQL or PL/SQL code - and instead of having to hard parse and add a new cursor to the Shared Pool, the existing cursor can be used via a soft parse.

                        The client now receives a handle for that cursor - and can proceed to use it (bind, describe, execute, fetch, etc).

                        If soft parsing is high, it means clients are creating (preparing) the same set of SQL (or PL/SQL) instructions over and over again.

                        This is an application problem in a stateful client - as it should prepare a statement once and re-use that statement (cursor) handle again and again (that is why there are bind calls - to allow the client to re-use that cursor handle for different values).

                        In a stateless environment, this is to be expected. As clients do not use stateful sessions lasting the entire lifetime of the client session. New sessions are created and closed continually. An increase is soft parsing is a direct result of this environment.

                        So I am bit puzzled what Oracle configuration knob you think can be turned to reduce soft parsing and modify behaviour of actual client code...
                        • 9. Re: low Execute to Parse % and high soft parse %
                          807722
                          thanks billy

                          yes we also thought its application problem and pointed out to them but since they had not improved
                          on this we want to do whatever from database side to improve

                          want to ensure if increasing session_cached_cursors should not have any negative impact apart from taking more memory from shared pool( if some one can suggest how to work out how much of memory it will take say like
                          increase from 20 to 100,somewhere in docs mentioned default is 50 then how come it taking value of 20 in oracle 10g rel 2)
                          • 10. Re: low Execute to Parse % and high soft parse %
                            Billy~Verreynne
                            804719 wrote:

                            yes we also thought its application problem and pointed out to them but since they had not improved on this we want to do whatever from database side to improve
                            What exactly do you want to improve?

                            If you want to improve hard parsing overheads, you can try and force more soft parsing using forced cursor sharing. In this case, Oracle removes literals from the SQLs supplied by clients, add bind variables instead, and do the required binding on the server side. As bind variables are now used, the likelihood of cursors being shared are increased.

                            This is however a large hammer approach - and can cause internal errors (e.g. ORA-600's) in Oracle. (have seen this in 10.2.x with forced cursor sharing).

                            Another potential cause of hard parsing is that cursors that are reusable, are aged out of the shared pool "too quickly" - and when the same SQL is parsed, the previous cursor for it no longer exists in the shared pool. A hard parse and creation of a brand new cursor thus follow.

                            This you can possibly address with a larger shared pool, or increasing the number of cursors cached per application. But this will not reduce soft parsing.

                            Soft parses mean there is a reusable cursor in the shared pool. This is optimal from a database perspective as it does not need to burn CPU cycles to perform a hard parse and create a brand new cursor in the shared pool.

                            Changing server parameters is not going to reduce the number of OCIStmtPrepare() client statements - the statements that are causing excessive soft parsing. The only way to address this is to fix the client in order to reuse the cursor handle it gets from a OCIStmtPrepare() - and not call OCIStmtPrepare() each time around when using the very same SQL.
                            want to ensure if increasing session_cached_cursors should not have any negative impact apart from taking more memory from shared pool( if some one can suggest how to work out how much of memory it will take say like increase from 20 to 100,somewhere in docs mentioned default is 50 then how come it taking value of 20 in oracle 10g rel 2)
                            I do not see this reducing soft parsing for existing cursors already cached...
                            • 11. Re: low Execute to Parse % and high soft parse %
                              807722
                              1)Application being siebel most of code is optimized by oracle itself ,there is lesser scope that application team
                              can further improve the code (as tables,codes etc is standard)


                              2)Reason for increasing session_cached_cursors is that i seen its usage by logging report ,All times in report
                              it seen all 100% of existing session_cached_cursors is used from one of query,
                              Is this be taken as indication of increasing it further??

                              From other query
                              CURSOR_CACHE_HITS SOFT_PARSES HARD_PARSES
                              ----------------- ----------- -----------
                                   85.44% 13.71%     0.85%

                              which is different then what is in awr report(where it shows 8% of CURSOR_CACHE_HITS and rest soft parse)
                              which one is true
                              • 12. Re: low Execute to Parse % and high soft parse %
                                Billy~Verreynne
                                Is there a performance issue?

                                Or are you looking for one? ;-)
                                • 13. Re: low Execute to Parse % and high soft parse %
                                  mbobak
                                  What Billy is getting at, is what problem are you actually trying to solve?

                                  Do you actually have a performance problem of any kind? (Are the users happy with the performance of the system? Are they complaining about slowness?)

                                  Now, even if you do have a performance problem, what indication do you have that it has to do with parsing?

                                  What do the Top 5 Timed Events from that AWR show? Are you sure the AWR is properly scoped? (Does it report on a period of time that people actually experienced slow performance?)

                                  What about tracing sessions? The most accurate assessment of performance will only come from profiling a session when it's experiencing a problem. That will tell you exactly where time is being spent. Then you can decide how to optimize the process.

                                  Hope that helps,

                                  -Mark
                                  • 14. Re: low Execute to Parse % and high soft parse %
                                    807722
                                    there is not any performance problem reported but awr report showing same number of parsing and execution
                                    and about 99% soft parsing,which made me think parsing is high and if increasing cache cursor can help

                                    at the same time if i use dynamic view to query cursor cache hit is 85% and its 100%used

                                    do i need to change anything or just keep monitoring

                                    Thanks
                                    1 2 Previous Next