【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能

本文主要是介绍【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

概述

我们知道，由于绑定变量窥视(Bind Peeking)功能，SQL文在进行硬解析（Hard Parse）时，会代入绑定变量的值来估算选择基数（cardinality ）并做成执行计划，而相同的SQL文以后在执行过程中，都会共享初次执行时做成的执行计划。

如果表的数据分布不均或者数据倾斜时，用于估算的变量值和以后执行中的用到的变量值估算选择基数（cardinality ）差异很大时，通过绑定变量窥视(Bind Peeking)功能做成的执行计划，针对某些变量值的执行可能都是最优的，甚至可能引起很严重的性能问题。

因此，优化器在11.1以后的版本上，为了解决绑定变量窥视(Bind Peeking)的问题，推出了自适应游标共享(Adaptive Cursor Sharing 以后简称ACS）功能，使包含绑定变量的同一条SQL语句在多次执行时，不会盲目的共享执行计划，而会根据绑定变量值和执行过程中收集信息的反馈，可以使用多个不同执行计划，避免性能问题。

下面我们将通过几个例子来进一步了解ACS功能。

例子1（ACS无效）

首先，我们看一下在绑定变量窥视(Bind Peeking)功能有效，但是自适应游标共享功能无效的情况下，执行以下SQL文的动作。（基于10.2.0.5版本测试）

0.准备测试用表和数据

SQL> conn scott/tiger
Connected.
SQL> create table TEST_ACS(c1 number, c2 varchar2(100));Table created.SQL> BEGIN FOR i IN 1 .. 1000 LOOP FOR j IN 1 .. 10 LOOP INSERT INTO TEST_ACS VALUES     (i,'a');COMMIT; END LOOP; END LOOP; 
END; 
/  2    3    4    5    6    7    8    9   10  PL/SQL procedure successfully completed.SQL> create index ind1 on TEST_ACS(c1);Index created.SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'TEST_ACS',CASCADE => TRUE); PL/SQL procedure successfully completed.---我们准备了一个表包含10000条数据，c1列有1~1000的值，并且每个值都有10条数据。
---并且在c1列有个索引ind1SQL> SELECT count(c1), count(DISTINCT c1), min(c1), max(c1) FROM TEST_ACS;COUNT(C1) COUNT(DISTINCTC1)    MIN(C1)    MAX(C1)
---------- ----------------- ---------- ----------10000              1000          1       1000

1.首先我们指定变量值为3，执行下面的SQL文

SQL> var x number;
SQL> exec :x := 3 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a
...3 a30 rows selected.SQL>

2.因为绑定变量窥视(Bind Peeking)功能的影响，所以硬解析选择执行计划时，
会把绑定变量值3代入到SQL文中计算基数，SQL文在10000条数据中选择了30条数据，
所以，选择了索引IND1进行INDEX RANGE SCAN。

SQL> set line 200 
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  cuf4u4th4w0nz, child number 0
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :xPlan hash value: 3882350253---------------------------------------------------------
| Id  | Operation                   | Name     | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ACS |     30 |
|*  2 |   INDEX RANGE SCAN          | IND1     |     30 |
---------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("C1"<=:X)
...25 rows selected.

3.我们再指定变量值为9，再次执行下面的SQL文。
虽然这时候SQL文在10000条数据中选择了9000条数据，选择率高达90%,但是因为SQL文已经解析过了，所以会继续用之前的执行计划，E-Rows还是30，使用用INDEX RANGE SCAN.

SQL> exec :x := 9 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;    C1 C2
------ --------------------
     1 a
...900 a9000 rows selected.SQL> 
SQL> set line 200 
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
SQL_ID  cuf4u4th4w0nz, child number 0
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :xPlan hash value: 3882350253---------------------------------------------------------
| Id  | Operation                   | Name     | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ACS |     30 |
|*  2 |   INDEX RANGE SCAN          | IND1     |     30 |
---------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("C1"<=:X)
....25 rows selected.SQL> 
SQL> select child_number, executions, buffer_gets,plan_hash_value
from v$sql
where sql_id = 'cuf4u4th4w0nz';  2    3  CHILD_NUMBER EXECUTIONS BUFFER_GETS PLAN_HASH_VALUE
------------ ---------- ----------- ---------------0          2        1239      3882350253

