为了改进包含绑定变量的SQL的执行计划，Oracle在11g版本中引入了一项名为自适应游标共享（ACS，Adaptive Cursor Sharing）的新功能。此功能可以为包含绑定变量的SQL生成多个游标，我们将在本文中解释原因。在我们讨论细节之前，让我们先回顾一下历史。Oracle在 9i版本中引入了绑定偷窥（Bind Peeking）功能。通过绑定偷窥，优化器在第一次调用游标时会查看用户定义的绑定变量的值。这允许优化器确定WHERE子句条件的选择性，就好像使用了字面量而不是绑定变量一样，从而提高了为包含绑定变量的SQL生成的执行计划的质量。然而，当WHERE子句中使用的列中的数据分布扭曲时，这种方法存在问题。如果该列中的数据分布扭曲，则在统计信息收集期间，该列上可能创建了直方图。当优化器查看用户定义的绑定变量的值并选择执行计划时，不能保证这个计划对绑定变量的所有可能值都是合适的。换句话说，该计划是针对绑定变量的偷窥值进行了优化，而不是针对所有可能的值进行了优化。在Oracle 11g中，对于使用绑定变量的单个语句，优化器可以生成多个不同执行计划。这确保了根据绑定值使用最佳执行计划。让我们根据一个例子看看它是如何工作的。假设我有简单的表EMP，它有10万行，在DEPTNO列上有一个名为EMP_I1的索引。
SQL> desc emp Name Null? Type---------------------- -------- ----------------------------------ENAME VARCHAR2(20)EMPNO NUMBERPHONE VARCHAR2(20)DEPTNO NUMBER
DEPTNO列上的数据分布扭曲，因此当我在EMP表上收集统计信息时，Oracle会自动在DEPTNO列上创建直方图。
SQL> desc emp Name Null? Type---------------------- -------- ----------------------------------ENAME VARCHAR2(20)EMPNO NUMBERPHONE VARCHAR2(20)DEPTNO NUMBER
现在我们在EMP表上执行一个简单的select语句，这个语句的WHERE子句谓词会查询DEPTNO列。谓词中包含一个绑定变量。我们首先将这个绑定变量的值设置为9，9这个值在表中出现了10次，即0.0001%的行。
SQL> exec :deptno := 9SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO)---------- ---------- 10 99
鉴于9这个值的选择性，我们预计这个查询会进行索引范围扫描。让我们检查一下执行计划。
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT-------------------------------------------------------------------------SQL_ID 272gr4hapc9w1, child number 0------------------------------------------------------------------------select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 3184478295------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |Cost (%CPU)|------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)|| 1 | SORT AGGREGATE | | 1| 16 | || 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|------------------------------------------------------------------------
果然，我们看到了预期的索引范围扫描。现在让我们看看这个语句的执行统计信息。
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE ------------ ---------- ----------- ----------------- ------------- 0 1 53 Y N
这里可以看到，这个SQL有一个子游标，已经执行过一次，并且BUFFER_GETS的值很少，只有53。我们还看到游标已被标记为绑定敏感（IS_BIND_SENSITIVE）。如果优化器认为最优计划可能取决于绑定变量的值，则游标会被标记为绑定敏感。当游标被标记为绑定敏感时，Oracle会使用不同的绑定值监控游标的行为，以确定是否需要针对不同的绑定值生成不同的执行计划。此游标被标记为绑定敏感，因为DEPTNO列上的直方图用于计算谓词“where deptno = :deptno”的选择性。直方图的存在表明这个列的数据分布是扭曲的，因此绑定变量的不同值可能会要求不同的执行计划。现在，让我们将绑定变量的值更改为10，这是DEPTNO列上出现最多的值，它出现了99900次，即99.9%的行。
SQL> exec :deptno := 10SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ---------- 99900 100000
我们预计这次执行计划与以前相同，因为Oracle最初认为这个游标可以共享。让我们检查一下：
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT ------------------------------------------------------------------------SQL_ID 272gr4hapc9w1, child number 0 ------------------------------------------------------------------------ select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno Plan hash value: 3184478295 ------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)|| 1 | SORT AGGREGATE | | 1| 16 | | | 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)| ------------------------------------------------------------------------
该计划和以前一样仍然是索引范围扫描，但如果我们查看执行统计信息，我们应该会看到有两次执行，BUFFER_GET的数量从之前的53大幅跃升到1007。
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE------------ ---------- ----------- ----------------- -------------0 2 1007 Y N
我们还应该注意到，游标仍然只标记为绑定敏感（IS_BIND_SENSITIVE），此时还是非绑定感知（IS_BIND_AWARE）。因此，让我们使用相同的值10重新执行该语句。
SQL> exec :deptno := 10SQL> select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptno; COUNT(*) MAX(EMPNO)---------- ----------- 99900 100000
Oracle一直在幕后监控这两次SQL执行的统计信息，发现不同的绑定值导致执行时查询的数据量显著不同。基于这种差异，Oracle会自动调整其行为，因此不总是为这个SQL共享相同的计划。因此，根据当前的绑定值10会生成一个新计划。让我们看看新计划：
SQL> select * from table(dbms_xplan.display_cursor); PLAN_TABLE_OUTPUT --------------------------------------------------------------------SQL_ID 272gr4hapc9w1, child number 1--------------------------------------------------------------------select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptnoPlan hash value: 2083865914--------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| -------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 240 (100)|| 1 | SORT AGGREGATE | | 1 | 16 | | |* 2 | TABLE ACCESS FULL | EMP | 95000 | 1484K | 240 (1)| --------------------------------------------------------------------
鉴于表中的值10的选择性很差，新计划采用的是全表扫描。现在，如果我们显示执行统计信息，我们看到已经创建了一个新的子游标（#1）。游标#1显示BUFFER_GET低于游标#0，并标记为绑定敏感（IS_BIND_SENSITIVE）和绑定感知（IS_BIND_AWARE）。绑定感知游标可能会对不同的绑定值使用不同的执行计划，具体取决于包含绑定变量的谓词的选择度。查看执行统计信息：
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%'; CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_BIND_SENSITIVE IS_BIND_AWARE------------ ---------- ----------- ----------------- ------------- 0 2 1007 Y N 1 1 821 Y Y
我们看到一个新的游标，它代表使用全表扫描的计划。但是，如果我们再次使用选择性高的绑定值执行SQL，执行计划应该访问索引：
SQL> exec :deptno := 9SQL> select /*ACS_1*/ count(*), max(empno)from empwhere deptno = :deptno; COUNT(*) MAX(EMPNO) ---------- ---------- 10 99 SQL> select * from table(dbms_xplan.display_cursor);PLAN_TABLE_OUTPUT------------------------------------------------------------------------SQL_ID 272gr4hapc9w1, child number 2------------------------------------------------------------------------select /*ACS_1*/ count(*), max(empno) from emp where deptno = :deptnoPlan hash value: 3184478295 ------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |Cost (%CPU)| ------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | | 2 (100)| | 1 | SORT AGGREGATE | | 1| 16 | || 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1| 16 | 2 (0)|| 3 | INDEX RANGE SCAN | EMP_I1| 1| | 1 (0)|------------------------------------------------------------------------
优化器根据当前绑定值的选择性，选择了适当的计划。关于这一点，还有最后一件有趣的事情需要注意。如果我们再看一遍执行统计信息，现在有三个游标：
SQL> select child_number, executions, buffer_gets, is_bind_sensitive, is_bind_aware from v$sql where sql_text like 'select /*ACS_1%';
CHILD_NUMBER EXECUTIONS BUFFER_GETS IS_B_SENS IS_B_AWAR IS_SHAR------------ ---------- ----------- --------- --------- ---------- 0 2 957 Y N N 1 1 765 Y Y Y 2 2 6 Y Y Y
当游标切换到绑定感知模式时，原来的游标被丢弃，它被标记为不可共享（is_shareable是“N”），这意味着该游标将会从游标缓存中淘汰，它将不再被使用。换句话说，它只是在等待垃圾收集。还有一个原因会造成在11g中产生额外游标，当使用新的绑定值时，优化器会根据绑定值选择度的相似性，试图找到它认为合适的游标。如果它找不到这样的游标，它将创建一个新的游标（就像前面的例子中，当为选择性低的“10”创建一个（#1），为选择性高的“9”创建另外一个（#2））。如果新游标的计划与现有游标之一相同，则两个游标将被合并，以节省游标缓存中的空间。这将导致一个处于不可共享状态的游标被抛弃，如果游标缓存空间紧张，此游标将会首先从缓存中删除，并且不会用于未来的执行。
我将在这里汇总回答大家的问题，而不是逐一回答大家评论中的问题。
问：这种行为是否由11g优化器自动管理，我们不再需要cursor_sharing了？
答：出于向后兼容性的目的，我们尚未更改cursor_sharing参数的行为。因此，如果您将这个参数设置为 similar，自适应游标共享只会在字面量被替换为绑定变量的SQL中作用。我们希望将来来，此功能将说服人们将cursor_sharing设置为force。
问：搜索合适的子游标会有性能的影响吗，比如长时间拿着库缓存栓。
答：匹配游标的任何其他开销总会引发大家对性能的担心，我们努力将影响降至最低。当然，代码路径会增加一些，以匹配绑定感知游标，因为它需要更智能的检查。但这个功能不应影响尚未标记为绑定感知的游标。
问：是什么触发游标被标记为“绑定感知”？
答：我们的目标是考虑许多类型的谓词，当绑定值发生变化时，选择性可能会发生变化。
问：听起来优化器正在根据返回的行数来决定是否生成一个新计划......
答：我不会说明如何决定标记游标绑定感知的细节，处理的行数只是其中一个输入。
下面是我写的书，欢迎大家购买！
