Home » Oracle » 1z0-117 » Which two statements are true about the usage of bind variables?
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.
Correct Answer: AB
Explanation/Reference:
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 .
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