4.我们再次指定变量值为9，执行下面的SQL文。
这时候SQL文还是在10000条数据中选择了9000条数据，但是因为SQL文已经解析过了，所以会继续用之前的执行计划，使用用INDEX RANGE SCAN

SQL> exec :x := 900 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a...900 a9000 rows selected.SQL>

5.无论以后指定变量值是什么，执行计划都会用最初做成的执行计划。
这样通过绑定变量窥视(Bind Peeking)功能做成的执行计划，针对某些变量值的执行可能不是最优的，甚至可能引起很严重的性能问题。

SQL> exec :x := 800 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;    C1 C2
------ --------------------
     1 a
...
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID  cuf4u4th4w0nz, child number 0
-------------------------------------
SELECT * FROM TEST_ACS WHERE c1 <= :xPlan hash value: 3882350253---------------------------------------------------------
| Id  | Operation                   | Name     | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT            |          |        |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ACS |     30 |
|*  2 |   INDEX RANGE SCAN          | IND1     |     30 |
---------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------   2 - access("C1"<=:X)...25 rows selected.

通过上面的例子，我们看到由于绑定变量窥视(Bind Peeking)的影响，对于变量值为9时，虽让本应该选择Table Full Scan才是最优的执行计划，还是使用了初次执行时根据变量值为3时做成的执行计划。
因此，如果最初用于估算的变量值不能代表大多数变量值时的特点时，对于生产系统，有时候可能是灾难性的。

例子2（ACS有效）

下面让我们看看，在11g后的版本上自适应游标共享(Adaptive Cursor Sharing 以后简称ACS)功能有效时，是如何改善这种这个问题。

ACS相关的动态视图

在以后执行过程中，我们可以通过观察相关的动态视图，来看看在这个过程中都发生了什么。
首先，我们介绍下在以后过程中用到的主要4个视图，下面是相关内容的简单介绍。

V$SQL视图在这个视图中主要有3个和ACS相关的列：  is_bind_sensitive:用于监视SQL游标是否是绑定敏感(bind sensitive)，是否可用ACS。is_bind_aware：用于监视SQL游标是否意识到需要根据绑定变量值来选择执行计划（bind aware）。is_shareable：用于监视SQL游标是否可以被共享。V$SQL_SHARED_CURSOR用于查看SQL游标没有共享的原因。V$SQL_CS_SELECTIVITY视图主要用来监视SQL游标的各个变量的选择率范围（SELECTIVITY CUBE）。它包含着各个条件谓词、绑定变量值以及它的最大值和最小值等信息。V$SQL_CS_HISTOGRAM根据所操作的行数，记录每个子游标执行的次数直方图。V$SQL_CS_STATISTICS    每个子游标执行的执行状况。（采样信息）

关于以上视图的定义和内容详细可以参考在线文档。

Oracle Database Online Documentation 11g Release 2 (11.2) / Database Reference

V$SQL
V$SQL_SHARED_CURSOR
V$SQL_CS_SELECTIVITY
V$SQL_CS_HISTOGRAM
V$SQL_CS_STATISTICS

下面开始我们的测试（基于11.2.0.4版本测试）：

准备测试用表和数据

0.准备测试用表和数据

    SQL> SQL> conn scott/tigerConnected.SQL> SQL> drop table TEST_ACS;   Table dropped.SQL> create table TEST_ACS(c1 number, c2 varchar2(100));    Table created.  SQL> SQL> BEGIN2      FOR i IN 1 .. 1000 LOOP3      FOR j IN 1 .. 10 LOOP4          INSERT INTO TEST_ACS5          VALUES (i,'a');6          COMMIT;7      END LOOP;8      END LOOP;9  END;10  /    PL/SQL procedure successfully completed.    SQL> SQL> create index ind1 on TEST_ACS(c1);    Index created.SQL> exec dbms_stats.gather_table_stats(ownname => 'SCOTT',tabname => 'TEST_ACS',CASCADE => TRUE);    PL/SQL procedure successfully completed.---我们准备了一个表包含10000条数据，c1列有1~1000的值，并且每个值都有10条数据。---并且在c1列有个索引ind1SQL> SELECT count(c1), count(DISTINCT c1), min(c1), max(c1) FROM TEST_ACS;COUNT(C1) COUNT(DISTINCTC1)MIN(C1)MAX(C1)---------- ----------------- ---------- ----------10000  1000  1   1000

