Which two statements are true about cursor sharing?
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.
A. When Cursor_sharing is set to FORCE, Oracle tries to avoid hard parses by replacing literals in SQL statements with bind variables, even if the original statement didn't include bind variables. This can lead to the use of a single execution plan for multiple executions of a statement with different literal values, which might not be optimal for all executions.
D. Setting cursor_sharing to EXACT ensures that SQL statements must match exactly for them to share a cursor. This setting prevents the use of Adaptive Cursor Sharing (ACS) since ACS relies on the ability to share cursors among similar statements that differ only in their literal values. With EXACT, there's no cursor sharing for statements with different literals, hence no opportunity for ACS to operate.
References:
Oracle Database SQL Tuning Guide, 19c
Oracle Database Reference, 19c
Contribute your Thoughts:
Chosen Answer:
This is a voting comment (?). You can switch to a simple comment. It is better to Upvote an existing comment if you don't have anything to add.
Submit