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…

The correct answer is 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. D. The instance collects statistics and based on the pattern of executions creates a histogram on the column. 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…

Understanding and Influencing the Optimizer

Question

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?

Exhibit

Options

AThe DEPTNO column will become bind aware once histogram statistics are collected.
BThe value for the bind variable will considered by the optimizer to determine the execution plan.
CThe same execution plan will always be used irrespective of the bind variable value.
DThe instance collects statistics and based on the pattern of executions creates a histogram on the column
EBind peeking will take place only for the first execution of the statement and subsequent execution will

How the community answered

(30 responses)

A
80% (24)
C
7% (2)
E
13% (4)

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 * (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?

Topics

#adaptive cursor sharing#bind peeking#histogram#skewed data

Community Discussion

No community discussion yet for this question.

Full 1Z0-117 Practice