Test1：指定变量值为3，首次执行

首先我们指定变量值为3，首次执行下面的SQL文

SQL> var x number;
SQL> exec :x := 3 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a 
...3 a 30 rows selected.SQL>

1.因为绑定变量窥视(Bind Peeking)功能的影响，所以硬解析选择执行计划时，会把绑定变量值3代入到SQL文中计算基数，SQL文在10000条数据中选择了30条数据，所以，选择了索引IND1进行INDEX RANGE SCAN。

SQL> set line 200
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  cuf4u4th4w0nz, child number 0 
------------------------------------- 
SELECT * FROM TEST_ACS WHERE c1 <= :x Plan hash value: 3882350253   ---------------------------------------------------------
| Id  | Operation   | Name | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT|  ||
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|*  2 |   INDEX RANGE SCAN  | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):  
---------------------------------------------------  2 - access("C1"<=:X)

当SQL文第一次被执行的时候，因为绑定变量窥视(Bind Peeking)功能的影响，所以硬解析选择执行计划时，会把绑定变量值3代入到SQL文中计算选择率。
因为SQL文在10000条数据中选择了30条数据，选择率为0.03，所以优化器选择了通过索引IND1进行访问数据（INDEX RANGE SCAN）。

SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2     is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3  from v$sql4  where sql_id = 'cuf4u4th4w0nz';SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE  
------------- ------------ ---------- ----------- - - - ---------------  
cuf4u4th4w0nz0  1   7 Y N Y  3882350253  ---1.V$SQL.is_bind_sensitive为Y，游标被标示成bind-sensitive，可以使用ACS。
---V$SQL.IS_SHAREABLE为Y，游标可以被共享
---V$SQL.is_bind_aware为N，没有意识到绑定变量不同会影响执行计划SQL> 
SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS2  from V$SQL_SHARED_CURSOR3  where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER B L
------------- ------------ - -
cuf4u4th4w0nz0 N N---1.游标共享信息V$SQL_SHARED_CURSOR.BIND_EQUIV_FAILURE为N
---V$SQL_SHARED_CURSOR.LOAD_OPTIMIZER_STATS为N （供以后做对比）SQL> 
SQL> select *2  from v$sql_cs_statistics3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz0  2706503459 Y  1 60   7  0---1.v$sql_cs_statistics采样信息中，记录了游标执行的执行状况，包括处理的行数，消耗的Buffer等信息SQL> 
SQL> ---no data in V$SQL_CS_SELECTIVITY for the first time.
SQL> select *2  from v$sql_cs_selectivity3  where sql_id = 'cuf4u4th4w0nz';no rows selected---1.V$SQL_CS_SELECTIVITY 中还没有任何信息。SQL> 
SQL> select *2  from V$SQL_CS_HISTOGRAM3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER  BUCKET_ID  COUNT 
---------------- ---------- ------------- ------------ ---------- ---------- 
000000008A976370 1615725215 cuf4u4th4w0nz0  0  1 ★
000000008A976370 1615725215 cuf4u4th4w0nz0  1  0 
000000008A976370 1615725215 cuf4u4th4w0nz0  2  0 ---1.V$SQL_CS_HISTOGRAM里根据变量值做成的执行次数直方图中，本次执行计算到BUCKET_ID：0中，1次。

根据上面的相关信息，我们可以知道，当包含绑定变量信息的SQL文第一次执行的时候，会发生下面的一些动作：

・SQL文进行硬解析（HARD Parse）
・根据最初绑定变量值为3，进行估算选择率（Bind Peeking）
・游标被标示成bind-sensitive
・选择出最优执行计划保存：
・记录了游标执行的执行状况，包括处理的行数，消耗的Buffer等信息
・记录了游标执行次数直方图

Test2：指定变量值为9，执行SQL文

下面我们传递一个变量9，再次执行SQL文。

