实战课堂:系统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 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

在线重定义的补充测试(r10笔记第26天)

在很多时候,我们都是需要保持业务的可持续性,尽管说DDL的过程持续时间很短,但是在线业务出现,就会阻塞DML,导致业务访问中断,事务收到影响,所以在有些...

36580
来自专栏FreeBuf

经验分享:社会工程学数据库搭建TIPS

最近一直在搞社工库的搭建。网上这方面也有很多文章,但是很少涉及到细节,在此与大家分享一些个人心得。 测试环境 测试坏境:windows server 2012...

32980
来自专栏数据和云

2015 OOW:Oracle的Sharding技术

在2015年OOW大会,国内很多小伙伴们一直非常关心Oracle Database 12.2中的Sharding技术实现,可是要知道在Larry Ellison...

30140
来自专栏Java进阶架构师

「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)

优化,一直是面试最常问的一个问题。因为从优化的角度,优化的思路,完全可以看出一个人的技术积累。那么,关于系统优化,假设这么个场景,用户反映系统太卡(其实就是高并...

11840
来自专栏数据和云

Oracle 12.2 新特性:在线的修改数据表为分区表

众所周知,分区技术是Oracle应用最为广泛的一个选件,并且历史悠久,然而在过去的版本中,如果我们要将一个现有的数据表转换为分区表,则是相当复杂的一个过程,而在...

40350
来自专栏企鹅号快讯

WebGenerate 产品介绍

北京劳恩斯科技有限公司 网址: WebGenerate降本增效的代码生产工具,经过WebGenerate生产的代码适用于Spring、MyBatis、Maven...

19970
来自专栏Python区块链

Python爬取100G级别,2000K以上数据量,用mysql还是mongodb?

这个问题我们可以从两个角度去解答。一个是100G的数据量用MySQL和MongoDB在存读取上有什么区别,另一个是数据本身的结构和你要进行的应用来考虑使用哪种数...

1K150
来自专栏蜉蝣禅修之道

oracle数据库学习之rownum

23220
来自专栏杨建荣的学习笔记

物化视图自动刷新的碰壁(r7笔记第61天)

今天和开发的同事讨论一个问题,他们说source 1的环境中存在一个表,现在希望目标环境target 1和target 2中都需要用到这部分的数据。 ? 对...

37540
来自专栏张善友的专栏

SQLite vs MySQL vs PostgreSQL:关系型数据库比较

自1970年埃德加·科德提出关系模型之后,关系型数据库便开始出现,经过了40多年的演化,如今的关系型数据库种类繁多,功能强大,使用广泛。面对如此之多的关系型数据...

37050

扫码关注云+社区

领取腾讯云代金券