logo资料库

清算/报表/日终跑批程序之性能优化案例（一）.pdf

发布时间：2022-06-24 发布人：admin 分类：说明书 资料大小：2.07M 资料格式：pdf 举报 版权申诉
第1页 / 共9页
第2页 / 共9页
第3页 / 共9页
第4页 / 共9页
第5页 / 共9页
第6页 / 共9页
第7页 / 共9页
第8页 / 共9页
资料共9页，剩余部分请下载后查看
    中亦科技黄远邦 ——清算 / 报表 / 日终跑批程序之性能优化案例（一） 不知不觉，技术人生系列·我和数据中心的故事来到了第五期。小 y 又和大家见面了！前几期主 要发了一些 TroubleShooting 的案例分享，其实小 y 最擅长的是性能优化，所以从这期开始， 小 y 会陆续的分享更多的数据库性能优化案例。 进入正题，如果您的日终跑批 / 清算 / 报表等程序时快时慢，或者从某一天以后就一直变慢，作为 运维 DBA 或开发的您，会怎么下手？还有，除了解决问题外，你要如何解答领导最关心的一个问题，“为 什么现在有问题，但是以前没有问题呢”！ 小 y 今天要和大家分享的就是这样一个性能问题的分析和解决过程。 1、问题来了 小 y, 有空么？一会一起看一个报表的性能问题。 有个 SQL 语句一周前开始，性能急剧恶化，执行时间从 10 分钟以内变成了 10 个小时以上。 刚在客户现场做完 Oracle 的培训，问题来的正是时候，刚好可以让客户感受下理论如何融入实战 的魅力！小 y 的第一想法是 SQL 语句的执行计划发生了改变，通常从统计信息或者 CBO 对 cardinality 的估算情况中就可以快速找到线索，应该很快就可以查明原因并解决！ 最后的事实证明，小 y 一开始想简单了。针对这个问题，客户通过并且重新收集统计信息或重启数 据库均无法解决问题。幸运的是，小 y 及时调整回到了学院派模式，最终在一个小时内找到了问题的原因， 问题的解决也就是顺其自然了。
    环境介绍： 操作系统 Redhat 64 bit 数据库 Oracle 11.2.0.3 , 2 节点 RAC 2、分析过程 >>> 2.1 完整的 SQL 语句 小 y 对这条 SQL 进行了敏感信息处理和写法的简化处理，可以看到： Ø 该 SQL 对两张表张进行 join，然后 group by Ø 参与关联的两张表一张是 80M 的小表，另外一张是 3.5G 的较大一些的表。记录数分别是 160 万和 800 万 Ø SQL 语句用了 hint，提示优化器表连接走 hash join, 单表访问路径小表走全表扫描。 这样的一条 SQL，按照小 y 的经验，驱动表只要选择小表，那么整个 HASH JOIN 的执行时间基本 等同于两张表的单表访问时间，两张表加起来不到 4G，通常都可以在 5 分钟内完成。这和客户描述的 以前的执行时间是相吻合的。 这里顺便说一下： 很多开发写 hint 往往写的不完整，例如这个 hint 只写了表连接方式，单表访问路径只写了一张表， 表的连接顺序没有写，其实并没有完全固定死执行计划。 接下来，小 y 将查看执行计划是否发生变化，还有执行计划是否正确。 >>> 2.2 执行计划 可以看到： Ø 执行计划（oracle 内部的算法）确实如 hint 一样 Ø 表连接方式走的是 hash join Ø 单表访问路径都是全表扫描 (table access full) Ø 表连接顺序是小表做驱动表 (hash 内存表） 这是一个完美、最优的执行计划。唯一的小缺点是优化器评估 hash join 和 hash group by 的步骤 用到了一些临时表空间，不过这只是 CBO 的评估，不代表实际会发生。
    对比了以前的执行计划，也是一样的。 既然执行计划没有问题，也没有发生改变，那么就需要将 SQL 的执行时间进行分解，看看时间到底 消耗在了是 CPU 还是 IO、集群、并发竞争等什么环节。 >>> 2.3 SQL 执行的相关统计 可以看到： 1）每次执行时间 39615 秒，超过 10 个小时 2）每次执行逻辑读只有 45276 个 block（块） 3）每次执行物理读 451421 个 block（块） 4）时间基本都消耗在 CPU 上，达到 38719 秒，超过 10 个小时，而在 IO/ 集群 / 应用（锁）/ 并 发环节消耗时间很小 >>> 2.4 第一次头脑风暴 到了这里，经验丰富的 DBA 应该可以发现，该 CASE 出现了一些奇怪的现象。不过还是要照顾一 下大家，先来回答一些朋友心里可能的问题。 2.4.1 是不是有什么异常等待事件 看到这里，也许有人会说 : 是不是 SQL 语句执行过程中有什么异常的等待事件？ 首先答案是 NO ！ 因为整个 SQL 的执行时间中，时间基本都消耗在 CPU 上，达到 38719 秒，超过 10 个小时，而在 IO/ 集群 / 应用（锁）/ 并发环节消耗时间很小（加起来不到 100 秒）。如果 SQL 跑在 CPU 上，那么 是不会有等待事件的线索的。时间分布如下图所示。 2.4.2 是不是 hash join One-pass/Muti-pass 导致慢 也许有人会说：执行计划出现了 temp 表空间的使用，是不是 hash join One-pass/Muti-pass 导 致 SQL 执行慢
    答案是 NO ！ 首先，执行计划中显示会用到 temp 表空间 (hash join one-pass/muti-pass)，这是 CBO 执行前 的评估而已，实际执行很可能根本不会使用。 其次，如果真的使用 temp 表空间，并且成为整个 SQL 的瓶颈，则我们会看到很多的 direct path read/write temp, 由于这类等待事件算在 IO 类的等待事件里，那么整个 SQL 语句的执行事件就应该是 IO 占的最多而不是现在看到的时间都消耗在 CPU 上。 2.4.3 小 y 的疑惑 到这里，小 y 开始感觉到了这个 case 需要更专注来解决了！ 执行时间基本都耗在 CPU 上，这通常意味着所需要的数据基本都在内存中。 一个常识是，如果所需要的 BLOCK 在内存中，那么 CPU 每秒可以处理 10 万甚至几十万的逻辑读！ 但具体到这条 SQL， 10 的小时的 CPU 时间，处理的逻辑读，才有 45 万！ 45 万的逻辑读刚好对应 4G 的大小，即两张表的大小之和。 目前确实有一些奇怪的地方，小 y 接下来需要： Ø 和历史执行时间的分解进行比对 Ø 将这条 SQL 语句重新跑起来，获取更多的线索。 >>> 2.5 历史执行情况比对和确认 可以看到： Ø 一开始的时候，每个小时还可以处理 44 万的逻辑读，但是后来就慢了起来 Ø 后来的绝大部分时间里，每个小时才处理 1000-3000 的逻辑读 Ø 执行时间确实都在 CPU 上！ 可惜的是，由于 AWR 报告只保留 7 天，因此未能获取到原来的执行时间的分解的情况，也就没有 办法做正常和异常时刻的比对。接下来，这是一个 SELECT 语句，可以直接跑起来重现问题，这样小 y 可以观察到更多的线索！
    >>> 2.6 重现问题实时抓取线索 将这条 SQL 语句重新跑起来，然后开启其他窗口观察，一开始的 1 分钟内还算正常，先后读取小 表和大表，IO 差不多到每秒 30M，然后 IO 就急剧的下降了，这个时候等待事件是 ON CPU。 小 y 立马查看了 SQL 的执行进度，v$session_longops 中表 SMALL_TABLE 已经扫描完成，但另 外一张表 BIG_TABLE 全表扫描的进度进本停留在 82% 的位置！但细看还是涨的，只是涨的比较慢！如 下图所示。 对 BIG_TABLE 的全表扫描，sofar 基本上每 5 秒才涨 1 ！ 按照这个速度，还需要（442460-362690）*5=40 万秒，即 10 个小时以上！这和“历史执行情况 比对和确认”章节是可以对上的！ 这里提示一下，涨的慢和 IO 性能没关系，上面已经分析过了，时间都消耗在 CPU 上 接下来，读者朋友们，可以停一下，把上述现象总结一下，再思考个几分钟、 如果是您来接这个 CASE，你会怎么继续往下查呢？ 不要走开后边还有 ..... >>> 2.7 没有等待事件如何往下查 既然 SQL 执行是在 CPU 上，那么就不会有什么等待事件的线索留出来，既然在 CPU 上，那么必 然要去看 call stack, 这是小 y 多年养成的习惯了。 通过 oradebug short_stack, 间隔几秒抓取了三次。如下图所示： 到了这里，小 y 已经做完了所有的检查。 是时候出去抽一根烟了，需要把所有发现的线索在脑子里过一次。 幸运的是，一根烟后，小 y 终于把所有问题都想明白了，所有看到的现象都可以说清楚了 , 还有领 导最关心的一个问题—“为什么现在有问题，但是以前没有问题呢”！，小 y 也有了答案。 建议朋友们，读到这里也可以先停一下，思考个几分钟，看看自己是否已经找到了问题原因。 到这里已经找到答案的朋友，可以发小 y 发一份简历，说明你有不错的思考能力和经验！欢迎你加 入中亦科技 Oracle 服务团队！简历请发 51994106@qq.com
    >>> 2.8 第二次头脑风暴 在出门抽烟的这一小会功夫里，小 y 不断思考着几个问题。 Ø 为什么每个小时才处理几千个逻辑读呢？ Ø SQL 执行时间都消耗在 CPU 上，都在做什么呢？ Ø 为什么以前不出，现在出呢？ 下图的这个函数 qerhjWalkHashBucket，将所有问题都彻底解释清楚了 ! qerhjWalkHashBucket 就表示在做 hash join 的过程中需要遍历 hash bucket 中的数据。 因此，小 y 重新缕了一下 Hash Join 原理，例如两张表 A 和 B 表的整表关联 SELECT * FROM A,B WHERE A.ID=B.ID ORACLE 内部的执行过程，可以简化为： Ø SCAN A（扫描 A 表） Ø HASH(A.ID), 打散到各个桶（BUCKET）中，呆在 pga hash area 中等待别人来匹配 Ø SCAN B（扫描 B 表） Ø HASH(B.ID) Ø 到相应的 Bucket 中，比较表关联字段的值是否相同，返回或丢弃 HASH 的目的是为了打算数据到各个桶中。每个算法都有优缺点。 那么 HASH JOIN 有什么缺点呢？ 我们是否命中了该缺点呢？！ >>> 2.9 真相浮出水命 很显然，当驱动表在内存中里的其中一个桶里 （bucket）的数据很多的时候，那么被驱动表的一 个值到该桶里比较起来就很需要遍历更多的数据，这个时候就类似于 nest loop 了。那么一个值的比对 就需要很久了！ 被驱动表一个 BLOCK 可以存储几十到几百条记录，而一条记录需要到一个记录很多的桶里去比较 很久，被驱动表一个 BLOCK 有很多条记录，自然就出现了每个小时只能处理几千个逻辑读的情况了！ 也就观察到了 v$session_longops.sofar 涨的很慢的情况了！ 同时，关联字段大量比较的过程是很消耗 CPU 的 （当驱动表读进 PGA 里后就呆在 PGA 内存中了） 那么为什么以前不出呢？ 那是因为以前驱动表的关联字段的数据分布是均匀的！而自从某一天以后，表关联字段的分布开始 不均匀了！ 发出 SQL，验证如下 :
    可以看到，驱动表 small_table 中 id=0 的记录数达到 17 万条，意味着一个 bucket 的数据至少达 到 17 万条，这与 hash join 打散数据到各个 bucket, 通常一个 bucket 的数据不超过 5 条的想法和设计 初衷是相违背的！ 至此，所有问题得到了圆满的解答！ >>> 2.10 进一步验证 在 SQL 语句中加入 small_table.id != 0 的过滤条件，small_table 的数据从 160 万减少到 143 万， 变化不大的情况下，执行上述 SQL，执行时间在 3 分钟左右就完成了！ 这就验证了 hash join 不适合驱动表表关联字段分布不均匀的一个缺点 ！ >>> 2.11 解决方案 知道原因了，那么解决方案就多种多样了！ hash join 不适合驱动表表关联字段分布不均匀的情况，因此解决方案有多种 1） 采用 use_merge 的 hint 而非 use_hash，无法修改程序的情况可以通过 sql profile 指定执行 计划。这里两张表都不大，排序合并连接也很快。 2） 对驱动表 small_table.id=0 的数据进行调查、确认和处理，为什么会在某一天突然出现大量 id=0 的数据，是否可以删除 ……
    专家团队成员 黄远邦 数据库产品团队负责人 十年以上数据库维护 经验，擅长人才培养、 复杂故障、复杂性能 问题定位和解决、无 备份时的数据拯救。 陈宏义 杨元同 张海亮 王远军 二十年以上数据库 维护经验，曾就职 于原厂二线支持团 队, 擅长复杂性能、 复杂故障问题定位 和解决。 十年以上数据库维 护经验，曾就职于 原厂售后团队 , 擅 长复杂故障、复杂 性能问题定位和解 决、无备份时的数 据拯救。 十年以上数据库维 护经验，曾就职于 原厂售后团队，擅 长 SQL 调优、故障 处理、性能调优。 二 十 年 以 上 数 据 库 维 护 经 验， 曾 就 职 于 原 厂 售 后 团 队， 擅 长 故 障 处理、性能调优。 李瑞龙 周永康 林浩南 佟长胜 夏海东 十年以上数据库维 护经验，擅长复杂 故障、复杂性能问 题定位和解决。 八年以上数据库维 护经验，擅长 SQL 调优、故障处理、 性能调优。 六年以上数据库维 护经验，擅长故障 处理、性能调优。 八 年 以 上 数 据 库 维 护 经 验， 擅 长 SQL 调 优、 故 障 处理、性能调优。 二 线 专 家， 擅 长 复 杂 环 境 下 的 故 障 排 查 和 性 能 优 化。 王培中 卞其龙 姜沛 唐毅 黄志刚 二 线 专 家， 擅 长 SQL 调 优、 复 杂 性能问题定位和解 决。 二 线 专 家， 擅 长 Oracle 数据恢复迁 移、故障处理、技 术培训、性能优化。 二 线 专 家， 擅 长 goldengate，数据 库故障定位及升级 迁移。 八 年 以 上 数 据 库 维 护 经 验， 精 通 ORACLE、DB2 复 杂 故 障 定 位 排 除。 擅 长 性 能 调 优、灾备恢复。 七 年 以 上 数 据 库 维 护 经 验， 擅 长 故 障 处 理、 版 本 升级、迁移实施。
    分享到：
    赞 收藏

    相关推荐

    资料库

    数据库

    共收录1218份资料，累计14个分类，关注成员有19位，主要包括：NoSQL,MongoDB,Redis,SQLite,Memcached,DB2,Sybase,Informix,PostgreSQL,MySQL,SQLServer,Access,Oracle,其它

    热门标签

    NoSQL MongoDB Redis SQLite Memcached DB2 Sybase Informix PostgreSQL MySQL SQLServer Access Oracle 其它

    最新资料