为什么Oracle中包含绑定变量的SQL会有多个游标?(译文)

简介: 为了改进包含绑定变量的SQL的执行计划,Oracle在11g版本中引入了一项名为自适应游标共享(ACS,Adaptive Cursor Sharing)的新功能。

为了改进包含绑定变量的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。

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

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

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

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

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

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


下面是我写的书,欢迎大家购买!

相关文章
|
3天前
|
存储 SQL 数据库
数据库sql语句-----游标和存储过程
数据库sql语句-----游标和存储过程
14 1
|
3天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标自定义异常:数据探险家的“专属警示灯”
【4月更文挑战第19天】Oracle PL/SQL中的游标自定义异常是处理数据异常的有效工具,犹如数据探险家的警示灯。通过声明异常名(如`LOW_SALARY_EXCEPTION`)并在满足特定条件(如薪资低于阈值)时使用`RAISE`抛出异常,能灵活应对复杂业务规则。示例代码展示了如何在游标操作中定义和捕获自定义异常,提升代码可读性和维护性,确保在面对数据挑战时能及时响应。掌握自定义异常,让数据管理更从容。
|
3天前
|
SQL Oracle 安全
Oracle的PL/SQL游标异常处理:从“惊涛骇浪”到“风平浪静”
【4月更文挑战第19天】Oracle PL/SQL游标异常处理确保了在数据操作中遇到的问题得以优雅解决,如`NO_DATA_FOUND`或`TOO_MANY_ROWS`等异常。通过使用`EXCEPTION`块捕获并处理这些异常,开发者可以防止程序因游标问题而崩溃。例如,当查询无结果时,可以显示定制的错误信息而不是让程序终止。掌握游标异常处理是成为娴熟的Oracle数据管理员的关键,能保证在复杂的数据环境中稳健运行。
|
3天前
|
SQL Oracle 安全
Oracle的PL/SQL异常处理方法:守护数据之旅的“魔法盾”
【4月更文挑战第19天】Oracle PL/SQL的异常处理机制是保障数据安全的关键。通过预定义异常(如`NO_DATA_FOUND`)和自定义异常,开发者能优雅地管理错误。异常在子程序中抛出后会向上传播,直到被捕获,提供了一种集中处理错误的方式。理解和善用异常处理,如同手持“魔法盾”,确保程序在面对如除数为零、违反约束等挑战时,能有效保护数据的完整性和程序的稳定性。
|
3天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL中FOR语句循环游标的奇幻之旅
【4月更文挑战第19天】在Oracle PL/SQL中,FOR语句与游标结合,提供了一种简化数据遍历的高效方法。传统游标处理涉及多个步骤,而FOR循环游标自动处理细节,使代码更简洁、易读。通过示例展示了如何使用FOR循环游标遍历员工表并打印姓名和薪资,对比传统方式,FOR语句不仅简化代码,还因内部优化提升了执行效率。推荐开发者利用这一功能提高工作效率。
|
3天前
|
SQL Oracle 关系型数据库
Oracle的PL/SQL游标属性:数据的“导航仪”与“仪表盘”
【4月更文挑战第19天】Oracle PL/SQL游标属性如同车辆的导航仪和仪表盘,提供丰富信息和控制。 `%FOUND`和`%NOTFOUND`指示数据读取状态,`%ROWCOUNT`记录处理行数,`%ISOPEN`显示游标状态。还有`%BULK_ROWCOUNT`和`%BULK_EXCEPTIONS`增强处理灵活性。通过实例展示了如何在数据处理中利用这些属性监控和控制流程,提高效率和准确性。掌握游标属性是提升数据处理能力的关键。
|
SQL Oracle 关系型数据库
Oracle基础 游标
原文:Oracle基础 游标 一、游标     游标用来处理从数据库中检索的多行记录(使用SELECT语句)。利用游标,程序可以逐个地处理和遍历一次检索返回的整个记录集。      为了处理SQL语句,Oracle将在内存中分配一个区域,这就是上下文区。
1186 0
|
3天前
|
SQL Oracle 安全
Oracle11g更改数据库名(详细教程)
Oracle11g更改数据库名(详细教程)
13 1
|
3天前
|
DataWorks Oracle 关系型数据库
DataWorks操作报错合集之尝试从Oracle数据库同步数据到TDSQL的PG版本,并遇到了与RAW字段相关的语法错误,该怎么处理
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
33 0
|
18小时前
|
Oracle 关系型数据库 MySQL
实时计算 Flink版操作报错合集之采集oracle的时候报ORA-65040:不允许从可插入数据库内部执行该操作如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
12 3

推荐镜像

更多