SQL> --var x number;
SQL> exec :x := 900 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------1 a 1 a 
...900 a 900 a 9000 rows selected.---还是使用最初的执行计划索引扫描。SQL> set line 2000
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT   
--------------------------------------------------------------------------
SQL_ID  cuf4u4th4w0nz, child number 0  
-------------------------------------  
SELECT * FROM TEST_ACS WHERE c1 <= :x  Plan hash value: 3882350253---------------------------------------------------------
| Id  | Operation   | Name | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT|  ||
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|*  2 |   INDEX RANGE SCAN  | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------2 - access("C1"<=:X)

虽然SQL文在10000条数据中选择了9000条数据，选择率应该为0.9，
但是因为该SQL文已经硬解析过了，所以这次是软解析，使用以前的执行计划，通过索引IND1进行访问数据（INDEX RANGE SCAN）。

再次观察相关动态视图的变化：

SQL> 
SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2     is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3  from v$sql4  where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE
------------- ------------ ---------- ----------- - - - ---------------
cuf4u4th4w0nz0  21239 Y N Y  3882350253---2.V$SQL中的内容没有变化，和指定变量值为3时一样。SQL> 
SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS2  from V$SQL_SHARED_CURSOR3  where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER B L 
------------- ------------ - - 
cuf4u4th4w0nz0 N Y ----2.但是因为估算的选择率（0.03）和实际执行的选择率（0.9）差距很大，V$SQL_SHARED_CURSOR游标共享信息中LOAD_OPTIMIZER_STATS变为Y SQL> select *2  from v$sql_cs_statistics3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME   
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------   
000000008A976370 1615725215 cuf4u4th4w0nz0  2706503459 Y  1 60   7  0   ---2.v$sql_cs_statistics采样信息并没有变化。(因为是采样，所以不一定随时变化)SQL> 
SQL> select *2  from v$sql_cs_selectivity3  where sql_id = 'cuf4u4th4w0nz';no rows selected---2.V$SQL_CS_SELECTIVITY 中还是没有任何信息。SQL> select *2  from V$SQL_CS_HISTOGRAM3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER  BUCKET_ID  COUNT   
---------------- ---------- ------------- ------------ ---------- ----------   
000000008A976370 1615725215 cuf4u4th4w0nz0  0  1   
000000008A976370 1615725215 cuf4u4th4w0nz0  1  1   
000000008A976370 1615725215 cuf4u4th4w0nz0  2  0   ---2.V$SQL_CS_HISTOGRAM里根据变量值做成的执行次数直方图中，本次执行计算到BUCKET_ID：1中，1次。

根据上面的相关信息，我们可以知道，当SQL文第二次执行，
并且绑定变量值和之前的绑定变量值的选择率很大时，会发生下面的一些动作：

・SQL文进行软解析（Soft Parse）
・使用以前的执行计划执行
・比较统计信息 （估算的选择率：0.03和实际执行的选择率：0.9）
・如果差距很大时，V$SQL_SHARED_CURSOR游标共享信息中LOAD_OPTIMIZER_STATS变为Y。(激活ECS)保存：
・记录了游标执行的执行状况，包括处理的行数，消耗的Buffer等信息
・记录了游标执行次数直方图（根据行数选择BUCKET）
・SQL变为BIND Aware

Test3：指定变量值为9，再次执行SQL文

下面我们传递一个变量9，再次执行SQL文:

SQL> --var x number;
SQL> exec :x := 900 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2   
------ --------------------
67 a
...900 a9000 rows selected.SQL> ---通过全表扫描执行。SQL> set line 2000
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT   
--------------------------------------------------------------------------
SQL_ID  cuf4u4th4w0nz, child number 1  
-------------------------------------  
SELECT * FROM TEST_ACS WHERE c1 <= :x  Plan hash value: 3781643149-----------------------------------------------
| Id  | Operation | Name | E-Rows |
-----------------------------------------------
|   0 | SELECT STATEMENT  |  ||
|*  1 |  TABLE ACCESS FULL| TEST_ACS |   9009 |
-----------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------1 - filter("C1"<=:X)

由于上次执行时发现，由于变量值的影响，SQL文的选择率预估值和实际执行时的差别很大（Bind Aware），所以这次执行SQL文重新编译，执行计划发生了变化，通过全表扫描执行。

再次观察相关动态视图的变化：

SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2     is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3  from v$sql4  where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE
------------- ------------ ---------- ----------- - - - ---------------
cuf4u4th4w0nz0  21239 Y N N  3882350253
cuf4u4th4w0nz1  1 622 Y Y Y  3781643149---3.V$SQL中生成了一个新的游标，
并且新生成的游标1IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y.
原来那个游标0，IS_SHAREABLE变为N.SQL> select sql_id, child_number, BIND_EQUIV_FAILURE, LOAD_OPTIMIZER_STATS2  from V$SQL_SHARED_CURSOR3  where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER B L 
------------- ------------ - - 
cuf4u4th4w0nz0 N Y 
cuf4u4th4w0nz1 Y N ---3. V$SQL_SHARED_CURSOR中生成一条BIND_EQUIV_FAILURE为Y的游标。
（绑定变量值的选择率和存在的游标不符）SQL> select *2  from v$sql_cs_statistics3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME   
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------   
000000008A976370 1615725215 cuf4u4th4w0nz1  3116944019 Y  1   9000 622  0   
000000008A976370 1615725215 cuf4u4th4w0nz0  2706503459 Y  1 60   7  0   ★---3. v$sql_cs_statistics采样信息中追加了一条最新的采样SQL> select *2  from v$sql_cs_selectivity3  where sql_id = 'cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER PREDICATE  RANGE_ID LOWHIGH   
---------------- ---------- ------------- ------------ -------------------- ---------- ---------- ---------- 
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X   0 0.810810   0.990990   ---3. V$SQL_CS_SELECTIVITY 中记录了新追加的游标的选择率范围（依赖于绑定变量值）SQL> 
SQL> select *2  from V$SQL_CS_HISTOGRAM3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER  BUCKET_ID  COUNT   
---------------- ---------- ------------- ------------ ---------- ----------   
000000008A976370 1615725215 cuf4u4th4w0nz1  0  0   
000000008A976370 1615725215 cuf4u4th4w0nz1  1  1 ★  
000000008A976370 1615725215 cuf4u4th4w0nz1  2  0   
000000008A976370 1615725215 cuf4u4th4w0nz0  0  1   
000000008A976370 1615725215 cuf4u4th4w0nz0  1  1   
000000008A976370 1615725215 cuf4u4th4w0nz0  2  0   6 rows selected.---3. V$SQL_CS_HISTOGRAM里记录了新追加的游标的直方图，并记录执行次数到BUCKET_ID：1中，1次。

根据上面的相关信息，我们可以知道，当SQL文第三次执行时（和第二次相同的变量），会发生下面的一些动作：

・SQL文进行硬解析（Hard Parse）
・生成新的执行计划
・V$SQL_SHARED_CURSOR游标共享信息中BIND_EQUIV_FAILURE变为Y。
・V$SQL中生成IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y的新游标1.原来那个游标0，IS_SHAREABLE变为N保存：
・记录游标执行的执行状况，包括处理的行数，消耗的Buffer等信息
・记录游标执行次数直方图（根据行数选择BUCKET）
・SQL变为BIND Aware
・记录游标的选择率范围（selectivity cube）

Test4：指定变量值为3，再次执行SQL文　（SELECTIVITY CUBE）

指定变量值为3 (和最初执行时一样)，再次执行SQL文

SQL> --var x number;
SQL> exec :x := 3 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2   
------ --------------------1 a
...3 a30 rows selected.---SQL文重新解析，通过索引扫描执行SQL> set line 200
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  cuf4u4th4w0nz, child number 2 
------------------------------------- 
SELECT * FROM TEST_ACS WHERE c1 <= :x Plan hash value: 3882350253   ---------------------------------------------------------
| Id  | Operation   | Name | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT|  ||
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ACS | 30 |
|*  2 |   INDEX RANGE SCAN  | IND1 | 30 |
---------------------------------------------------------Predicate Information (identified by operation id):  
---------------------------------------------------  2 - access("C1"<=:X)

这次执行计划发生了变化，SQL文重新解析，通过索引扫描执行。
再次观察相关动态视图的变化：

