Oracle之系统统计信息

Oracle之系统统计信息

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

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

该醒醒了,起来干活!

事件背景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,如下:Oracle之系统统计信息可以发现已走正确的执行计划,说明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绑定执行计划。

参数解释如下:

FLAGS:标志

CPUSPEEDNW:非工作量统计模式下CPU主频,直接来自硬件

IOSEEKTIM:IO寻址时间(毫秒),直接来自硬件

IOTFRSPEED:IO传输速率(字节/毫秒)

SREADTIM:读取单个数据块的平均时间

MREADTIM:读取多个数据块的平均时间

CPUSPEED:工作量统计模式下CPU主频,根据当前工作量评估出一个合理值

MBRC:oracle收集完统计信息后评估出的一次多块读可以读几个数据块db_file_multiblock_read_count

MAXTHR:最大IO吞吐量(字节/秒)

SLAVETHR:平均IO吞吐量(字节/秒)

发表评论

登录后才能评论
联系客服
联系客服
分享本页
返回顶部