专栏首页数据和云实战课堂:系统CPU高消耗的SQL筛选和最佳索引优化

实战课堂:系统CPU高消耗的SQL筛选和最佳索引优化

在一次客户系统性能优化项目中,经过第一阶段的优化之后,数据库的DB Time和物理读都明显降低,但是我们发现CPU并没有明显降低。

对此,我方针对占用CPU较高的SQL进行了分析,并继续寻找优化空间。

找出占用CPU高的CPU有很多办法,比如:

通过操作系统高CPU消耗的Oracle进程,通过其 PID 和数据库内部视图 v$process ,v$session 关联,找到相关SQL。 通过 AWR 的历史信息,获取TOP CPU消耗的SQL列表,再针对性的分析

从思路二出发,首先通过查询 DBA_HIST_SQLSTAT 字典表,获取 CPU 按照使用率的SQL列表:

这其中排在最前列的主要是2条SQL语句,通过 v$sql 可以找到其SQL文本:

那么接下来的问题,就是分析这两条SQL,找到提升其效率的办法。

两条SQL共占用CPU TIME 30%以上。这两条SQL基本一致,只是mod一个字段的值不同,一个筛选mod之后为1的数据,一个筛选mod之后为0的数据。经验证这两条SQL解决方法一致,以其中一条为例。

select * from GBORDDDRETURN_UP_SCAN t where mod(SEQ_ID, 2)=1 and status >0 and upload_state = 0 and work_type in('10', '11', '12', '13', '20') and rownum <= 200 order by done_date

该SQL的执行统计信息如下,单次执行需要接近 5s 时间,消耗逻辑读 125,887,而平均每次执行返回 0.01 行,也就是说绝大多数查询是不返回满足条件的结果的。而如果 1393 次执行,只返回 10 行记录,那么单次的逻辑读消耗就显得高的可怕。这也是高 CPU 消耗的原因。

考察一下执行计划,可以看到一个索引被使用到,很多时候我们认为走索引就问题不大,这个常规判断在这里显然不成立,这个效率不高的索引是高逻辑读的主要原因。

我们再来分析一下表的元数据,可以看到现有索引的效率不佳,过滤性极差:

那么我们继续分析一下查询中的其他条件,以期望尽快的筛选记录,减少逻辑读。

通过分析我们注意到,虽然status和upload_state字段单独的过滤性都很差,但是放在一起却是一个非常好的条件。这两个条件可以快速筛选:

在创建了新的索引之后,可以看到整个SQL的执行效率大大提升:

建立该索引之后,执行时间由4966 ms降低到10m秒以内。逻辑读由125887降低到10以下。系统的CPU消耗得以快速消减。

这个案例给我们的启示是:

有效的索引才是好的索引; 如果单行查询逻辑读过高,一定需要对SQL进行单独的审核和优化;

多看多知,这就是实战课堂。

本文分享自微信公众号 - 数据和云(OraNews),作者:墨墨

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-06-05

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle Hints - 先知的提示

    在上周恩墨微信大讲堂的讨论中,几个有趣的视图跃入我们的视野,可以分享给大家。 在Oracle 11g中,新增的视图V$SQL_HINT记录了Oracle数据库中...

    数据和云
  • 为什么用尽了办法你的系统性能还是不见改善

    随着业务数据的增长,以及新业务的推出,很多企业都面临着系统性能的问题,并且日益凸显。我们曾遇到很多这样的用户,似乎用尽了所有招数,但性能就是不见改善,问题到底出...

    数据和云
  • 杨廷琨Oracle Code大会分享:如何编写高效SQL(含PPT)

    2018 Oracle Code 于5月17日在新加坡拉开帷幕。作为全球开发者交流分享的年度盛会,为吸引所有领域的开发者,Oracle今年将自1996年开始的J...

    数据和云
  • 循序渐进解读Oracle AWR性能分析报告

    Oracle中的AWR,全称为Automatic Workload Repository,自动负载信息库。它收集关于特定数据库的操作统计信息和其他统计信息,Or...

    宜信技术学院
  • 解锁不可见索引新特性,处理ORA-01555故障

    何国亮 云和恩墨交付部技术顾问,获得 Oracle 11g OCM 认证。有超过 6 年超大型数据库专业服务经验,曾为通信运营商、银行、保险、政府、制造业...

    数据和云
  • BI商业智能与大数据的区别

    商业智能(Business Intelligence,简称:BI),又称商业智慧或商务智能,它是一套完整的解决方案,用来处理企业中现有数据,并将其转换成知识、分...

    数据前沿
  • 全球芯片攻坚战:3nm台积电后年量产,2nm进展顺利,1nm遥遥无期

    目前有35项卡脖子技术,光刻机和芯片赫然在列。华为、阿里、百度纷纷推出自研芯片。在国家的扶持和资本的推动下,国内芯片企业遍地开花。一时之间,仿佛所有厂商都宣布要...

    新智元
  • 同样是晶体管,为什么寄存器比内存快?

    计算机的存储层次(memory hierarchy)之中,寄存器(register)最快,内存其次,最慢的是硬盘。

    黄泽杰
  • 为什么寄存器比内存快?

    计算机的存储层次(memory hierarchy)之中,寄存器(register)最快,内存其次,最慢的是硬盘。

    帅地
  • 为什么寄存器比内存快?

    计算机的存储层次(memory hierarchy)之中,寄存器(register)最快,内存其次,最慢的是硬盘。

    Linux阅码场

扫码关注云+社区

领取腾讯云代金券