ORACLE11g特性--基数反馈

原创 Jenny 2021-10-22

2726

本文主要是讨论什么时候会启用基数反馈特性。

首先介绍一下基数反馈理论知识，然后举例探讨oracle何时触发基数反馈。

一、基数反馈的理论知识

Cardinality Feedback基数反馈是版本11.2中引入的关于SQL 性能优化的新特性，Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估，造成CBO选择不当的执行计划，引入基数反馈这一特性，是为了帮助Oracle优化器依据更精准的基数生成更加优秀的执行计划

（一）基数反馈处理过程：

Oracle在SQL第一次执行时，会监控实际的基数（A-Rows）和评估的基数（E-Rows）之间的差异，如果差异较大，那么就记录实际基数（A-Rows），做上标记。在第二次执行时，优化器会依据实际的基数重新决策生成执行计划。如果两个值相差不大，那么CBO就不再监控这条SQL语句。当然重新生成的执行计划也有可能与原来的执行计划一样。

（二）基数反馈开启与关闭：

基数反馈的开启和关闭通过一个隐含参数“_OPTIMIZER_USE_FEEDBACK”来控制，该参数默认为TRUE，表示开启基数反馈特性。此参数除了可以在SESSION和SYSTEM级别进行设置之外，还可以在SQL语句级使用Hint进行开启和关闭，如下所示：

SELECT /*+ OPT_PARAM(’_OPTIMIZER_USE_FEEDBACK’ ‘FALSE’) */ COUNT(*) FROM TEST;
SELECT /*+ OPT_PARAM(’_OPTIMIZER_USE_FEEDBACK’ ‘TRUE’) */ COUNT(*) FROM TEST;

（三）使用基数反馈的标识

Oracle 11gR2针对此特性，专门在V$SQL_SHARED_CURSOR中增加了USE_FEEDBACK_STATS列来记录SQL是否使用了基数反馈。若使用了该特性则在执行计划的Note部分可以看到“cardinality feedback used for this statement”字样。

（四）最佳实践

由于CFB的评估结果数据只存在内存中（重启之后就会丢失），并且由于在Oracle 11g中存在过多的Bug，常见的问题就是在第二次执行SQL时候性能下降很多。因此在Oracle 11g的数据库中往往会对11.2.0.4以下的数据库会将该特性关闭。

二、针对哪些情况启用基数反馈

Oracle只针对下面情况开启CFB：

①　没有收集表的统计信息，并且动态采样（Dynamic Sampling）也没有开启。

②　查询条件复杂（比如条件有函数）或者涉及多列，但却没有收集扩展的统计信息（Extended Statistics）。

在这几种情况下，CBO是无法估算出准确的Cardinality的。

（一）验证绑定变量与基数反馈使用的关系

cursor_sharing=force或者对目标表过滤条件上使用绑定变量也不会使用基数反馈，应该是因为无法保证换一个绑定变量值基数不变，所以记录基数没有意义。

下面举例说明基数反馈与使用绑定变量的关系：以下实验是在oracle11.2.0.3的版本做的。

1. 目标表过滤不使用绑定变量。

目标sql上涉及多列过滤，没有收集扩展统计信息

第一次执行：

SQL> select object_id,object_type from t1 where object_id=13364 and data_object_id=90007 and created_time between to_date('2021-8-11','yyyy-mm-dd') and to_date('2021-8-12','yyyy-mm-dd');

OBJECT_ID OBJECT_TYPE
---------- -------------------
13364 PACKAGE

第二次执行

SQL> select object_id,object_type from t1 where object_id=13364 and data_object_id=90007 and created_time between to_date(‘2021-8-11’,‘yyyy-mm-dd’) and to_date(‘2021-8-12’,‘yyyy-mm-dd’);
OBJECT_ID OBJECT_TYPE
---------- -------------------
13364 PACKAGE

实验表明：第一次执行时E-rows与A-rows相差悬殊，在第二次执行时就用到基数反馈，执行计划发生变更，选择使用另一个索引。

2.sql语句中对目标表的过滤条件中加入绑定变量
目标表的访问过滤条件里使用了绑定变量，无论执行多少次都不会启用基数反馈。

SQL> var status varchar2(30);
SQL> exec :status:='VALID';
PL/SQL procedure successfully completed.

第一次执行（上一个语句里又加了一个过滤字段条件）

SQL> select object_id,object_type from t1 where object_id=13364 and data_object_id=90007 and created_time between to_date('2021-8-11','yyyy-mm-dd') and to_date('2021-8-12','yyyy-mm-dd') and status=:status;

OBJECT_ID OBJECT_TYPE
---------- -------------------
13364 PACKAGE

第二次执行

实验表明：当目标表里使用了绑定变量，第二次执行的执行计划与第一次一样，没有提示启用基数反馈。

3.多表关联，目标表全部用常量过滤，其他关联表使用绑定变量。

第一次执行

SQL> select t1.object_id,t1.object_type
  2 from t2
  3 inner join t1 on t2.object_id=t1.object_id
  4 where t1.object_id=13364 and t1.data_object_id=90007 and t1.created_time between to_date('2021-8-11','yyyy-mm-dd') and to_date('2021-8-12','yyyy-mm-dd') and t2.status=:status;

OBJECT_ID OBJECT_TYPE
---------- -------------------
     13364 PACKAGE

第二次执行

SQL> select t1.object_id,t1.object_type
2 from t2
3 inner join t1 on t2.object_id=t1.object_id
4 where t1.object_id=13364 and t1.data_object_id=90007 and t1.created_time between to_date('2021-8-11','yyyy-mm-dd') and to_date('2021-8-12','yyyy-mm-dd') and t2.status=:status;

OBJECT_ID OBJECT_TYPE
---------- -------------------
13364 PACKAGE