Oracle之系统统计信息

IT那活儿 2021-11-23

1009

点击上方“IT那活儿”，关注后了解更多精彩内容！！！

指缝太宽，时光太瘦。转眼间，我们又迎来了天寒地冻的冷冬—小雪。你，还记得小时候看到雪的样子吗？

雪后，放下手中的电脑，陪家人做一餐饭，逛一逛街；约上三五好友聚上一聚，岂不美哉！

该醒醒了，起来干活！

事件背景

2021年11月1日月初保障，某营业商业务侧反馈凌晨营业B库某进程执行效率较营业A库慢很多，经分析是同一个SQL（sql_id: gucsa3276bhgx）在营业AB库执行计划不一样,营业A该SQL走了TBCS.SUBS_PRODUCT的PRODID列的索引，而营业B的执行计划却走了全表扫描（453GB），在RAC的一个节点去扫描一个453GB的表导致数据库性能严重下降。

在这里说明一下，营业A库和营业B库业务逻辑完全一样，只是地市不同而已。

进一步分析检查营业A、营业B 都是相同的采样比(5%)，在BCV测试环境尝试收集30%的采样后，依然不走索引，经分析因为营业B库Oracle 的优化器CBO 系统统计信息过于陈旧，导致CBO无法精确的评估cpu和io的cost，导致优化器选择错误的表连接方式(营业A 选择了NL、营业B选择了HASH连接)以及执行计划错误。最终导致AB库的执行计划不一样。

系统统计信息和优化器概念

分析之前，我们来看下oracle系统统计信息和优化器的概念。

oracle优化器（optimizer）是oracle数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径，也就是最佳执行计划。以目前最常用的CBO为例，CBO以目标SQL成本为判断原则，通过目标SQL语句所涉及的表、索引、列、系统等的统计信息算出各条执行路径的成本，从而选择成本最小的执行计划作为SQL的执行计划。

Oracle优化器统计信息包括：

No1. 表的统计信息

行数
Block数
行平均长度

No2. 列的统计信息

列中不同值的数量
列中null的数量
数据分布(柱状图/直方图)

No3. 索引的统计信息

叶子块的数量
索引的高度
聚簇因子(clustering factor)

No4. 系统的统计信息

I/O性能和利用
CPU性能和利用

其中系统统计信息背后的概念是衡量系统 CPU 和存储子系统（如 NAS、SAN、JBOD 或闪存）的性能，并在计算替代 SQL 执行计划的成本时使用这些信息。当数据库知道存储和 CPU 的实际速度有多快，它才可以对每个替代计划的成本做出更精细的判断。

问题分析

下面是重头戏，看分析过程。

1 营业AB库执行计划对比

营业A库

营业B库

营业AB库执行计划确实不一致，按经验可以确定是某一块统计信息有误导致。

2 营业AB库统计信息采样对比

营业A库

营业B库

AB库统计信息采样比是一样的，但执行计划却不一样，在BCV库尝试收集了30%的采样比，B库仍然不会走索引，说明表和列本身的统计信息应该没有问题，于是做了一个优化器的trace。

营业A库

营业B库

从优化器的trace来看，经过复杂的cost计算后，营业A库认为NL关联为最优，而营业B库则认为Hash关联为最优。

3 尝试强制指定hint

尝试强制去指定驱动表，更改营业B库表的连接方式：

执行计划：

可以看到执行计划走了正确的索引，说明索引的统计信息也无问题。

4 尝试修改IO的cost

通过修改参数db_file_multiblock_read_count为8（原先为16），尝试重新评估IO的cost，如下：

可以发现已走正确的执行计划，说明IO的统计信息可能有误，检查CBO的系统统计信息，发现B库是2018年收集的，统计信息过于陈旧。

检查营业A库的系统统计信息是2014年收集的。

--重新收集CBO优化器系统统计信息

EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

再次查看执行计划：

营业B库该SQL已经可以走正确的执行计划。

问题总结

综上所述，可以看出由于营业B库优化器的系统统计信息不准，引起SQL语句（sql_id: gucsa3276bhgx）执行计划产生偏差。

一般来说，数据库的系统统计信息只有在主机cpu、内存、存储、操作系统等有进行升级替换的时候才需要重新收集，如下图，可以使用命令进行手动收集，在BCV库测试后，可以发现IOSEEKTIM（IO寻址时间（毫秒））由6变为10，IOTFRSPEED（IO传输速率（字节/毫秒））由54682变为4096，和营业A库一致。但如无异常情况，不建议手动收集系统统计信息，避免带来未知的风险。考虑到系统统计信息重新收集后，可能影响较多的SQL执行计划，建议可以先对问题SQL进行sqlprofile绑定执行计划。