Oracle之系统统计信息

IT那活儿发布于2023-01-11 13:20 / 2066人阅读

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的执行计划。

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绑定执行计划。

参数解释如下：

FLAGS：标志

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

IOSEEKTIM：IO寻址时间（毫秒），直接来自硬件

IOTFRSPEED：IO传输速率（字节/毫秒）

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

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

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

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

MAXTHR：最大IO吞吐量（字节/秒）

SLAVETHR：平均IO吞吐量（字节/秒）

END

更多精彩干货分享

点击下方名片关注

IT那活儿

云服务器 GPU云服务器 PPAS oracle统计信息服务器信息统计系统然之开源系统 linux系统之centos7系统重启查

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129738.html

程序员笔记|循序渐进解读Oracle AWR性能分析报告

摘要：表示用户操作花费的时间，包括时间和等待事件。当内存中排序空间不足时，使用临时表空间进行排序，这个是内存排序对总排序的百分比。过低说明有大量排序在临时表空间进行。要确保，否则存在严重的性能问题，比如绑定等会影响该参数。 Oracle中的AWR，全称为Automatic Workload Repository，自动负载信息库。它收集关于特定数据库的操作统计信息和其他统计信息，Oracle以...

honhon 2019-06-25 19:15 评论0 收藏0
开源|性能优化利器：数据库审核平台Themis的选型与实践

摘要：正是存在问题，促使我们考虑引入数据库审核平台。的确，与很多互联网公司相比，数据库数十套的估摸并不是太大但与互联网类公司不同，类似宜信这类金融类公司对数据库的依赖性更大，大量的应用是重数据库类的，且其使用复杂程度也远比互联网类的复杂。作者：韩锋出处：DBAplus社群分享 Themis开源地址：https://github.com/CreditEaseDBA 拓展阅读：宜信开源|数...

wenhai.he 2019-06-25 19:16 评论0 收藏0