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…

Question

Options

• AReplace literals with bind variables and evolve a baseline for the statement.
• BUse the RESULT_CACHE hint in the queries.
• CCreate baselines for the almost identical SQL statement by manually loading them from the cursor cache.
• DSet the CURSOR_SHARING parameter to SIMILAR.

• A
  4% (2)
• B
  15% (7)
• C
  6% (3)
• D
  74% (35)

Explanation

A: We can reduce this Hard parsing by using bindvariables 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 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.

No community discussion yet for this question.