Hello,
In this blog post, I want to demonstrate the concept of bind peeking. Firstly, Bind peeking was introduced in Oracle 9i. The query optimizer peeks at the values of userdefined bind variables on the first invocation of a cursor. This feature allows the optimizer to determine the selectivity of any WHERE clause condition. Up to 10g, no further peeking takes place on subsequent invocations of the cursor and the cursor is shared based on the standard cursor-sharing criteria, even if subsequent invocations use different bind values.
From 11g, the adaptive cursor sharing feature enables a single statement that contains bind variables to use different execution plans for different binds.
OK let me prepare test environment for doing example of bind variables.
# sqlplus / as sysdba SQL>create user DBM identified by oracle SQL>default tablespace USERS SQL>quota unlimited on USERS; SQL>grant dba to dbm; SQL>connect dbm/oracle Connected. SQL> alter session set optimizer_mode='ALL_ROWS'; Enable bind peeking feature SQL> alter session set "_optim_peek_user_binds"=true; Let me test with CPU costing off SQL> alter session set "_optimizer_cost_model"='io'; SQL> create table survey ( ID number(5), beverage varchar2(20) );
Now, I have a survey that shows 80000 people prefer to drink Coco Cola while only one person drinks RedWine and another coffee.
SQL> Begin For i in 1..80000 Loop insert into survey Values (i, 'Cola'); End Loop; insert into survey Values (80001, 'RedWine'); insert into survey Values(80002, 'Coffee'); commit; End; / SQL> create index drink_idx on survey(beverage); SQL> begin dbms_stats.gather_table_stats(ownName => 'DBM', TabName => 'SURVEY', method_opt => 'For All Indexed Columns Size Auto', cascade => True); end; /
According to the Cursor_Sharing parameter value (it is exact) means that queries that differ only by their literal values will NOT be shared.
Let me examine the execution plan using autotrace in sqlplus.
SQL> set autotrace traceonly explain SQL> select * from survey where beverage= 'Cola';
Elapsed: 00:00:02.46
Execution Plan ---------------------------------------------------------- Plan hash value: 102092465 ------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost | ------------------------------------------------------------ | 0 | SELECT STATEMENT | | 26667 | 260K| 39 | |* 1 | TABLE ACCESS FULL| SURVEY | 26667 | 260K| 39 | ------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BEVERAGE"='Cola')
SQL> select count(*) from survey
where beverage= 'Cola';
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3060591156
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 29 |
| 1 | SORT AGGREGATE | | 1 | 6 | |
|* 2 | INDEX FAST FULL SCAN| DRINK_IDX | 26667 | 156K| 29 |
-------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BEVERAGE"='Cola')
You will see either Index Fast Full Scan or Table Full Access. Now let me show the plan for a different value that we know only matches 1 row.
SQL> select * from survey where beverage= 'Coffee';
Elapsed: 00:00:00.09 Execution Plan ---------------------------------------------------------- Plan hash value: 1191206642 --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 10 | 2 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| SURVEY | 1 | 10 | 2 | |* 2 | INDEX RANGE SCAN | DRINK_IDX | 1 | | 1 | ---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("BEVERAGE"='Coffee')
You see Index Range Scan, since from the statistics, the optimizer knows that there is only one row satisfying the condition. So the optimizer chooses Index Range access path.
Now let me define a bind variable and assign different values.
SQL> variable drink varchar2(20); SQL> exec :drink :='Cola'; SQL> select count(*) from survey 16:52:15 2 where beverage= :drink; Elapsed: 00:00:00.04
Execution Plan
----------------------------------------------------------
Plan hash value: 3060591156
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 50 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX FAST FULL SCAN| DRINK_IDX | 26667 | 156K| 50 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BEVERAGE"=:DRINK)
With this new query we get a new plan which has Index Fast Full Scan or FTS. If we now assign a different value for the bind variable like Coffee.
SQL> exec :drink :='Coffee'; PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 16:52:44 SQL> select count(*) from survey 16:52:48 2 where beverage= :drink; Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3060591156
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 6 | 50 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX FAST FULL SCAN| DRINK_IDX | 26667 | 156K| 50 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("BEVERAGE"=:DRINK)
The query is same, 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. Oracle has peeked on the value of :drink and chosen the optimal plan once again. When bind variables are used in a statement, it is assumed that cursor sharing is intended and that the plan chosen for any set of binds peeked will produce acceptable performance for all sets of binds. If different invocations of the cursor would significantly benefit from different execution plans and this is critical to the application, then you should consider whether using bind variables for the query is appropriate in this case.
The bind peeking which is enabled by default. Following is the command to disabled it.
alter system set "_optim_peek_user_binds" = false scope=both;
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. You can check the adaptive cursor sharing is enabled or not as below query.
select a.ksppinm name, b.ksppstvl value, b.ksppstdf deflt, decode (a.ksppity, 1, 'boolean', 2, 'string', 3, 'number', 4, 'file', a.ksppity) type, a.ksppdesc description from sys.x$ksppi a, sys.x$ksppcv b where a.indx = b.indx and a.ksppinm like '%_optimizer_extended_cursor_sharing%' order by name;
Talip Hakan Ozturk's ORACLE BLOG 