Learn how to reduce hard parses in Oracle 1z0-084 certification exam by adjusting CURSOR_SHARING and OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameters. Find effective strategies to optimize your Oracle database performance.
Table of Contents
Question
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.)
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.
Answer
C. Increase the size of the library cache.
E. Set the CURSOR_SHARING parameter to FORCE.
Explanation
The Oracle 1z0-084 exam question focuses on addressing a large number of hard parses in an AWR report, caused by several almost identical SQL statements. To reduce the number of hard parses, consider the following actions:
Set the CURSOR_SHARING parameter to FORCE (Option E):
Explanation: The CURSOR_SHARING parameter controls whether SQL statements that differ in literals but are otherwise identical can share the same cursor. Setting CURSOR_SHARING to FORCE replaces literals with system-generated bind variables, allowing the SQL statements to be shared more efficiently. This significantly reduces the number of hard parses since similar SQL statements can use the same execution plan.
Increase the size of the library cache (Option C):
Explanation: The library cache holds executable forms of SQL statements, PL/SQL programs, and Java classes. Increasing the size of the library cache helps in accommodating more SQL statements, thus reducing the need to parse them repeatedly. This can improve performance by lowering the number of hard parses.
Options Analysis:
- Option A (Create the KEEP cache and cache tables): This option focuses on improving data access speed but does not directly impact the number of hard parses.
- Option B (Create the RECYCLE cache and cache tables): Similar to Option A, this affects data caching strategies but not the parsing mechanism.
- Option D (Set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES to TRUE): While this helps in capturing and evolving execution plans, it does not directly reduce hard parses. It is more about plan stability and evolution rather than immediate reduction of hard parses.
By setting the CURSOR_SHARING parameter to FORCE and increasing the size of the library cache, you address the issue of repeated parsing of similar SQL statements effectively, leading to enhanced database performance.
Oracle 1z0-084 certification exam practice question and answer (Q&A) dump with detail explanation and reference available free, helpful to pass the Oracle 1z0-084 exam and earn Oracle 1z0-084 certification.