为什么Oracle中包含绑定变量的SQL会有多个游标？（译文）

oracleace 2023-02-05

691

原文网址：https://blogs.oracle.com/optimizer/post/why-are-there-many-cursors-for-queries-containing-bind-variables

摘要：为同一个SQL的不同绑定变量值生成不同的执行计划是Oracle数据库的独门绝技，这个特性让Oracle的优化器变得更加智能。这篇Oracle原厂大师的文章深入浅出地阐述了这个特性等适用场景和效果，值的Oracle DBA仔细阅读。

作者：Maria Colgan，Oracle公司的Distinguished Product Manager，自1996年7.3版发布以来一直在Oracle公司工作。Maria的核心职责是编写关于Oracle数据库的资料和课件，以及这些资料在客户环境中的最佳实践。她还负责将客户和合作伙伴的反馈纳入产品的未来版本中。在此角色之前，她是Oracle数据库内存和Oracle查询优化器的产品经理。

译者，姚远：

Oracle ACE（Oracle和MySQL数据库方向）
华为云MVP
《MySQL 8.0运维与优化》的作者
中国唯一一位Oracle高可用大师
拥有包括 Oracle 10g和12c OCM在内的20+数据库相关认证。
曾任IBM公司数据库部门经理
现在一家第三方公司任首席数据库专家，服务2万+客户。

为了改进包含绑定变量的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                           NUMBER
PHONE                           VARCHAR2(20)
DEPTNO                          NUMBER

DEPTNO列上的数据分布扭曲，因此当我在EMP表上收集统计信息时，Oracle会自动在DEPTNO列上创建直方图。

SQL> desc emp


Name                   Null?    Type
---------------------- -------- ----------------------------------
ENAME                           VARCHAR2(20)
EMPNO                           NUMBER
PHONE                           VARCHAR2(20)
DEPTNO                          NUMBER

现在我们在EMP表上执行一个简单的select语句，这个语句的WHERE子句谓词会查询DEPTNO列。谓词中包含一个绑定变量。我们首先将这个绑定变量的值设置为9，9这个值在表中出现了10次，即0.0001%的行。

SQL> exec :deptno := 9
SQL> 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 := 10
SQL> 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 := 10
SQL> 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 = :deptno
Plan 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 := 9
SQL> select /*ACS_1*/ count(*), max(empno)
from emp
where 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 = :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_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。

问：搜索合适的子游标会有性能的影响吗，比如长时间拿着库缓存栓。

答：匹配游标的任何其他开销总会引发大家对性能的担心，我们努力将影响降至最低。当然，代码路径会增加一些，以匹配绑定感知游标，因为它需要更智能的检查。但这个功能不应影响尚未标记为绑定感知的游标。

问：是什么触发游标被标记为“绑定感知”？

答：我们的目标是考虑许多类型的谓词，当绑定值发生变化时，选择性可能会发生变化。

问：听起来优化器正在根据返回的行数来决定是否生成一个新计划......

答：我不会说明如何决定标记游标绑定感知的细节，处理的行数只是其中一个输入。

下面是我写的书，欢迎大家购买！

欢迎加我的微信，拉你进高手如云的微信群👇