high parse to execute ratio

hi,

thanks for your earlier responses!

in this post i would take up couple of performance related issues. these are regarding the high "parse to execute ratio" and high "cpu parse overhead". i am gtting a parse to execute ratio of 38 and "cpu parse overhead" is 22. i am getting all these statistics from TOAD.Pls mention what to interpret from these values and how to rectify them.

thanks once again.

Parijat paul

The high parse ratio depends on the application and what you care about.

Personally, I don't care at all about soft parses. I think it is an antiquated concept. I rarely, if ever, have a loop in PL/SQL where I can execute the same SQL and just change the params. If that were the case, I would likely use straight SQL, duh. Otherwise, I would use BULK operations to run the same statement with multiple params, etc. There is rarely a case where you will parse the statement once and then execute it multiple times in a loop to save on soft parses.

But, the ratio that TOAD shows is all parses vs all executions, so the soft parses are included and it looks bad. Ignore it. Take a look at the hard parses and see if that bothers you.

Again, it depends on the the application and the database. If you have a very evenly distributed database that requires fast,fast,fast access, then you'd best be using binds everywhere and cursor-sharing and your hard parses better be low. But if you have wildly distributed data and need to use histograms and constants to get any decent performance, then your hard parses are going to be high - no way around it.

But the key to hard parsing is binds, whether explicitely used or forced with plan stability, etc.

- Chris