微信号:OraNews

介绍:分享数据库技术、新闻与信息,尤其是和Oracle数据库相关的内容,文章内容来自原创、专栏作者投稿或读者投稿.

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

2018-06-05 23:59 墨墨

在一次客户系统性能优化项目中,经过第一阶段的优化之后,数据库的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)回复关键字获取

2018DTCC , 数据库大会PPT

2017DTC,2017 DTC 大会 PPT

DBALIFE ,“DBA 的一天”海报

DBA04 ,DBA 手记4 电子书

122ARCH ,Oracle 12.2体系结构图

2017OOW ,Oracle OpenWorld 资料

PRELECTION ,大讲堂讲师课程资料

 
数据和云 更多文章 来了!PostgreSQL 同步流复制原理和代码浅析,请签收 招商银行王龙:金融科技银行数据架构设计的13条守则(含PPT) 数据库流行度6月排行榜:Oracle飙升MySQL止跌回升 SQL优化:你真的知道国家字符集的性能影响吗? 诊断案例:Failed parse elapsed time引发db time过高的案例
猜您喜欢 睿云智合 - 容器技术企业落地的领导者 Linux定时原理 App架构设计经验谈:业务层的设计 Flask 入门指南 共享出行业务下的高并发场景