DPA Results - Procedure Cache Hit Ratio - HIGH - Alarm Level : Critical - Advice Recommendations.

satyakiran

Please advice us any recommendations based on the below results.

Resource: Procedure Cache Hit Ratio - HIGH
  Alarm Level: Critical
  100% of data values were in critical state

Resource: Buffer Cache Hit Ratio - NORMAL
  Alarm Level: Normal
  No data values were in warning or critical state

Resource: Host Memory Usage - NORMAL
  Alarm Level: Normal
  No data values were in warning or critical state

Resource: O/S Memory Utilization (%) - NORMAL
  Alarm Level: Normal
  No data values were in warning or critical state

Resource: Page Life Expectancy - NORMAL
  Alarm Level: Normal
  No data values were in warning or critical state

Resource: VM Active Memory Usage - NORMAL
  Alarm Level: Normal
  No data values were in warning or critical state

Resource: VM Memory Swap In Rate (KB/s) - NORMAL
  Alarm Level: Normal
  No data values were in warning or critical state

Resource: VM Memory Swap Out Rate (KB/s) - NORMAL
  Alarm Level: Normal
  No data values were in warning or critical state 

mandevil

Generally speaking, in the absence of any other memory pressure, a low procedure cache hit ratio means that when the DBMS goes to see if that statement has run previously and doesn't need to be parsed again, it doesn't find it in cache. This places more overhead on the DBMS because it then has to parse the statement, generate and optimized query plan, etc. Likely reasons for this are non-parameterized SQL statements (using literals that keep changing) or a LOT of ad hoc queries.

If the queries are using lots of literals, work with developers or third party application support to see if they might consider refactoring using parameters.