In your database, the CURSOR_SHARING parameter is set to FORCE.
A user issues the following SQL statement:
Select * from SH.CUSTOMERS where REIGN=’NORTH’
Which two statements are correct?
A.
The literal value ‘NORTH’ is replaced by a system-generated bind variable.
B.
Bind peeking will not happen and subsequent executions of the statement with different literal
values will use the same plan.
C.
Adaptive cursor sharing happens only if there is a histogram in the REIGN column of the
CUSTOMERS table.
D.
Adaptive cursor sharing happens irrespective of whether there is a histogram in the REIGN
column of the CUSTOMERS table.
Explanation:
CURSOR_SHARING determines what kind of SQL statements can share the same
cursors.
Values:
* FORCE
Forces statements that may differ in some literals, but are otherwise identical, to share a cursor,
unless the literals affect the meaning of the statement.
* SIMILAR
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor,
unless the literals affect either the meaning of the statement or the degree to which the plan is
optimized.
* EXACT
Only allows statements with identical text to share the same cursor.
A,D
Adaptive cursor sharing is enabled for the database by default and cannot be disabled. Adaptive cursor sharing does not apply to SQL statements containing more than 14 bind variables.
Note:
Adaptive cursor sharing is independent of the CURSOR_SHARING initialization parameter. Adaptive cursor sharing is equally applicable to statements that contain user-defined and system-generated bind variables.
I agree with vasya
A,D
Cursor_sharing = force. Whether there is a histogram or not, literal replacement will take place, and the optimizer will optimize the query as if it were: select * from employees where job = :b Bind peeking will take place, so that the value “Clerk” is used to generate cardinality estimates for the query. Subsequent executions of this query differing only in the literal value will share the same plan.
https://blogs.oracle.com/optimizer/entry/explain_adaptive_cursor_sharing_behavior_with_cursor_sharing_similar_and_force
Errata:
I’m note sure that D is correct. After investigation i think that the optimizer should have something that allows it to make a decision what the current plan for particular bind variable is ineffective. This thing could be histogram.
So A and C should be correct.
A and D are correct.
C is not correct because Cursor Sharing can works without histograms.
I think A,D