SQL> ---sql_id:cuf4u4th4w0nz
SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2     is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3  from v$sql4  where sql_id = 'cuf4u4th4w0nz';SQL_ID　CHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE  
------------- ------------ ---------- ----------- - - - ---------------  
cuf4u4th4w0nz0  21239 Y N N  3882350253  
cuf4u4th4w0nz1  1 622 Y Y Y  3781643149  
cuf4u4th4w0nz2  1   7 Y Y Y  3882350253  ★---4.V$SQL中生成了一个新游标CHILD# 2，
并且新生成的游标IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y.SQL> select *2  from v$sql_cs_statistics3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz2  2706503459 Y  1 60   7  0★
000000008A976370 1615725215 cuf4u4th4w0nz1  3116944019 Y  1   9000 622  0
000000008A976370 1615725215 cuf4u4th4w0nz0  2706503459 Y  1 60   7  0---4. v$sql_cs_statistics采样信息中追加了一条最新的采样SQL> 
SQL> ---select CHILD_NUMBER,PREDICATE,RANGE_ID,LOW,HIGH
SQL> select *2  from v$sql_cs_selectivity3  where sql_id = 'cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_ID　CHILD_NUMBER PREDICATE  RANGE_ID LOWHIGH   
---------------- ---------- ------------- ------------ -------------------- ---------- ---------- ---------- 
000000008A976370 1615725215 cuf4u4th4w0nz2 <=X   0 0.002702   0.003302   ★
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X   0 0.810810   0.990990   ---4. V$SQL_CS_SELECTIVITY 中记录了新追加游标CHILD# 2的选择率范围（依赖于绑定变量值）SQL> select *2  from V$SQL_CS_HISTOGRAM3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER  BUCKET_ID  COUNT 
---------------- ---------- ------------- ------------ ---------- ---------- 
000000008A976370 1615725215 cuf4u4th4w0nz2  0  1 ★
000000008A976370 1615725215 cuf4u4th4w0nz2  1  0 
000000008A976370 1615725215 cuf4u4th4w0nz2  2  0 
000000008A976370 1615725215 cuf4u4th4w0nz1  0  0 
000000008A976370 1615725215 cuf4u4th4w0nz1  1  1 
000000008A976370 1615725215 cuf4u4th4w0nz1  2  0 
000000008A976370 1615725215 cuf4u4th4w0nz0  0  1 
000000008A976370 1615725215 cuf4u4th4w0nz0  1  1 
000000008A976370 1615725215 cuf4u4th4w0nz0  2  0 9 rows selected.---4. V$SQL_CS_HISTOGRAM里记录了CHILD# 2的直方图，并记录执行次数到BUCKET_ID：0中，1次。

根据上面的相关信息，我们可以知道，当SQL文第四次执行时（和第一次相同的变量），
由于第一次生成的游标变成IS_SHAREABLE为N,会发生下面的一些动作：

・SQL文进行硬解析（Hard Parse）
・生成新的执行计划 
・V$SQL中生成IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y的新游标.保存：
・记录游标执行的执行状况，包括处理的行数，消耗的Buffer等信息
・记录游标执行次数直方图（根据行数选择BUCKET）
・记录游标的选择率范围（selectivity cube）

Test5：指定变量值为5，再次执行SQL文 (Merge)

指定变量值为5，再次执行SQL文.
我们知道对于本条SQL文变量值为5时符合条件的选择率是50/10000=0.005，并不在以前执行过游标的选择率范围内（变量值为3的选择率范围为0.002702～0.003302；变量值为9的选择率范围为0.810810～0.990990)，
所以SQL文依然是进行了硬解析。

SQL> --var x number;
SQL> exec :x := 5 ;PL/SQL procedure successfully completed.SQL> SELECT * FROM TEST_ACS WHERE c1 <= :x;C1 C2
------ --------------------   1 a 
...5 a 5 a 50 rows selected.
---SQL文选择了最优的执行计划，通过索引扫描。
SQL> set line 200
SQL> set pagesize 9999
SQL> select * from table(dbms_xplan.display_cursor(null,null,'NOTE ALLSTATS LAST'));PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  cuf4u4th4w0nz, child number 3 
------------------------------------- 
SELECT * FROM TEST_ACS WHERE c1 <= :x Plan hash value: 3882350253   ---------------------------------------------------------
| Id  | Operation   | Name | E-Rows |
---------------------------------------------------------
|   0 | SELECT STATEMENT|  ||
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_ACS | 50 |
|*  2 |   INDEX RANGE SCAN  | IND1 | 50 |
---------------------------------------------------------Predicate Information (identified by operation id):  
---------------------------------------------------  2 - access("C1"<=:X)

