Home » Oracle » 1z0-117 » Which two methods can you use to reduce hard parsing?
You are working on a database that supports an OLTP workload. You see a large number of hard parses occurring and several almost identical SQL statements in the library cache that vary only in the literal values in the WHERE clause conditions.
Which two methods can you use to reduce hard parsing?
A. Replace literals with bind variables and evolve a baseline for the statement.
B. Use the RESULT_CACHE hint in the queries.
C. Create baselines for the almost identical SQL statement by manually loading them from the cursor cache.
D. Set the CURSOR_SHARING parameter to SIMILAR.
Correct Answer: D
Explanation/Reference:
A:We can reduce this Hard parsing by using bindvariables
D:SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
Note:
A hard parse is when your SQL must be re-loaded into the shared pool. A hard parse is worse than a soft parse because of the overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated.
Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.
See the cursor_sharing parameter for a easy way to make SQL reentrant and remember that you should always use host variables in you SQL so that they can be reentrant.
Reference: Oracle Database Reference, CURSOR_SHARING
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
