You are administering a database that supports an OLTP workload.CURSOR_SHARINGis
set toEXACTfor the instance. An application is frequently executing almost identical
queries that vary in literal values in theWHEREclause, causing a large number of hard
parses to occur. Which four statements would be true if you use bind variables for these
queries?(Choose four.)
A.
Mutex contention in the library cache will be reduced.
B.
The optimizer will use one parent cursor and one child cursor for each SQL statement
with different literal values.
C.
Hard parses will be reduced for the queries.
D.
The optimizer will use bind peeking and subsequent execution of the queries will always
generate the same plans irrespective of the cardinality.
E.
The optimizer will generate the same plan for all bind values if no histograms exist on the
columns used in theWHEREclause of these queries.
F.
The optimizer will use bind peeking and use the literal value to determine the execution
plan for these queries.
imo A,B,C,F right
D and E are incorrect because of adaptive cursor sharing and cardinality feedback.
A,C,E,F
imo A,C,D,E cause we don’t know if an histogram exists.. so i can’t assume it exists.
Very tricky question, but in old world Need could be correct. In 12c we have features like adaptive cursor sharing and carnality feedback so optimizer will not use the same plan for everything: http://www.oracle.com/technetwork/articles/sql/11g-sqlplanmanagement-101938.html
It means no D and E here.
So A,C,F is totally true here. Do anyone has some proof for B?
E is not necessirly true for example the cursor will be marked ***Bind Sensitive*** if the optimizer believes the optimal plan may depend on the value of the bind variable(for example, a histogram is present on the column or the predicate is a range, or )
B is wrong because of the way ACS works ===>
Peek the bind value(s) on first execution and determine the execution plan based n the bind value’s selectivity
The cursor will be marked ***Bind Sensitive*** if the optimizer believes the optimal plan may depend on the value of the bind variable(for example, a histogram is present on the column or the predicate is a range, or )
When the cursor is marked bind sensitive, Oracle monitors the behavior of the cursor using different bind values, to determine if a different plan is called for
If a different bind value is used in a subsequent execution, the CBO will use the existing cursor and execution plan because Oracle initially assures the cursors can be shared.
However, the execution statistics for the new bind value will be recorded and compared to the execution statistics for the previous value.
If Oracle determines that the new bind value caused the data volumes manipulated by the query to be significantly different it ***ADAPTS***and hard parses on the new bind value on its next execution and the cursor is marked ***BIND-AWARE***. Each bind-aware cursor is associated with a selectivity range of the bind so that the cursor is only shared for a statement when the bind value in the statement is believed to fall within the range.
When another new bind value used, CBO tries to find a cursor it thinks will be a good fit, based on similarity in the bind value’s selectivity. If it cannot find such a cursor, it will create a new one
If the plan for the new cursor is the same as an existing cursor, the two cursors will merged to save space in the shared pool. And, the selectivity range for that cursor will be increased to include the selectivity of the new bind
So ACF are correct 100%
E could be correct but not absoulty correct1
So valid to go with ACEF
E is wrong if B is proved to be right! And, B is right if the answer reads as:
“The optimizer will use one parent cursor and one child cursor for each SQL statement
with different literal values.”
The optimizer will use one parent cursor ***ONLY***, and one child cursor for each SQL statement
with different literal values.
So answer is ABCF!
Mb B is wrong, pls see:
http://oracleinaction.com/parent-child-curosr/
OBJECT TRANSLATIONS
If a statement references different objects with the same name then multiple child cursors can be generated
For example, we will create two tables with same name t1 with same structure in HR and SCOTT schema and issue identical SQl statement referring to the two tables.
SYS>ALTER SYSTEM FLUSH SHARED_POOL;
HR>CREATE TABLE t1 (c1 NUMBER);
SELECT c1 FROM t1;
SCOTT>CREATE TABLE t1 (c1 NUMBER);
SELECT c1 FROM t1;
The statement SELECT c1 FROM t1 will have a shared parent cursor, but multiple child cursors
Parent (select c1 from t1;)
+
+————–+————-+
| |
Child I Child II
(HR.t1) (SCOTT.t1)
This question is crap.
B is 100% wrong. adaptive cursor sharing | bind aware cursors.
D is 100% wrong. Again adaptive cursor sharing | bind aware cursors.
E is 100% wrong. Oracle doesnt need a histogram to know fist that a cursor might have to be bind sensitive and because where id < 10 might produce many fewer rows than where id < 10000000 and that if the conditions apply, you may have to make the cursor bind aware. Nowhere it mentions anything about histograms being needed
The question is wrong.
Even F, like what do you mean by "will use bind peeking". Do you mean every time, only the first time? It should say the first time, otherwise it's ambiguous and could be even be discarded as well…
A WRONG . Mutex contention in the library cache will be reduced.
Many child cursor does not create more mutex contention. It only consumes resource.
So why would reusing the same cursor generate less contention ??
B. WRONG The optimizer will use one parent cursor and one child cursor for each SQL statement with different literal values.
C. RIGHT Hard parses will be reduced for the queries.
D. RIGHT The optimizer will use bind peeking and subsequent execution of the queries will always generate the same plans irrespective of the cardinality.
We can’t know if ACS is available
E. RIGHT The optimizer will generate the same plan for all bind values if no histograms exist on the columns used in theWHEREclause of these queries.
Without histograms, even ACS can’t help…
F. RIGHT The optimizer will use bind peeking and use the literal value to determine the execution plan for these queries.
He will, even only one time
https://asktom.oracle.com/pls/asktom/f?p=100:11:::NO:RP:P11_QUESTION_ID:9532861000346019011