Hard parsing is a complete SQL compilation process, which is performed for each SQL query when the hit rate of the plan cache is low. Therefore, hard parsing increases the SQL execution duration.
You can check the values of the GET_PLAN_TIME field in the V$OB_SQL_AUDIT view to determine whether the time consumed to generate an execution plan is abnormal. The duration of plan generation is usually less than 0.1 ms, but can be increased to more than 100 ms in the case of a request exception.
Note
For more information about the V$OB_SQL_AUDIT view, see V$OB_SQL_AUDIT (Oracle mode) or V$OB_SQL_AUDIT (MySQL mode).
Hard parsing may occur in the following scenarios:
A small cache size is configured, and execution plans are therefore frequently evicted.
The SQL parameters are normalized, and many execution plans are therefore generated for the same type of SQL statements.
You can use the following methods to solve this issue:
Increase the size of the plan cache.
You can use the system variable
ob_plan_cache_percentageto specify the percentage of tenant memory available for the plan cache. The absolute value of the maximum memory size available for the plan cache is calculated by using the following formula: Maximum memory size for the tenant ×ob_plan_cache_percentage/100. The default value ofob_plan_cache_percentageis5.