high parse to execute ratio
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: high parse to execute ratio

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    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

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

Posting Permissions

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



Click Here to Expand Forum to Full Width