Oracle 11.1 自适应游标

发生自适应条件：

（1）表数据倾斜，可能由于绑定变量不同值导致不同执行计划。

（2）表有统计信息，没有统计信息不会发生自适应。

（3）绑定变量窥视（_optim_peek_user_binds），即在SQL第一次硬解析时，绑定变量窥视，

将V$SQL中IS_BIND_SENSITIVE标记为Y，下次执行时，如果生成了不同的执行计划，会将V$SQL中IS_BIND_AWARE标记为Y同时会将之前执行计划IS_BIND_SHAREABLE标记为N，会在shared pool老化时第一批老化cursor，会根据绑定变量不同传入值选择不同的执行计划，

或者生成不同的执行计划，造成SQL多Version Count。

（4）列上有直方图时。

下面为官方原文对自适应游标概念说明：

A bind-sensitive cursor is a cursor whose optimal plan may depend on the value of a bind variable.

The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.

The criteria used by the optimizer to decide whether a cursor is bind-sensitive include the following:

（1）The optimizer has peeked at the bind values to generate selectivity estimates.

（2）A histogram exists on the column containing the bind value.

BIND-AWARE

A bind-aware cursor is a bind-sensitive cursor eligible to use different plans for different bind values. 

After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its selectivity estimate.

（1）When a statement with a bind-sensitive cursor executes, the database decides whether to mark the cursor bind-aware. 

（2）The decision depends on whether the cursor produces significantly different data access patterns for different bind values. 

If the database marks the cursor bind-aware, then the next time that the cursor executes the database does the following:

（1）Generates a new plan based on the new bind value.

（2）Marks the original cursor generated for the statement as not shareable (V$SQL.IS_SHAREABLE is N).

     This cursor is no longer usable and will be among the first to be aged out of the shared SQL area.

Adaptive Cursor Sharing (Bind Sensitivity)

The first step towards ACS is recognising a query as "Bind Sensitive". 

This means that the best plan for a query has potential to be different according to the bind variables supplied to it. 

In effect this just means the cursor is being monitored to see if it should be made bind aware.

A cursor is marked as Bind Sensitive if:

（1）query is executed with bind peeking

（2）binds using any of the following relational operators = < > <= >= != or a user defined bind operator e.g. contains(e.job,:job,1)>0,

From 11.2.0.2 the "LIKE" operator is also supported.

（3）A histogram exists on the column containing the bind value.

（4）In other words, a cursor is marked Bind Sensitive if a change in the bind variable value may lead to a different plan.

Adaptive Cursor Sharing will be disabled Situation

Apart from checking for a valid operator there are also a number of subsequent bind sensitivity checks that need to be performed before it can be marked as bind sensitive. 

If any of these checks fail, the cursor will not be marked as bind sensitive and adaptive cursor sharing will not occur and Adaptive Cursor Sharing will be disabled :-

（1）Extended cursor sharing has been disabled

（2）The query has no binds

（3）Parallel query is used

（4）Certain parameters like ("_optim_peek_user_binds"=false) are set

（5）You are using a /*+ NO_BIND_AWARE */ hint

（6）Outlines are being used

（7）Query is recursive

（8）The number of binds in a given sql statement is greater than 14.  ** Could be less depending on version and setting of fix_control for Bug 10182051.  See Document : 1983132.1

Monitoring View

V$SQL can be used to see whether a cursor is_bind_sensitive, is_bind_aware, or is_shareable.

Bind context information can be viewed via V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS and V$SQL_CS_HISTOGRAM

V$SQL_CS_SELECTIVITY displays  the valid selectivity ranges for a child cursor in extended cursor sharing mode.

A valid range consists of a low and high value for each predicate containing binds. 

Each predicate's selectivity (with the current bind value) must fall between the corresponding low and high values in order for the child cursor to be shared.

V$SQL_CS_STATISTICS contains the raw execution statistics used by the monitoring component of adaptive cursor sharing.

A sample of the executions is monitored. This view shows which executions were sampled, and what the statistics were for those executions. 

The statistics are cumulative for each distinct set of bind values.

V$SQL_CS_HISTOGRAM summarizes the monitoring information stored by adaptive cursor sharing.

This information is used to decide whether to enable extended cursor sharing for a query. 

It  is stored in a histogram, whose bucket's contents are exposed by this view.

测试

相关参数：

_optimizer_adaptive_cursor_sharing

_optimizer_extended_cursor_sharing

_optim_peek_user_binds

