This discussion is archived
1 2 Previous Next 20 Replies Latest reply: Oct 10, 2012 8:55 AM by Mark Malakanov (user11181920) RSS

What is the maximum cache size recommended for best 'execute to parse%' ?

966515 Newbie
Currently Being Moderated
I am designing my DAO layer of my applicaiton for the best optimum performance and best value for the 'execute to parse%' in the AWR report (the ideal optimum value would be ~100%). We have seen improvements in this by setting a value of 20 for the 'oracle.jdbc.implicitStatementCacheSize' property in the DataSource connection properties. We have seen major improvements in system execution time in data fetches and SQL executions etc. 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!!

Thanks

Edited by: 963512 on Oct 5, 2012 12:51 PM
  • 1. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    phaeus Pro
    Currently Being Moderated
    Hello,
    i am not a expert with your application layer you use. But your hit ratio also depends other things like database parameter (for example cursor_sharing). In case it is exact then similar would do the job (but dont forget to test database changes).

    You can look in this page.
    http://www.dba-oracle.com/m_parse_to_execute_ratio.htm

    regards
    Peter
  • 2. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    damorgan Oracle ACE Director
    Currently Being Moderated
    You are not going to address your issue solely by focusing on "the best value for."

    If there was a single "best value" don't you think Oracle would have set it as the default and/or every doc ever written would tell you to use that value?

    Certainly there is value in improving execute to parse but there are many factors that enter into it ... everything from cursor invalidation to bind variable usage to .... I hope you get the idea.

    Also, by focusing so tightly on a single factor you are dangerously approaching a disease we call "Compulsive Tuning Disorder."

    So my recommendation would be to step back and answer the following questions:

    1. Am I meeting my SLA?
    2. Is anyone complaining about performance?
    3. At what layer of the technology stack is the greatest wait time? Network, Application, Middleware, Database, Storage?
    4. If my Execute to Parse was 100% (which is totally unachievable) how big a real-world impact would it have on the customer experience?
    5. How many parent cursors am I generating?
    6. How many child cursors per parent am I generating?
    7. What are my top waits?

    Given the essentially zero information you provided, no version number, wait information, etc. The best advice I can give is to reconsider your approach as it is likely not going to accomplish much by itself.
  • 3. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    Mark Malakanov (user11181920) Expert
    Currently Being Moderated
    "what is the best value for the oracle.jdbc.implicitStatementCacheSize to be set to get the optimum value in the execute to parse% ?"
    How many different SQL statements the application issues?
    But, this has not bumped up the 'Execute to Parse%' to an expected value of ~100%, but only 45%.
    Do use bind variables or literal values in your SQLs?
  • 4. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    jgarry Guru
    Currently Being Moderated
    That cache is in the middle layer, not the db. The best way to reduce parsing would be to [url http://www.oracle.com/technetwork/issue-archive/2009/09-sep/o59asktom-082712.html]move the queries to pl/sql stored procedures.

    You've been asked to provide version and details like the spread of SQL statements. If your developers don't make the same SQL requests, you are going to be parsing new SQL requests. The details of child cursors vary by versions.
  • 5. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    966515 Newbie
    Currently Being Moderated
    The Oracle version being used is Oracle 11.2g.
    The goal here is to improve performance at the DB layer when the Batch writes to the DB.
    A little bit of the system and what it is doing: The system basically here is a Batch written in Java; reads data, processes and writes to the Database (as simple as that)
    We have many XML Queries + Relational Queries; the SQLs are managed and executed by Java based APIs using JDBC4 driver.
    SLA is definitely an issue at the moment with more emphasis on the writes to the DB as we have benchmarked that the Java process takes its optimum amount of time and this part has already been tuned up. The SQLs have also been tuned up, the only parameter we are focussing right now is to improvise on the overall Writes + Reads process by making best use of the features offered by Oracle11.2g, especially the Statement Caching at the Physical Connection levels. This way, we want to optimize and we did see a huge variance in the benchmarks of the overall Batch performance in terms of time. We did see a 30% time improvement by enabling the statement caching. But, right now, we do want to play with the maximum cache size parameter and find the best value for our app.

    Pls let me know if more info is needed.


    Thanks !!
  • 6. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    sb92075 Guru
    Currently Being Moderated
    963512 wrote:
    right now, we do want to play with the maximum cache size parameter and find the best value for our app.
    Ready, FIRE, Aim!
  • 7. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    6363 Guru
    Currently Being Moderated
    963512 wrote:
    The system basically here is a Batch written in Java; reads data, processes and writes to the Database (as simple as that)
    So you do not process data using a system designed for data management, but using an interpreted language designed to operate televisions and you wonder why it is slow?

    http://en.wikipedia.org/wiki/Java_%28programming_language%29#History
    the Java process takes its optimum amount of time and this part has already been tuned up.
    Just because you are processing data as fast as Java can does not mean the data is being processed as fast as it can be.
    best value for the 'execute to parse%' in the AWR report (the ideal optimum value would be ~100%).
    Number of times a statement is executed is determined by the client application. Number of times a statement is parsed is determined by the client application. Therefore the ratio of the two is also entirely determined by the client application and a value of 45% is terrible and means that this particular client application likely does not use bind variables, over parses and is insecure.

    Setting the cursor_sharing parameter to force may lessen the impact of the Java code not using shareable SQL with variables until it is fixed.
  • 8. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    damorgan Oracle ACE Director
    Currently Being Moderated
    You have so far not provided any of the information required to tell you more than the fact that you are approaching this the wrong way. Get an experienced DBA involved.
  • 9. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    966515 Newbie
    Currently Being Moderated
    @damorgan:
    I thought I did provide the info you've asked for. Anyways, I am giving them in the sequence of your asking here:

    1. Am I meeting my SLA?
    Not really, the performance bottlenecks have been identified in our application and we have tuned the Java code side, made the SQL queries performant, but we haven't hit the sweet spot in terms of the DB optimization capabilities such as caching etc. We have however, been able to minimize the overall time the app is taking when we enabled Statement caching on the physical connections. Hope, this specific point is clear on the fact that I am trying to find the best way to reduce this time further. However, my only fear is, would Oracle provide any LIMITATION on this value, for instance, I know for a fact that the fetchSize when set to >100 is a very BAD performance number. Would there be any such caution to be taken when proceeding with trying out or using the MAX number on the statement caching ?

    2. Is anyone complaining about performance?
    YES!! This is crux of the problem and I hope I did make it very clear in the my point in #1.

    3. At what layer of the technology stack is the greatest wait time? Network, Application, Middleware, Database, Storage?
    As described already, the Java Code | Database are the 2 major layers we are focussing on. There is no other middleware apart from the code directly doing the CRUD operations on the DB. As a java developer, I have made the best tune up possible on the Java Side of it. However, we are now focussing on the Database aspect. And greatest wait time was observed on the Reads + Writes and after the statement caching was enabled, we did see average improvements in the AWR after the batch was run.

    NOTE: When the statement caching was turned ON, we have seen the overal Execute to Parse % bumped up from -0.77% to 45%. This was remarkable. Also, this has helped us reduce our Overall Processing time by 30% !! Basing on this observation, I am trying to find out the best value possible to bring down our Overall Processing time further. So... I HOPE this is very clear!

    4. If my Execute to Parse was 100% (which is totally unachievable) how big a real-world impact would it have on the customer experience?
    Very much!! As explained in the NOTE in #3.

    5. How many parent cursors am I generating?
    I am not aware of this, as I am not a DB Developer nor a DBA.

    6. How many child cursors per parent am I generating?
    I am not aware of this, as I am not a DB Developer nor a DBA.

    7. What are my top waits?
    Name a specific parameter and I can provide you with the info.
  • 11. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    966515 Newbie
    Currently Being Moderated
    @sb92075
    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)
  • 12. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    sb92075 Guru
    Currently Being Moderated
    when all else fails, Read The Fine Manual

    http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm
    When the statement caching was turned ON, we have seen the overal Execute to Parse % bumped up from -0.77% to 45%.
    When fewer than 50% of SQL are being reused, increasing DB statement cache means it just collect more never reused SQL statements.
  • 13. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    damorgan Oracle ACE Director
    Currently Being Moderated
    You didn't answer my questions, you still haven't, and you acknowledge as much.

    1. You are not meeting your SLA ... that is one data point.
    2. People are complaining ... now we know there is an actual tuning issue.
    3. You have not established that anything points to the database as you clearly acknowledge but do not seem to understand.

    To demonstrate it is a database issue you need to eliminate every other portion of the stack and run the code directly in the database. Have you done so? Can you point to the number of milliseconds/seconds difference between running in the database and via the application? It appears the answer is "No."

    But let us assume it is the database, so far not established, do you know where the database is spending its time? Again "No."

    4. Nothing you wrote in response to this is believable starting with the value "-0.77%" which absolutely isn't real. I so far see no supporting evidence that changing your execute to parse percentage is the root cause.

    5. Then find out. You can not tune in a vacuum. That you are a developer is obvious. That you have not involved your DBA in addressing this issue unfathomable.

    6. Then find out. You can not tune in a vacuum. That you are a developer is obvious. That you have not involved your DBA in addressing this issue unfathomable.

    7. And your answer to this question is 100% of the problem with believing that anything you have done, or are trying to do, isn't just a complete waste of time. Here is a website created specifically for people doing what you are doing.
    http://www.battleagainstanyguess.com/

    I am glad to try to help you ... so are many others ... but we can not help you if you do not involve your DBA, or someone with DBA credentials, that can determine the root cause of what is causing things to be slow.
  • 14. Re: What is the maximum cache size recommended for best 'execute to parse%' ?
    jgarry Guru
    Currently Being Moderated
    >

    >
    4. Nothing you wrote in response to this is believable starting with the value "-0.77%" which absolutely isn't real. I so far see no supporting evidence that changing your execute to parse percentage is the root cause.
    Search for the word "negative" in the link I posted. Such a value means more parsing than executing.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points