In Your Database, The Cursor_Shareing Parameter is set to EXACT. In the Employees table, the
data is significantly skewed in the DEPTNO column. The value 10 is found in 97% of rows.
Examine the following command and out put.
Which three statements are correct?
A.
The DEPTNO column will become bind aware once histogram statistics are collected.
B.
The value for the bind variable will considered by the optimizer to determine the execution plan.
C.
The same execution plan will always be used irrespective of the bind variable value.
D.
The instance collects statistics and based on the pattern of executions creates a histogram on
the column containing the bind value.
E.
Bind peeking will take place only for the first execution of the statement and subsequent
execution will use the same plan.
Explanation:
* We here see that the cursor is marked as bind sensitive (IS_BIND_SEN is Y).
* In 11g, the optimizer has been enhanced to allow multiple execution plans to be used for a single
statement that uses bind variables. This ensures that the best execution plan will be used
depending on the bind value.
* A cursor is marked bind sensitive if the optimizer believes the optimal plan may depend on the
value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors the behavior
of the cursor using different bind values, to determine if a different plan for different bind values is
called for.
* (B, not C): A cursor is marked bind sensitive if the optimizer believes the optimal plan may
depend on the value of the bind variable. When a cursor is marked bind sensitive, Oracle monitors
the behavior of the cursor using different bind values, to determine if a different plan for different
bind values is called for.
Note: Setting CURSOR_SHARING to EXACT allows SQL statements to share the SQL area only
when their texts match exactly. This is the default behavior. Using this setting, similar statementscannot shared; only textually exact statements can be shared.
Reference: Why are there more cursors in 11g for my query containing bind variables?
E correct.
A incorrect.
As for E:
the cursor is NOT bind aware now (IS_BIND_AWA=N). That means the next execution ot the query will use the same execution plan.
As long as the cursor doesn’t become bind aware, the database can’t use different plan for the future executions.
As for A:
the cursor is bind sensitive now (IS_BIND_SENS=Y). That means the histograms already exists on the column.
The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:
– The optimizer has peeked at the bind values to generate selectivity estimates.
– A histogram exists on the column containing the bind value.
yes, since A & C are incorrect.
I will bet on B, D & E.
“A.
The DEPTNO column will become bind aware once histogram statistics are collected.” – cursor, not column may be bind aware::) B and D sould be correct