1Z0-084 practice question 7 of 15

Which two statements are true about cursor sharing?

Answer options

A. Setting Cursor_sharing to FORCE can result in a plan that is suboptimal for the majority of values bound to a bind variable when executing a cursor with one or more bind variables.

Correct answer

B. Adaptive Cursor Sharing guarantees that a suboptimal plan will never be used on any execution of a SQL statement.

C. Setting optimizer_capture_sql_plan_baselines to TRUE loads all adaptive plans for the same statement into the cursor cache.

D. Setting cursor_sharing to EXACT prevents Adaptive Cursor Sharing from being used.

E. Adaptive Cursor Sharing requires histograms on filtered columns, used in equality predicates, to allow different execution plans to be generated for statements whose bound values would normally generate different plans at hard parse time.

Correct answer

Correct Answer:

A. A. Setting Cursor_sharing to FORCE can result in a plan that is suboptimal for the majority of values bound to a bind variable when executing a cursor with one or more bind variables.; E. E. Adaptive Cursor Sharing requires histograms on filtered columns, used in equality predicates, to allow different execution plans to be generated for statements whose bound values would normally generate different plans at hard parse time.

Explanation

Setting CURSORSHARING to FORCE causes Oracle to replace literals with bind variables, leading to a single execution plan for SQL statements that differ only by those literals. If data is skewed, this single plan can be suboptimal for many literal values. Adaptive Cursor Sharing (ACS) is a feature that allows a single statement to use multiple plans based on bind variable selectivity. For the optimizer to recognize that different bind values have different selectivities, it relies on statistics, particularly histograms for columns with skewed data distributions.

Why Incorrect

B. Adaptive Cursor Sharing is a mechanism to improve plan selection but does not guarantee that a suboptimal plan will never be used.
C. optimizercapturesqlplanbaselines is a parameter related to SQL Plan Management (SPM) for capturing plans, not for loading adaptive plans into the cursor cache.
D. Adaptive Cursor Sharing is specifically designed to work when CURSORSHARING is set to EXACT to solve the bind variable peeking problem. It does not prevent it.

References

1. Oracle Database Reference 19c, "CURSORSHARING". The documentation notes for FORCE: "The plan may be suboptimal for some literals."

2. Oracle Database SQL Tuning Guide 19c, Chapter 14 Using Adaptive SQL Plans, "About Adaptive Cursor Sharing". The guide explains that the optimizer uses bind peeking and statistics to determine if a cursor should be bind-aware, which is the basis for ACS. The effectiveness of this feature depends on accurate statistics, including histograms.

3. Oracle Database SQL Tuning Guide 19c, Chapter 14 Using Adaptive SQL Plans, "Use Cases for Adaptive Cursor Sharing". The examples show ACS working with bind variables, which implies CURSORSHARING is EXACT.

Keep practicing

Take the free 1Z0-084 practice test

Ten exam-style questions with answers and explanations, plus the exam facts and study guides.

Start the free practice test