ORALCE11G新特性之Cardinality Feedback
- 概述
在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)的一部分。
在普通用户下,在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语句
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走索引执行计划正常。
禁用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;
关闭之后对应的执行计划比较正常。