Causes of HARD parsing and solution
Usually hard parses are due to large statements involving partitioned objects. They also causes high CPU usage.
Case 1:
Optimizer is using dymanic sampling which impacts the parse time
Dynamic sampling is performed by the CBO at parse time when it is either requested via hint or parameter, or by default because statistics are missing.
Depending on the level of the dynamic sampling, it may take some time to complete.
This time is reflected in the parse time for the statement
From 10g, use SQL Tuning Advisor to generate a profile for the query and accept it.
Find the hints needed to implement the plan normally generated with dynamic sampling and modify the query with the hints.
Case 2:
Many queries are being HARD parsed
Hard parsing can happen due to unshared SQL statments (by using Literals in the statement)
if the "Misses in the library cache" is close to total number of parses, hard parsing can be sensed.
Identifying and modifying (rewriting) the SQL statements being hard parsed statements to use bind values could solve the
issue.
To make the statement use bind variables, we can se CURSOR_SHARING parameter in init.ora.
The following are the options set based on the desired output.
EXACT - Leave the statement as it was written with literals (default value).
FORCE - Substitute all literals with binds (as much as possible).
SIMILAR - Substitute literals with binds only if the query's execution plan won't change.
Case 3:
Hard parsing due to the shared SQL aged out
If the shared pool is too small, it causes many statements that could be shared to age out of the library cache and later
reloaded.
Each reload requires a hard parse and impacts the CPU and latches.
This can be identified by AWR report in Library cache statistics section. This section shows more "reloads" count.
Increasing the sharedpool size could reduce the number of statements being aged out.
Implement Automatic Shared Memory Management(ASMM) by setting SGA_TARGET value greater than 0. This will manage the shared
pool size automatically.
Keeping the frequently used SQL and PL/SQL statements in the sharedpool (pinning) is one of the method to avoid SQL
statement age out issues.
This can be done using DBMS_SHARED_POOL.KEEP() procedure.
This will reduce reloads and fragmentation since the object doesn't need to keep reentering the shared pool over and over.
Usually hard parses are due to large statements involving partitioned objects. They also causes high CPU usage.
Case 1:
Optimizer is using dymanic sampling which impacts the parse time
Dynamic sampling is performed by the CBO at parse time when it is either requested via hint or parameter, or by default because statistics are missing.
Depending on the level of the dynamic sampling, it may take some time to complete.
This time is reflected in the parse time for the statement
From 10g, use SQL Tuning Advisor to generate a profile for the query and accept it.
Find the hints needed to implement the plan normally generated with dynamic sampling and modify the query with the hints.
Case 2:
Many queries are being HARD parsed
Hard parsing can happen due to unshared SQL statments (by using Literals in the statement)
if the "Misses in the library cache" is close to total number of parses, hard parsing can be sensed.
Identifying and modifying (rewriting) the SQL statements being hard parsed statements to use bind values could solve the
issue.
To make the statement use bind variables, we can se CURSOR_SHARING parameter in init.ora.
The following are the options set based on the desired output.
EXACT - Leave the statement as it was written with literals (default value).
FORCE - Substitute all literals with binds (as much as possible).
SIMILAR - Substitute literals with binds only if the query's execution plan won't change.
Case 3:
Hard parsing due to the shared SQL aged out
If the shared pool is too small, it causes many statements that could be shared to age out of the library cache and later
reloaded.
Each reload requires a hard parse and impacts the CPU and latches.
This can be identified by AWR report in Library cache statistics section. This section shows more "reloads" count.
Increasing the sharedpool size could reduce the number of statements being aged out.
Implement Automatic Shared Memory Management(ASMM) by setting SGA_TARGET value greater than 0. This will manage the shared
pool size automatically.
Keeping the frequently used SQL and PL/SQL statements in the sharedpool (pinning) is one of the method to avoid SQL
statement age out issues.
This can be done using DBMS_SHARED_POOL.KEEP() procedure.
This will reduce reloads and fragmentation since the object doesn't need to keep reentering the shared pool over and over.
No comments:
Post a Comment