Introduction
This custom metric measures the number of hard parses per second during the execution interval in DPA. A hard parse occurs when a SQL has to be loaded into the Oracle Shared Pool. Higher values of hard parses occur when many different SQL statements are being executed.
Causes
When this value spikes, review the queries that executed within the interval. If your execution interval is 1 minute, review queries from the data point backwards by 1 minute. For example, if the high data point is seen at 1:05 pm, review data from 1:04 - 1:05pm. Some causes and solutions for this issues are:
- Queries that use literal values versus bind variables. If this is a problem, the parameter CURSOR_SHARING can be set to FORCE or SIMILAR to cause Oracle to replace literal values with bind variables.
- This problem can also be caused by shared pool that is too small for the query workload. Consider giving more memory to Oracle if this is the case.
Custom Metric in DPA
Create a Rate type custom metric in DPA because Oracle stores the total hard parses in v$systat since instance startup. Using a Rate type metric will cause DPA to collect data once a minute and perform a delta between the 2 values and divide by 60 (execution interval) to get the hard parses per second:
