[ORACLE]自适应游标共享Adaptive Cursor Sharing

V$SQL

这个视图有3个和ACS相关的列
  -- IS_BIND_SENSITIVE 用于监视SQL游标是否是绑定敏感,是否可用Adaptive Cursor Sharing
  -- IS_BIND_AWARE 用于监视SQL游标是否需要根据绑定变量值来选择计划
  -- IS_SHAREABLE 用于监视SQL游标是否可以共享

v$SQL_SHARED_CURSOR  --查看SQL游标没有共享的原因
v$SQL_CS_SELECTIVITY --查看SQL游标的各个变量选择范围(SELECTIVY CUBE),它包含着各个条件谓词,绑定变量值以及它的最大值和最小值等信息
v$SQL_CS_HISTOGRAM --根据所操作行数,记录每个子游标执行次数直方图
v$SQL_CS_STATISTICS --每个子游标执行的执行状况.(采样信息)

select IS_BIND_SENSITIVE,IS_BIND_AWARE,IS_SHAREABLE,SQL_TEXT,SQL_ID,CHILD_NUMBER,EXECUTIONS,BUFFER_GETS,HASH_VALUE,PLAN_HASH_VALUE from v$sql where SQL_TEXT like ‘select count(pad) from t where id<=:id‘;
select SQL_ID,CHILD_NUMBER,BIND_EQUIV_FAILURE,LOAD_OPTIMIZER_STATS from V$SQL_SHARED_CURSOR where SQL_ID=‘3dn9naksd7chh‘;
select * from v$SQL_CS_STATISTICS where SQL_ID=‘3dn9naksd7chh‘;
select * from V$SQL_CS_SELECTIVITY where SQL_ID=‘3dn9naksd7chh‘;
select * from v$SQL_CS_HISTOGRAM where SQL_ID=‘3dn9naksd7chh‘;

1.构建Ｔ表，数据及主键

variable id number
column sql_id new value sql_id
drop table t;
create table t as select rownum as id,rpad(‘*‘,100,‘*‘) as pad from dual connect by level <= 1000;
alter table t add constraint t_pk primary key (id);

2.收集统计信息

begin
      dbms_stats.gather_table_stats(ownname => ‘C##SAPR3‘,
      tabname => ‘T‘,
      estimate_percent => 100,
      method_opt => ‘for all columns size skewonly‘
      );
end;
/

3. 查表T当前的分布情况

SQL> select count(id),count(distinct id) ,min(id),max(id) from T;

 COUNT(ID) COUNT(DISTINCTID)    MIN(ID)    MAX(ID)
---------- ----------------- ---------- ----------
      1000              1000          1       1000

4.发现当前情况下,要吧区分出数据分布而正确使用执行计划

set linesize 1000
set autotrace traceonly explain
select count(pad) from test where id< 990; 
Execution Plan
----------------------------------------------------------
Plan hash value: 1950795681

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |   105 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |   105 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| TEST |   990 |   101K|     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - inmemory("ID"<990)
       filter("ID"<990)


select count(pad) from test where id< 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2239902560

---------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |     1 |   105 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                      |      |     1 |   105 |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST |     9 |   945 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | T_PK |     9 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("ID"<10)

5现将id的值改为变量实验一下绑定变量的SQL是否能使用真直方图
首先代入990,发现走全表扫描,正确

execute :id :=990;
select count(pad) from t where :id<=:id;
SQL> select count(pad) from TEST where :id<=:id;

COUNT(PAD)
----------
      1000

select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));
Plan hash value: 1617223730

-------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |      |      1 |      1 |   101 |            |          |      1 |00:00:00.01 |       3 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   FILTER                     |      |      1 |        |       |            |          |   1000 |00:00:00.01 |       3 |
|   3 |    TABLE ACCESS INMEMORY FULL| TEST |      1 |   1000 |    98K|     3   (0)| 00:00:01 |   1000 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------------------------
SQL> select SQL_ID,CHILD_NUMBER,BIND_EQUIV_FAILURE,LOAD_OPTIMIZER_STATS from V$SQL_SHARED_CURSOR where SQL_ID=‘4c2ncvqa02kpm‘;

SQL_ID        CHILD_NUMBER B L
------------- ------------ - -
4c2ncvqa02kpm            0 N N

--接着代入10,发现仍走index,SQL_ID 发现更变

execute :id :=10;

select count(pad) from TEST where id<=:id;
COUNT(PAD)
----------
        10

select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d44bhvafdpckv, child number 0
-------------------------------------
select count(pad) from TEST where id<=:id

Plan hash value: 2239902560

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |   105 |            |          |      1 |00:00:00.01 |       3 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST |      1 |     10 |  1050 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                  | T_PK |      1 |     10 |       |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------------

6.共享池清空,很重要一步,保证硬解析

alter system flush shared_pool; #清空之后,所有的如下视图为空
v$SQL_SHARED_CURSOR  --查看SQL游标没有共享的原因
v$SQL_CS_SELECTIVITY --查看SQL游标的各个变量选择范围(SELECTIVY CUBE),它包含着各个条件谓词,绑定变量值以及它的最大值和最小值等信息
v$SQL_CS_HISTOGRAM --根据所操作行数,记录每个子游标执行次数直方图
v$SQL_CS_STATISTICS --每个子游标执行的执行状况.(采样信息)

--接着代入10,发现可以使用直方图,执行计划为索引,很正常 使用SQLID : d44bhvafdpckv

execute :id :=10;
select count(pad) from t where id<=:id;
select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d44bhvafdpckv, child number 0
-------------------------------------
select count(pad) from TEST where id<=:id

Plan hash value: 2239902560

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |   105 |            |          |      1 |00:00:00.01 |       3 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST |      1 |     10 |  1050 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                  | T_PK |      1 |     10 |       |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------------

 可以看到IS_BIND_SENSITIVE 和IS_SHAREABLE 都 变成Y, IS_BIND_AWARE还是N，　出现一条SQL_SHARED_CURSOR 语句,出瑞三条v$SQL_CS_HISTOGRAM记录

--代入990,发现异常,仍然走索引,这个时间应走全表扫描

execute :id :=990;
select count(pad) from t where id<=:id;
select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d44bhvafdpckv, child number 0
-------------------------------------
select count(pad) from TEST where id<=:id

Plan hash value: 2239902560

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       5 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |   105 |            |          |      1 |00:00:00.01 |       5 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST |      1 |     10 |  1050 |     3   (0)| 00:00:01 |    990 |00:00:00.01 |       5 |
|*  3 |    INDEX RANGE SCAN                  | T_PK |      1 |     10 |       |     2   (0)| 00:00:01 |    990 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------------------------------

些时,v$sql_shared_cursor 中LOAD_OPTIMIZER_STATS 为Y,表示可以优化.

再次执行,走全表扫描,出现child number 1

select count(pad) from t where id<=:id;
select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  d44bhvafdpckv, child number 1
-------------------------------------
select count(pad) from TEST where id<=:id

Plan hash value: 1950795681

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE             |      |      1 |      1 |   105 |            |          |      1 |00:00:00.01 |       3 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   TABLE ACCESS INMEMORY FULL| TEST |      1 |    991 |   101K|     3   (0)| 00:00:01 |    990 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------------------------

sql_shared_cursor 中BIND_EQUIV_FAILURE 失效.

 再次执行,出现child number 2

execute :id :=10;
select count(pad) from t where id<=:id;
select * from table(dbms_xplan.display_cursor(null,null,‘ADVANCED ALLSTATS LAST PEEKED_BINDS‘));

SQL_ID  d44bhvafdpckv, child number 2
-------------------------------------
select count(pad) from TEST where id<=:id

Plan hash value: 2239902560

---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |      |      1 |        |       |     3 (100)|          |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |   105 |            |          |      1 |00:00:00.01 |       3 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| TEST |      1 |     10 |  1050 |     3   (0)| 00:00:01 |     10 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN                  | T_PK |      1 |     10 |       |     2   (0)| 00:00:01 |     10 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------------