Oracle Database Administration 2022 Certified Professional — Question 30

Which two statements are true about cursor sharing? (Choose two.)

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.
• 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: A, D

Explanation

Answer A is correct because setting cursor_sharing to FORCE can lead to suboptimal execution plans for different bound variable values. Answer D is also correct as setting cursor_sharing to EXACT disables Adaptive Cursor Sharing, which is intended to optimize execution plans based on differing bind values. The other options provide incorrect information about the behavior and requirements of cursor sharing and adaptive cursor sharing.