Oracle数据库11G新特性-Cardinality Feedback

ORALCE11G新特性之Cardinality Feedback

1. 概述

在Oracle 11gR2的版本上推出了基数反馈（Cardinality Feedback 以后简称CFB）功能，通过这个特性，对于某些查询在第一次执行时，如果CBO发现根据统计信息估算出的基数(Computed cardinality)和SQL执行时的实际值差距很大的情况发生时，在SQL下次执行时，会根据实际值调整基数，重新生成执行计划。

Cardinality Feedback基数反馈是版本11.2（11.2.0.1及以后）中引入的关于SQL 性能优化的新特性，该特性主要针对 统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况，Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估，造成CBO选择不当的执行计划。

另外，基数反馈 （CFB）在12c版本上得到更进一步的扩展改称为统计反馈（Statistics Feedback），成为12c自动重新优化（Automatic Reoptimization）的一部分。

1. 发生情景

在普通用户下，在sys用户下是不会发生feedback特性 

在下列情况CBO可能无法估算出准确的Cardinality，Oracle会启用CFB功能：

1 手工指定表的cardinality值

select /*+ cardinality(test,  1) */ count(*) from test;
2 没有收集表的统计信息，并且dynamic samping也没有开启 
3 查询条件复杂（比如条件有函数）或者涉及多列，但是没有收集扩展统计信息（extend statics）

针对上述情况，Oracle会采取如下的CFB流程处理：

SQL文第一次执行时，Oracle会监控操作的实际行数（A-Row），然后对比CBO估算的行数（E-Row）,如果两个值相差很大，就记录实际行数（A-Row），做上标记,下次执行时再次进行硬解析，根据实际行数来重新生成执行计划,如果两个值相差不大，CBO就不再监控这条SQL语句

使用的标志;统计信息中有：

Note

cardinality feedback used for this statement

（1）实际案例1 

在2018年12月1日晚上9点左右，业务反馈，存在sql语句的执行效率严重下降，从原来的2分钟执行到20分钟左右，执行效率严重下降，对相应的语句进行分析。

使用如下sql语句查询产生feedback的sql_id对应v$sql查询对应的sql_ID分析执行计划，由于是绑定变量的方式，对应的sql_id不同。查询到对应的sql_id为

select sql_ID,USE_FEEDBACK_STATS FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS =’Y’;

2u8qs2pfut099

9ndaayk7rnpkd

4yk17ty6t05ry

5d5x36qyzv136

frm8dwhzwzygp

87prs4dcgjkta

186y99fkmyu3g

at4f81rs8uqvj

bq2b9s10njqzc

8szmwam7fysa3

根据反馈对应的语句查询之心计划，分析语句变慢的原因 ,

SELECT * FROM TBALE(DBMS_XPLAN.DISPLAY_CURSOR('5d5x36qyzv136'));

 

发现语句在执行过程中采用的历史告警表的分区剪裁的全分区扫描，执行效率可想而知，又注意到在执行过程中进行了feedback，执行计划并非最优，在session级别使用hint走索引执行计划正常。

1. 禁用feedback

禁用feedback可以在session级别以及system级别进行禁用，以及使用hint的方式进行sql禁用。

hint : opt_param(‘_optimizer_use_feedback’ ‘false’) 
修改参数：

alter system set “_optimizer_use_feedback”=false scope=both;

alter session set “_optimizer_use_feedback”=false scope=both;

hint：cardinality(test, 1)强制使用

查看share pool中还有那些sql用到了feedback

select sql_ID,USE_FEEDBACK_STATS FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS =’Y’;

（1）实际案例2

今日对数据库进行优化分析，发现一个sql语句执行， 驱动表包含合适的索引 而且连接列也存在唯一索引 ，使用NL连接比较合适，而且还存在 group by 排序，但是对sql进行分析过程中发现。

会话等待一直出现大量的direct path read等待，匪夷所思。

查询

select * from dba_sql_plan_baselines where sql_id=&sql_id 

为空，多个执行计划

select * from table(dbms_xplan.display_awr('sql_id’） 

发现sql存在多个版本的执行计划 ，其中就包含对驱动表以及非驱动表的全表扫描 计划 。（产生原因 为Cardinality Feedback ）

alter system set “_optimizer_use_feedback”=false scope=both;

关闭之后对应的执行计划比较正常。