Which two actions should you perform to make the cursor…

Examine the structure of theEMPLOYEEStable.

EMPLOYEE_ID is the primary key. No other indexes exist on this table. View the Exhibit
to examine the commands and their output.

Which two actions should you perform to make the cursor bind aware?(Choose two.)

Examine the structure of theEMPLOYEEStable.

EMPLOYEE_ID is the primary key. No other indexes exist on this table. View the Exhibit
to examine the commands and their output.

Which two actions should you perform to make the cursor bind aware?(Choose two.)

A.
Create a histogram on theDEPARTMENT_IDcolumn.

B.
Change the defaultCURSOR_SHARINGvalue toFORCE.

C.
Execute the query with the sameDEPARTMENT_IDvalue multiple times.

D.
Create an index on theDEPARTMENT_IDcolumn.

E.
Gather statistics for the index.

F.
Regather statistics on the table.

Show Hint

← Previous question

Next question →

Leave a Reply 10

vasya

A, C

diablo

I think C,D. because I want enable BIND_AWARE…to do it, we have to force optimizer to generate another better execution plan against this one.
Current execution plan use a FST on EMPLOYEES and with histogram statistic we haven’t improvement on execution plan because 45 rows will be gotten (about 45% of total).
On other side, if we create an index on DEPARTMENT_ID, the optimizer have to generate a better plan that use FFST on the index and don’t need direct access to table data.

vasya

if you don’t have histograms the optimizer will not peek at the binds and therefore if won’t first make a cursor bind sensitive and then bind aware.

vasya

The question is how to make the cursor bind aware.
So as to make a cursor bind aware we first need to make it bind sensitive.
A bind sensitive cursor – when the optimizer knows that the data in predicate column is skewed. And this can be found out from the histogram.

Need

With hystogram it should become bind sensitive, with an index it would become bind aware because of the new plan through index.
So A,D takes precedence over C imo.