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.
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.
But the hard parse is only 21% of the total parse.So is it just the application design problem.
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):