SQL文重新解析，通过索引扫描执行。再次观察相关动态视图的变化：

SQL> col PREDICATE format a20
SQL> ---sql_id:cuf4u4th4w0nz
SQL> select sql_id,child_number, executions, buffer_gets,2     is_bind_sensitive BS, is_bind_aware BA,IS_SHAREABLE SH,plan_hash_value3  from v$sql4  where sql_id = 'cuf4u4th4w0nz';SQL_IDCHILD_NUMBER EXECUTIONS BUFFER_GETS B B S PLAN_HASH_VALUE  
------------- ------------ ---------- ----------- - - - ---------------  
cuf4u4th4w0nz0  21239 Y N N  3882350253  
cuf4u4th4w0nz1  1 622 Y Y Y  3781643149  
cuf4u4th4w0nz2  1   7 Y Y N  3882350253  
cuf4u4th4w0nz3  1  11 Y Y Y  3882350253  ★---5.V$SQL中生成了一个新游标CHILD#3，CHILD#3游标的IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y；而且我们注意到和新生成CHILD#3拥有相同执行计划的CHILD#2的IS_SHAREABLE变成了N,也就所说CHILD#2不再被共享进入了age out队列。SQL> select *2  from v$sql_cs_statistics3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER BIND_SET_HASH_VALUE P EXECUTIONS ROWS_PROCESSED BUFFER_GETS   CPU_TIME
---------------- ---------- ------------- ------------ ------------------- - ---------- -------------- ----------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz3   365452098 Y  1100  11  0★
000000008A976370 1615725215 cuf4u4th4w0nz2  2706503459 Y  1 60   7  0
000000008A976370 1615725215 cuf4u4th4w0nz1  3116944019 Y  1   9000 622  0
000000008A976370 1615725215 cuf4u4th4w0nz0  2706503459 Y  1 60   7  0---5. v$sql_cs_statistics采样信息中追加了一条最新的采样SQL> select *2  from v$sql_cs_selectivity3  where sql_id = 'cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_IDCHILD_NUMBER PREDICATE  RANGE_ID LOW HIGH
---------------- ---------- ------------- ------------ -------------------- ---------- ---------- ----------
000000008A976370 1615725215 cuf4u4th4w0nz3 <=X   0 0.002702   0.005504★
000000008A976370 1615725215 cuf4u4th4w0nz2 <=X   0 0.002702   0.003302
000000008A976370 1615725215 cuf4u4th4w0nz1 <=X   0 0.810810   0.990990---5. V$SQL_CS_SELECTIVITY 中记录了新追加游标CHILD# 3的选择率范围（依赖于绑定变量值5）而且CHILD#3的选择率范围（0.002702～0.005504）把CHILD#2的选择率范围也包含了在内（0.002702～0.003302）。SQL> select *2  from V$SQL_CS_HISTOGRAM3  where sql_id='cuf4u4th4w0nz';ADDRESS  HASH_VALUE SQL_ID CHILD_NUMBER  BUCKET_ID  COUNT 
---------------- ---------- ------------- ------------ ---------- ---------- 
000000008A976370 1615725215 cuf4u4th4w0nz 3  0  1 ★
000000008A976370 1615725215 cuf4u4th4w0nz 3  1  0 ★
000000008A976370 1615725215 cuf4u4th4w0nz 3  2  0 ★
000000008A976370 1615725215 cuf4u4th4w0nz 2  0  1 
000000008A976370 1615725215 cuf4u4th4w0nz 2  1  0 
000000008A976370 1615725215 cuf4u4th4w0nz 2  2  0 
000000008A976370 1615725215 cuf4u4th4w0nz 1  0  0 
000000008A976370 1615725215 cuf4u4th4w0nz 1  1  1 
000000008A976370 1615725215 cuf4u4th4w0nz 1  2  0 
000000008A976370 1615725215 cuf4u4th4w0nz 0  0  1 
000000008A976370 1615725215 cuf4u4th4w0nz 0  1  1 
000000008A976370 1615725215 cuf4u4th4w0nz 0  2  0 12 rows selected.---5. V$SQL_CS_HISTOGRAM里记录了CHILD# 3的直方图，并记录执行次数到BUCKET_ID：0中，1次。

