1 2 Previous Next 20 Replies Latest reply on Oct 10, 2012 3:55 PM by Mark Malakanov (user11181920) Go to original post
      • 15. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
        Well, I think there are couple of things that a quite clear. The negative Execute to Parse ration DOES exist and jr has just confirmed in the last response. This number means more parse than execute - that's exactly what the AWR suggested. After the statement caching was enabled, this Execute to Parse% has increased to 45% - this is undeniable as the AWR speaks the truth more than what we or anyone would think or 'assumes' to be true.

        The only question remaining, however, is if anyone thinks or knows for sure or has experimented the Parse% has improved in anyway and if they have come to a very optimum number or if there is any saddle point or tradeoffs in other parameters, if this is optimized further ?

        @jgarry :
        would you happen to know, if any?


        Edited by: 963512 on Oct 9, 2012 2:05 PM
        • 17. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
          Apologies. I meant to write: jgarry and is corrected now
          • 18. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
            I wouldn't know of any formula, but I would answer the questions from user11181920. I would guess you'd have to figure out empirically (fancy word for trial and error) the best size for your app. I would also guess it would be a lost cause since I have trouble believing an app would be so narrow as to only have a limited set of SQL and return volume, but I could be wrong.
            • 19. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
              Jonathan Lewis
              963512 wrote:
              But, this has not bumped up the 'Execute to Parse%' to an expected value of ~100%, but only 45%. Now, would there be a recommended value or the criteria to calculate "*what is the best value for the oracle.jdbc.implicitStatementCacheSize to be set to get the optimum value in the execute to parse% ?*"

              Any best advise is higly appreciated!!
              These ratios are barely worth looking at - to a large extent they're a waste of time, but in most cases they cause confusion rather than helping. (See http://jonathanlewis.wordpress.com/2006/12/27/analysing-statspack-2/ )

              If you're going to worry about the impact of parse times, the first thing to check is total CPU usage compared to parse CPU time, total SQL elapsed time against parse time elapsed.

              You also need to understand what the count represents - a parse call may be counted, but depending on circumstances the amount of work that results from the parse call may vary quite dramatically; you may already have reached the best state that your implementation can achieve.

              If you're using the JAVA cache to reduce overheads you are the only person that can answer the question you've asked - you need to set it to a size that will enable it to keep open the cursors for the statements that you keep re-using in your application (plus a few percent to cater for whatever LRU to touch count algorithm the caching code uses to handle overflow).

              Jonathan Lewis
              • 20. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
                Mark Malakanov (user11181920)
                The SQL queries have already been tuned. Please provide any other info related to utilizing the DB capabilities in the optimum way, such as improvising and best utilizing Caching (esp. statement caching)
                It is multiple times asked here - do SQLs use bind variables or literal values?
                We all see this (use of bind vars) as a main factor reducing repetitive parsing.

                And even if mostry all SQLs are use bind var, just one that uses literal when executed frequently with different literals may spoil parsing stats, pollute caches etc...

                show us your slowest SQLs with plans and timing.
                Or better
                Run Statspack (or AWR) during your performance test, produce performance report, carefully look there or show it to us.
                1 2 Previous Next