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 alwaysbe 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.
Correct Answer: ABD
Explanation/Reference:
* We here see that the cursor is marked as bind sensitive (IS_BIND_SENis 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 monitorsthe 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 statements cannot shared; only textually exact statements can be shared.
Reference:Why are there more cursors in 11g for my query containing bind variables?
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
