An application frequently executed similar types of queries that vary only in the usage of literals in
the WHERE clause. You plan to use bind variable in place of literal values.
The CURSOR_SHARING parameter to set to EXACT.
Which two statements are true about the usage of bind variables?
A.
The number of latch gets in the library cache will be reduced.
B.
Bind peeking will take place and subsequent execution of queries can have different plans
based on the cardinality of the bind value in the column.
C.
Bind peeking will take place and subsequent execution of queries can have different plans only
when the histograms exists on a column used in WHERE clause.
D.
Bind peeking will not happen and the optimizer will use the same plan for all bind values if no
histograms exist on a column use in the WHERE clause.
E.
Bind peeking will happen and subsequent execution of queries will have the same parent cursor
but different child cursors.
Explanation:
CURSOR_SHARING determines what kind of SQL statements can share the same
cursors.
EXACT
Only allows statements with identical text to share the same cursor.
Note:EXACT-This is the default setting. With this value in place, the query is not rewritten to use bind
variables.
With CURSOR_SHARING=EXACT (the default), every unique SQL statement executed will create
a new entry in V$SQL, it will be hard-parsed, and an execution plan will be created just for it.
There can be hundreds or thousands of very similar queries in the shared pool that differ only in
the literals used in the SQL statement itself. This implies that the database is forced to hard-parse
virtually every query, which, in turn, not only consumes a lot of CPU cycles but also leads to
decreased scalability.
The database just cannot hard-parse hundreds or thousands of SQL statements concurrently—the
application ends up waiting for the shared pool to become available. One of the major scalability
inhibitors in the database is not using bind variables. That was the motivation behind adding
CURSOR_SHARING=FORCE .
the answers are A,E
Agree
raka, can you leave ur email, I’d like to contact you
rakasax (at) gmail (dot) com
E incorrect, because we will have more than one child cursor if there is need to generate a new, different plan for the bind variable.
In case we have one plan for all the values of bind variables, we will have only one child cursor and one parent.
A correct,
I hesitate between B and C
A & B should be correct
By peeking at bind values the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.
Agree with PS: A and B are correct.
E is not correct because subsequent execution of queries will not necessary have different child cursor. When could have execution of queries with different bind variable but that generate the same execution plan. In this case, the same child cursor and the column EXECUTION from v$sql is increment by 1.
Argh i think that A and C could be the best.
In fact, optimizer could choose an other plan if bind sensivity = Y (i.e. histogram statistics have been gathered) and if bind aware = Y.
So presence of histogram on filtered column could be necessary so that optimizer can detected skewed data and so adapt it’s execution plan.
A & B are correct.
C is not correct because Bind Peeking has nothing to do with the presence of histograms. It happens because you have a bind variable in the statement.
E is false because the new statement will have a different parent cursor; remember: the syntax is being changed from “Select … FROM … where x = y” to “x = :y”
Different Syntax => Different sql_IDs, i.e. different parent cursors.
In real exam it’s need to choose three statements!
If there are three answers to choose then A, B, E
https://docs.oracle.com/database/121/TGSQL/tgsql_cursor.htm#TGSQL94741
The optimizer uses the following criteria to decide whether a cursor is bind-sensitive:
— The optimizer has peeked at the bind values to generate cardinality estimates.
— The bind is used in an equality or a range predicate.
It means Cursor is able to be bind_sensitive without histogram. F.E. if we have range predicate on WHERE condition, :b1<my_table.my_column<:b2
It follows the answers C and D are wrong.