DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Statspack - very low execute to parse ratio.

  1. #1
    Join Date
    Aug 2000
    Posts
    236

    Statspack - very low execute to parse ratio.

    Used statspack to analyze performance:
    Here is one section:

    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Buffer Nowait %: 100.00 Redo NoWait : 100.00
    Buffer Hit %: 99.66 In-memory Sort %: 99.92
    Library Hit %: 99.93 Soft Parse %: 99.89
    Execute to Parse %: 0.62 Latch Hit %: 100.00
    Parse CPU to Parse Elapsd %: 91.65 % Non-Parse CPU: 97.10

    Everything looks OK except the extremely low Execute to parse ratio.
    I was using CURSOR_SHARING = SIMILAR.

    Any suggestions as to how to force Oracle to execute more for every parse. My application is a complex telecom facility design solution.

    Thanks,

    Nizar

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758

    Re: Statspack - very low execute to parse ratio.

    Originally posted by nabaig
    Any suggestions as to how to force Oracle to execute more for every parse. My application is a complex telecom facility design solution.

    Thanks,

    Nizar
    You can't make Oracle to execute more but you can reduce the parsing.
    Make sure your application uses bind variables. Pin the freequently used pl/sql code in library cache. Check the size of your shared pool.
    Read this for more information.
    http://download-west.oracle.com/docs...mory.htm#30971
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  3. #3
    Join Date
    Aug 2000
    Posts
    236
    I dont think my parses are high. Oracle does find a parsed version of the SQL in the library cache as seen from the lib cache hit % at 98.

    However Oracle is doing a soft parse almost always. When I changed CURSOR_SHARING to force from similar, the % of execution went up to 45%. But |Oracle manual says that when FORCE is used, this leads to a detioration of the execution plan. How? I am not clear.

    There are no hard coded variables in my application; uses bind whereever possible?

    Any comments?

    Nizar

  4. #4
    Join Date
    Jul 2003
    Posts
    59
    The execution plan can deteriorate as a result of the optimizer not having access to the constants in the where clause. Constants are turned into bind variables, so it doesn't know the values being used anymore. Values are useful if there are histogram statistics, and for eg, the where clause says "where x > 100". Say the statistics indicate that > 50% of the rows meet this criteria. The optimizer would probably not use an index, and that would be best. But, if this 100 is turned into a bind variable, the the optimizer would probably choose to use an index, which would most likely be a bad move, in this case.

    Take a look at your v$sql and look for constants. I have to think you have some statements in there that are using constants.

    Of course, it is possible that the nature of your app is to execute each statement (with bind variables) just once or twice, but that's unlikely, I'd think. Is that a possibility? Did you let it run long enough to fill up the lib cache before statspacking it?
    Tom Best

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