根据上面的相关信息，我们可以知道，当SQL文第五次执行时（不在之前的变量值的选择率范围内），会发生下面的一些动作：

・SQL文进行硬解析（Hard Parse）
・生成新的执行计划 
・V$SQL中生成IS_BIND_SENSITIVE, IS_BIND_AWARE, IS_SHAREABLE都是Y的新游标.
・把之前有相同执行计划的游标IS_SHAREABLE标记为N，成为age out的对象。
・合并具有相同计划的游标（Merge）保存：
・记录游标执行的执行状况，包括处理的行数，消耗的Buffer等信息
・记录游标执行次数直方图（根据行数选择BUCKET）
・记录合并后游标的选择率范围（selectivity cube）

ACS通过这种游标合并的操作，可以尽量减少子游标的数量，避免生成过多的子游标，减少内存等资源的浪费。

补充

我们也看到，在V$SQL_CS_HISTOGRAM视图中，每个子游标都有3行直方图数据，分别为Bucket 0、Bucket 1和Bucket 2，针对SQL文的每次执行，Oracle都会在相应的Bucket 行中记录子游标的执行次数。

子游标的执行次数到底记录在哪个Bucket 呢？
事实上，Oracle是根据实际执行所操作的总行数（Row Source Processing）来生成直方图的。

一般条数较多时，记录的方式如下：

Bucket 0: <1024(1K)行 ，
Bucket 1：大于或等于1024行小于1024*1024 (1M)行，
Bucket 2：大于或等于1024*1024 (1M)行。

而这个直方图最大的作用在于，比较SQL文的Child#0的Bucket 的高度（COUNT），如果其中Bucket的高度满足一定条件时，就会启用ACS功能(Bind Aware)。
※注意：
这仅仅是判断逻辑中的最简单的一种方式，还有其他更为复杂的判断逻辑，在这里暂时不做介绍。

ACS 的处理流程

下面引用一下Oracle ACE Mohamed Houri做的一个ACS流程图，来总体的回顾一下ACS的处理过程。

这里写图片描述

总结

通过上面的例子，我们基本了解了ACS的基本处理流程和ACS的好处：
解决由于绑定变量窥视功能导致的性能问题。

参考

Oracle Database Online Documentation 12c Release 1 (12.1) / Database SQL Tuning Guide
>Adaptive Cursor Sharing

这篇关于【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能

概述

例子1（ACS无效）

例子2（ACS有效）

ACS相关的动态视图

准备测试用表和数据

Test1：指定变量值为3，首次执行

Test2：指定变量值为9，执行SQL文

Test3：指定变量值为9，再次执行SQL文

Test4：指定变量值为3，再次执行SQL文　（SELECTIVITY CUBE）

Test5：指定变量值为5，再次执行SQL文 (Merge)

补充

ACS 的处理流程

总结

参考

相关文章

oracle DBMS_SQL.PARSE的使用方法和示例

java父子线程之间实现共享传递数据

Deepseek使用指南与提问优化策略方式

PLsql Oracle 下载安装图文过程详解

Tomcat高效部署与性能优化方式

SpringBoot整合DeepSeek实现AI对话功能

Python实现多路视频多窗口播放功能

css实现图片旋转功能

oracle如何连接登陆SYS账号

Oracle数据库如何切换登录用户(system和sys)

【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能

概述

例子1（ACS无效）

例子2（ACS有效）

ACS相关的动态视图

准备测试用表和数据

Test1：指定变量值为3，首次执行

Test2：指定变量值为9，执行SQL文

Test3：指定变量值为9，再次执行SQL文

Test4：指定变量值为3，再次执行SQL文 （SELECTIVITY CUBE）

Test5：指定变量值为5，再次执行SQL文 (Merge)

补充

ACS 的处理流程

总结

参考

相关文章

Test4：指定变量值为3，再次执行SQL文　（SELECTIVITY CUBE）