好好的数据库怎么跑不动了？（基数反馈引起的性能问题）-CSDN博客

本文链接：https://blog.csdn.net/xiaofan23z/article/details/136830478

欢迎关注作者，更多数据库相关安装配置，troubleshooting，调优，备份恢复等资源
CSDN：潇湘秦-CSDN博客

公众号：潇湘秦

---------------------------------------------------------------------------------------------

周六傍晚开车带着老婆儿子去山姆超时购物，半路开发同仁打电话反应，说MES有两个功能模块超时，跑不出来让帮忙看看是不是数据库有问题？

做为一个合格的运维dog，找个路边停车，从后备箱拿出电脑，连上热点，打开vpn登陆到公司内网开始排查问题！

老婆开车，我在后座工作！

1.问题处理过程

先打开OEM看看数据库整体状态

看到数据库负载呈现规律性升高降低

从top sql中可以看到一个sql id 执行时间过久

之前遇到过sql执行计划变化引起的性能问题，这里首先想到的就是用coe_xfr_sql_profile.sql

查看一下是不是选择了效率低的执行计划？

查询结果可以看到这个sqlid 有五个执行计划，而现在却选择了最差的平均时间35秒的执行计划

以恢复系统为第一优先级，最简单的办法就是使用coe_xfr_sql_profile.sql来绑定执行计划

参数1：为问题sql id 参数2：为需要绑定的执行计划，执行结束后会生产一个sql如下

coe_xfr_sql_profile_54ftmkvm9z5kj_4062773594.sql

执行这个sql 就会将sql id和最优的执行计划绑定；

另外还有一个sql 更离谱，也利用coe_xfr_sql_profile.sql来做修正，修正后系统恢复正常！

恢复正常后的OEM数据库负载图

2.为什么执行计划会跑偏

周一上班再来仔细追查一下sql的执行计划为什么会跑偏？

首先使用sqlhc来查看一下sql的具体执行情况，sqlhc是oracle提供的一个sql性能调优工具，具体可以查看如下文档

SQL Tuning Health-Check Script (SQLHC) (Doc ID 1366133.1)

使用方法也简单，选择模式（TDN），输入需要诊断的sqlid

在当前目录下会生成sqlhc_20240318_1324_54ftmkvm9z5kj.zip压缩包

压缩包下有如下几个文件，这里重点关注2/3文件

diagnostics summary

关键信息1：sql plan summary

可以看到这个sql有五个执行计划，第五个是最差的

关键信息2 ：sql statistics

可以看到10号前后执行计划都是最优的4062773594

可以看到问题时段有几个执行计划选择了最差的2891383906，也就是造成系统卡顿的主要原因

关键信息3：查看具体的sql plan

找到有问题的sql plan

在sqlplan的最后有note 关键信息

cardinality feedback used for this statement，表面改执行计划是因为基数反馈而从新生成的执行计划

另外一个520秒的异常sql plan也是因为基数反馈

3.何为基数反馈？

Cardinality Feedback是11gR2出现的新特性(ps:12C后改名为Statistics Feedback，统计反馈，本文还是以个人习惯称为基数反馈），基数反馈是优化器自动改进对基数估计错误的重复查询的计划的能力。由于多种原因，优化器可能不正确地估算基数，例如缺少统计信息、不准确的统计信息或复杂的谓词。基数反馈帮助优化器从错误计算中学习，以便使用更准确的基数估计生成更好的计划。

基数反馈是如何工作

即使统计数据被尽可能准确地计算，估计的基数可能也是不准确的。在第一次执行 SQL 语句时，会生成一个执行计划。在计划优化期间，会注意到某些类型的估算，并监视生成的游标。执行完成后，计划中的一些基数估算会与执行期间实际观察到的基数进行比较。如果发现这些估算与实际基数存在显著差异，则会存储更正后的基数以供以后使用。下次执行查询时，将再次对其进行优化（硬解析），而这次优化器将使用这些更正后的估算值来替代之前使用的原始估算值。基于更准确的统计数据可能会创建不同的计划。

Oracle 能够使用统计反馈重复地重新优化语句。这可能是必要的，因为基数差异可能取决于计划的结构和形状。因此，在第二次执行查询时，使用统计反馈生成新计划后，仍可能发现更多的基数估算与实际基数存在显著偏差。在这种情况下，Oracle 可以在下次执行时再次重新优化。

但是，有一些保障措施可确保在少数执行后这种情况将稳定下来，因此您可能会在最初的几次执行中看到计划的变化，但最终将选择出一个计划，并用于所有后续的执行。

流程图如下

官方建议什么情况下适合启用基数反馈

没有统计信息且未使用动态采样表
表上有多个连接或分离的过滤谓词，且没有扩展统计信息
包含复杂运算符的谓词，优化器无法准确计算选择性估算值
在某些情况下，还有其他可用的技术来改善估算；例如，动态采样或多列统计允许优化器更准确地估算连接谓词的选择性。在这些技术适用的情况下，Statistics Feedback将不被启用。
然而，如果对于相关列的组合不存在多列统计信息，则优化器可以回退到使用Statistics Feedback。

如何关闭基数反馈

基数反馈有隐含参数_OPTIMIZER_USE_FEEDBACK控制，默认是开启的，可以在session和system级别关闭

 1.会话级别或者系统级别关闭基数反馈 

 alter session set "_OPTIMIZER_USE_FEEDBACK"  = FALSE;

alter system set "_OPTIMIZER_USE_FEEDBACK"  = FALSE;

2.sql级别加hint

select   /*+ opt_param('_optimizer_use_feedback' 'false') */  ...

为什么基数反馈后执行计划反而变坏

参考Bug 16837274 - Cardinality feedback produces poor subsequent plan (Doc ID 16837274.8)

Description
A suboptimal execution plan may be produced due to cardinality feedback for 
the object on the right side of NLJ .（nested loops join）
 
Rediscovery Notes
 Bad plan due to cardinality feedback for the object on the right side of NLJ.
 
Workaround
 Set "_optimizer_use_feedback"=false
  
Note:
 This fix effectively fixes all of the cases fixed by Bug 13454409 and should
 be used instead of that fix.

基数反馈是否会经常发生

是的只要游标被share pool剔除，再次被加载到share pool时就会触发基数反馈

4.处理办法

数据库当前统计信息正常，近期未有DDL相关操作，出问题的sql确实存在复杂的表连接和大量的nested loops，而且也不是第一次出现这种情况

综合以上信息在系统级别关闭了技术反馈功能，并将不好的sql plan flush出share pool！

如何将sql plan 刷出share pool

 1.Find ADDRESS and HASH_VALUE using SQL_ID

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '<SQL_ID>';

Example:

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='XXXXXXXXXXX';

ADDRESS HASH_VALUE
---------------- ----------
000000085FD77CF0 808321886

2) Now purge the plan from Shared pool using DBMS_SHARED_POOL procedure

SQL> exec DBMS_SHARED_POOL.PURGE ('000000085FD77CF0, 808321886', 'C');

PL/SQL procedure successfully completed.

NOTE:

‘C’ (for cursor) or ‘S’ (for SQL)

3) Check the shared pool again after the purge successfully completes which should show no rows. 

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='XXXXXXXXXXX';

no rows selected