Explain Adaptive Cursor Sharing & Bind peeking in Oracle
Bind peeking was introduced in Oracle 9i and 10g.
The query optimizer peeks at the values of user-defined bind variables on the first invocation of a cursor.
This feature allows the optimizer to determine the selectivity of any WHERE clause condition.
With cursor_sharing=force, all query start using bind variable which releaf the shared pool but may caused performance issue in terms of choosing the execution plan for different bind variable values.
Because the query is identical, it is shared and the first plan is re-used even though the value for the bind variable has changed. In this case the same plan is not the optimal plan for this value
The bind peeking which is enabled by default.
Following is the command to disabled it.
-- for session
alter session set "_optim_peek_user_binds" = FALSE;
-- for system
alter system set "_optim_peek_user_binds" = false scope=both;
Note: it must be realized that having bind peeking may result in unpredictable execution plans dependent on
the first set of bind values presented to the optimizer on hard parse.
For Change the plan which is generated first for bind variable:
You can do one of the following:
1. Issue the command Alter System Flush Shared_Pool;
2. Delete or change the cursor underlying objects statistics
3. Grant and/or revoke privileges on any of the cursor underlying objects
4. Alter cursor underlying objects
5. Bounce the instance
Adaptive Cursor Sharing
Adaptive cursor sharing is introduced in Oracle 11g version.
ACS is help in choosing the different execution plan for different bind variables value for SQL Query.
From 11g, the adaptive cursor sharing feature enables a single statement that contains bind variables to use different execution plans for different binds.
Overcome the use of single execution plan in case of bind variables.
Adaptive cursor sharing is a solution to provide the shareability of binds, with the plan adaptability of literals.
There may be some extra overhead associated with Adaptive Cursor Sharing:
More Hard Parses (CPU) – Extra Hard Parses will be required when a cursor becomes “Bind Aware” as we attempt to generate the better matched execution plans for the bind selectivity.
More Child Cursors (SGA) – It is recommended that some consideration be taken to increase the size of the shared_pool on upgrade from 10g to 11g, given the extra cursors that may be required to accommodate this feature.
More Work to Match the Cursor (CPU) – More Child Cursors and the requirement to match a query to the best execution plan for its predicate selectivity.
Check Status of Adaptive cursor sharing
col parameter for a35
col "Session value" for a10
col "Instance value" for a10
SELECT x.ksppinm "Parameter",
Y.ksppstvl "Session Value",
Z.ksppstvl "Instance Value"
FROM x$ksppi X,
WHERE x.indx = Y.indx
AND x.indx = z.indx
AND x.ksppinm like '_optimizer_extended_cursor_sharing%'
order by x.ksppinm;
Parameter Session Va Instance V ----------------------------------- ---------- ---------- _optimizer_adaptive_cursor_sharing TRUE TRUE