Oracle Database Administration 2022 Certified Professional — Question 1

The CURSOR_SHARING and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters are set to default. The top five wait events in an AWR report are due to a large number of hard parses because of several almost identical SQL statements.
Which two actions could reduce the number of hard parses? (Choose two.)

Answer options

• A. Create the KEEP cache and cache tables accessed by the SQL statements.
• B. Create the RECYCLE cache and cache tables accessed by the SQL statements.
• C. Increase the size of the library cache.
• D. Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE.
• E. Set the CURSOR_SHARING parameter to FORCE.

Correct answer: D, E

Explanation

Setting OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE allows the optimizer to capture and use SQL plan baselines, reducing hard parsing by reusing execution plans. Additionally, changing the CURSOR_SHARING parameter to FORCE allows similar SQL statements to share the same cursor, further decreasing the need for hard parses. The other options do not directly address the issue of hard parses as effectively as these two.