Oracle之系统统计信息
点击上方“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的执行计划。其中系统统计信息背后的概念是衡量系统 CPU 和存储子系统（如 NAS、SAN、JBOD 或闪存）的性能，并在计算替代 SQL 执行计划的成本时使用这些信息。当数据库知道存储和 CPU 的实际速度有多快，它才可以对每个替代计划的成本做出更精细的判断。营业AB库执行计划确实不一致，按经验可以确定是某一块统计信息有误导致。AB库统计信息采样比是一样的，但执行计划却不一样，在BCV库尝试收集了30%的采样比，B库仍然不会走索引，说明表和列本身的统计信息应该没有问题，于是做了一个优化器的trace。从优化器的trace来看，经过复杂的cost计算后，营业A库认为NL关联为最优，而营业B库则认为Hash关联为最优。可以看到执行计划走了正确的索引，说明索引的统计信息也无问题。通过修改参数db_file_multiblock_read_count为8（原先为16），尝试重新评估IO的cost，如下：可以发现已走正确的执行计划，说明IO的统计信息可能有误，检查CBO的系统统计信息，发现B库是2018年收集的，统计信息过于陈旧。EXEC DBMS_STATS.GATHER_SYSTEM_STATS;综上所述，可以看出由于营业B库优化器的系统统计信息不准，引起SQL语句（sql_id: gucsa3276bhgx）执行计划产生偏差。一般来说，数据库的系统统计信息只有在主机cpu、内存、存储、操作系统等有进行升级替换的时候才需要重新收集，如下图，可以使用命令进行手动收集，在BCV库测试后，可以发现IOSEEKTIM（IO寻址时间（毫秒））由6变为10，IOTFRSPEED（IO传输速率（字节/毫秒））由54682变为4096，和营业A库一致。但如无异常情况，不建议手动收集系统统计信息，避免带来未知的风险。考虑到系统统计信息重新收集后，可能影响较多的SQL执行计划，建议可以先对问题SQL进行sqlprofile绑定执行计划。参数解释如下：
FLAGS：标志
CPUSPEEDNW：非工作量统计模式下CPU主频，直接来自硬件
IOSEEKTIM：IO寻址时间（毫秒），直接来自硬件
IOTFRSPEED：IO传输速率（字节/毫秒）
SREADTIM：读取单个数据块的平均时间
MREADTIM：读取多个数据块的平均时间
CPUSPEED：工作量统计模式下CPU主频，根据当前工作量评估出一个合理值
MBRC：oracle收集完统计信息后评估出的一次多块读可以读几个数据块db_file_multiblock_read_count
MAXTHR：最大IO吞吐量（字节/秒）
SLAVETHR：平均IO吞吐量（字节/秒）
文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。
转载请注明本文地址：https://www.ucloud.cn/yun/129738.html