NAME                                     VALUE           ISDEFAULT DESCRIBE                                           ISMOD      ISADJ
---------------------------------------- --------------- --------- -------------------------------------------------- ---------- -----
_optim_peek_user_binds                   TRUE            TRUE      enable peeking of user binds                       FALSE      FALSE
_optimizer_extended_cursor_sharing       UDO             TRUE      optimizer extended cursor sharing                  FALSE      FALSE
_optimizer_adaptive_cursor_sharing       TRUE            TRUE      optimizer adaptive cursor sharing                  FALSE      FALSE

测试开始：

（1）创建表

LIBAI@honor1 > create table test_bind(id number,name varchar2(10));
Table created.

（2）插入倾斜数据

declare
i number;
begin
for i in 1..100000
loop
insert into test_bind values(i,'haha');
end loop;
commit;
end;
/
declare
i number;
begin
for i in 100001..100010
loop
insert into test_bind values(i,'test');
end loop;
commit;
end;
/

（3）创建索引

LIBAI@honor1 > select count(*) from test_bind;
                                COUNT(*)
----------------------------------------
                                  100010
                                  
LIBAI@honor1 > create index idx_test_bind on test_bind(name);
Index created.

（4）未收集统计信息时查看执行计划选择

LIBAI@honor1 > set autotrace on
LIBAI@honor1 > var v_name varchar2(20);
LIBAI@honor1 > exec :v_name:='haha';
LIBAI@honor1 > select * from test_bind where name=:v_name;
Execution Plan
----------------------------------------------------------
Plan hash value: 2889536435
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   982 | 19640 |    67   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_BIND     |   982 | 19640 |    67   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_BIND |   393 |       |   112   (0)| 00:00:02 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("NAME"=:V_NAME)
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
         13  recursive calls
          0  db block gets
       6957  consistent gets
          0  physical reads
          0  redo size
    2209404  bytes sent via SQL*Net to client
      73845  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed

     

（5）发现并没有因为查询数据有倾斜发生自适应，

因为此时Oracle并不知道数据有倾斜，CBO会根据实际表数据评估执行计划，选择最优执行计划

     

LIBAI@honor1 > SELECT sql_id,
                               sql_text,
                               IS_BIND_SENSITIVE,
                               IS_BIND_AWARE,
                               IS_SHAREABLE,
                               child_number 
                          FROM v$sql
                            WHERE sql_text LIKE '%select * from test_bind where name=:v_name%';
     
     SQL_ID        SQL_TEXT                                                     I I I                             CHILD_NUMBER
------------- ------------------------------------------------------------ - - - ----------------------------------------
868x3yt87vqhy select * from test_bind where name=:v_name                   N N Y                                        0
                                  0

（6）收集统计

LIBAI@honor1 > exec dbms_stats.gather_table_stats('LIBAI','TEST_BIND');

（7）flush shared pool

SYS@honor1 > alter system flush shared_pool;

（8）第一次执行，硬解析，可以看到由于统计信息作用，Oracle知道选择列上有数据倾斜，将IS_BIND_SENSITIVE标记为

Y，发生了自适应，以便根据将来绑定变量传入值，选择合适或者解析新的执行计划。

LIBAI@honor1 > exec :v_name:='haha';
LIBAI@honor1 > select * from test_bind where name=:v_name;
LIBAI@honor1 > SELECT sql_id,
                               sql_text,
                               IS_BIND_SENSITIVE,
                               IS_BIND_AWARE,
                               IS_SHAREABLE,
                               child_number 
                          FROM v$sql
                            WHERE sql_text LIKE '%select * from test_bind where name=:v_name%';
     
     SQL_ID        SQL_TEXT                                                     I I I                             CHILD_NUMBER
------------- ------------------------------------------------------------ - - - ----------------------------------------
868x3yt87vqhy select * from test_bind where name=:v_name                   Y N Y                                        0

（9）当传入新值时，由于IS_BIND_SENSITIVE作用，Oracle选择了新的执行计划，并标记IS_BIND_AWARE为Y

LIBAI@honor1 > exec :v_name:='test';
LIBAI@honor1 > select * from test_bind where name=:v_name;
LIBAI@honor1 > SELECT sql_id,
                               sql_text,
                               IS_BIND_SENSITIVE,
                               IS_BIND_AWARE,
                               IS_SHAREABLE,
                               child_number 
                          FROM v$sql
                            WHERE sql_text LIKE '%select * from test_bind where name=:v_name%';
     
     SQL_ID        SQL_TEXT                                                     I I I                             CHILD_NUMBER
------------- ------------------------------------------------------------ - - - ----------------------------------------
868x3yt87vqhy select * from test_bind where name=:v_name                   Y Y Y                                        0