Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Unanswered: High Parse to Execute Ratio - need Help

    Hai all,

    I am running a oracle datase on a solaris box.
    I used a third party tool to tune the database and found the following recommendations.

    HIgh Parse to Execute Ratio

    parse count (total) =93517039

    parse count (hard) = 19941341

    execute count = 95536447

    Parse to execute ratio = parse count (total)/execute count = 97.88%

    I am not sure what is the difference between the total and hard parse counts.

    I would like to know what is the next thing I should do to reduce this value.


    Thanks

    micky

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: High Parse to Execute Ratio - need Help

    Originally posted by mickykt
    Hai all,

    I am running a oracle datase on a solaris box.
    I used a third party tool to tune the database and found the following recommendations.

    HIgh Parse to Execute Ratio

    parse count (total) =93517039

    parse count (hard) = 19941341

    execute count = 95536447

    Parse to execute ratio = parse count (total)/execute count = 97.88%

    I am not sure what is the difference between the total and hard parse counts.

    I would like to know what is the next thing I should do to reduce this value.


    Thanks

    micky
    Total parse is all the SQL statements sent to the parser; hard parse are those SQL statements that did not match a statement that has already been processed, and so needs to be fully parsed to get the execution plan. Soft parses are where the SQL statement did match one processed earlier, and so the plan could be reused.

    From the very large numbers you are getting for both total and hard parses, it seems that your applications are very poorly written, making very little use of bind variables, and re-parsing every time a statement is executed rather than just re-binding and re-executing the same SQL statement where possible.

    I suggest you do one/all of the following:
    - get an experienced Oracle DBA or designer to help you
    - read "Expert One on One Oracle" by Tom Kyte
    - visit asktom.oracle.com and search for "hard parse", "soft parse", "bind variables" etc. and read everything you can find on this subject.

  3. #3
    Join Date
    Mar 2003
    Location
    Singapore
    Posts
    200

    Re: High Parse to Execute Ratio - need Help

    hai

    THanks

    But the hard parse is only 21% of the total parse.So is it just the application design problem.

    Regards,

    micky

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: High Parse to Execute Ratio - need Help

    Originally posted by mickykt
    hai

    THanks

    But the hard parse is only 21% of the total parse.So is it just the application design problem.

    Regards,

    micky
    It is also 21% of the total executions, when it is supposed to be near 0.
    And the parse/execute ratio is near 100%, which is dreadful.

    I suspect that your application is VBScript or Java and that it creates each and every SQL statement by concatenating values together (not binding). So every INSERT, UPDATE and SELECT is being parsed and treated as a brand new statement, never seen before, like:

    execute('insert into emp (empno, ename) values (123, ''Joe'' )');
    execute('insert into emp (empno, ename) values (234, ''Jane'' )');
    execute('insert into emp (empno, ename) values (345, ''Fred'' )');
    execute('insert into emp (empno, ename) values (456, ''Lisa'' )');

    The above are 4 distinct SQL statements - 4 hard parses, 4 executes. Do 1000 more, and you will have 1004 hard parses.

    The correct way to write the application is to use bind variables like this (pseudocode):

    strSQL = 'insert into emp (empno, ename) values (?, ?)'
    bind( 'empno', 123 );
    bind( 'ename', 'Joe' );
    execute;
    bind( 'empno', 234 );
    bind( 'ename', 'Jane' );
    execute;
    ...

    That way you get 1 hard parse for 4, 1004, or 1,000,000 inserts into emp.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •