一条全表扫描sql语句的分析 (r4笔记第32天)

今天在对生产系统做监控的时候,发现一个process的cpu消耗很高,抓取了对应的session和执行的sql语句。 发现是一个简单的update语句,这样一条如果CPU消耗较大,很可能是由于全表扫描的。 UPDATE COMM_ACTIVITY SET COMM_ACTIVITY.EXTRACT_STATUS = NVL(:1 , EXTRACT_STATUS), COMM_ACTIVITY.SOURCE_TYPE = NVL(:2 , SOURCE_TYPE), OPERATOR_ID = :3 , APPLICATION_ID = :4 , DL_SERVICE_CODE = :5 , DL_UPDATE_STAMP = :6 , SYS_UPDATE_DATE = SYSDATE where COMM_ACTIVITY.ACTIVITY_CODE=:7 AND COMM_ACTIVITY.EXTRACT_STATUS=:8 查看了对应的sql执行计划,发现和预期是一致的。 Plan hash value: 557276772 ---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | UPDATE STATEMENT | | | | 11187 (100)| | | 1 | UPDATE | COMM_ACTIVITY | | | | | |* 2 | TABLE ACCESS FULL| COMM_ACTIVITY | 370K| 13M| 11187 (1)| 00:02:15 | ----------------------------------------------------------------------------------------

对这样的一条语句,该怎么判定呢? 首先走全表扫描是否合理,这个可以从表的数据量来判定,如果表中数据很多,全表扫描肯定是很不划算的,如果就几十几百条数据,走全表扫描应该没有什么影响。这个表中的数据在百万以上,所以走全表扫描还是需要分析原因的。 其次需要查看对应的索引信息,如果存在对应的索引信息而不走索引,那么就很可能是由于数据类型冲突导致的。具体的案例可以参见:http://blog.itpub.net/23718752/viewspace-1142678/ 如果不存在对应的索引,那么需要考虑这种执行的结果是否可接受,这个判定的一个标准就是执行的频率,如果某个语句执行频率很高,走了全表扫描,资源消耗大,就很可能是需要改进的。 如果某个语句走了全表扫描,但是执行频率很低,几天,一个星期左右执行一次,那么这样的影响相对就小很多,执行的情况也是基本可以接受的。 对于这条sql语句,我查看了对应的索引信息,发现没有符合的索引列。

INDEX_NAME                     TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST                    TABLE_TYPE STATUS   NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- -
     COMM_ACTIVITY_PK           INDXS01    NORMAL     UNIQUE    NO  COMM_ACT_ID                    TABLE      VALID     2967610 23-OCT-14 N

然后更进一步,我抓取了近几天的执行情况。

BEGIN_INTERVAL_TIME            SQL_ID        SUM(CPU_TIME_DELTA) SUM(DISK_READS_DELTA) SUM(EXECUTIONS_TOTAL)   COUNT(*)
------------------------------ ------------- ------------------- --------------------- --------------------- ----------
29-JAN-15 12.51.00.254 AM      4pbpdtw14s7bg            65290070                     1                 10299          1
29-JAN-15 01.00.00.873 AM      4pbpdtw14s7bg            73191873                     0                 10365          1
29-JAN-15 01.10.01.496 AM      4pbpdtw14s7bg            72758932                     0                 10431          1
29-JAN-15 01.20.02.802 AM      4pbpdtw14s7bg            73899773                     0                 10495          1
29-JAN-15 01.30.03.476 AM      4pbpdtw14s7bg            70535269                     0                 10560          1
29-JAN-15 01.40.05.746 AM      4pbpdtw14s7bg            73060894                     0                 10626          1
29-JAN-15 01.50.06.911 AM      4pbpdtw14s7bg            70580265                     0                 10691          1
29-JAN-15 02.00.07.399 AM      4pbpdtw14s7bg            69498430                     0                 10754          1
29-JAN-15 02.10.08.026 AM      4pbpdtw14s7bg            62471507                     0                 10811          1
29-JAN-15 02.20.08.507 AM      4pbpdtw14s7bg            66107955                     0                 10872          1
29-JAN-15 02.30.08.954 AM      4pbpdtw14s7bg            66053960                     0                 10933          1
29-JAN-15 02.40.09.427 AM      4pbpdtw14s7bg            69912367                     0                 10999          1
29-JAN-15 02.50.10.190 AM      4pbpdtw14s7bg            66570877                     0                 11062          1

BEGIN_INTERVAL_TIME            SQL_ID        SUM(CPU_TIME_DELTA) SUM(DISK_READS_DELTA) SUM(EXECUTIONS_TOTAL)   COUNT(*)
------------------------------ ------------- ------------------- --------------------- --------------------- ----------
28-JAN-15 12.50.50.048 AM      4pbpdtw14s7bg            38913085                     0                  2696          1
28-JAN-15 01.00.52.505 AM      4pbpdtw14s7bg            37876244                     0                  2732          1
28-JAN-15 01.10.54.153 AM      4pbpdtw14s7bg            38930083                     0                  2768          1
28-JAN-15 01.20.55.304 AM      4pbpdtw14s7bg            38816100                     0                  2803          1
28-JAN-15 01.30.55.968 AM      4pbpdtw14s7bg            37014377                     0                  2838          1
28-JAN-15 01.40.56.555 AM      4pbpdtw14s7bg            38199193                     0                  2873          1
28-JAN-15 01.50.57.720 AM      4pbpdtw14s7bg            38408164                     0                  2908          1
28-JAN-15 02.00.58.545 AM      4pbpdtw14s7bg            36498453                     0                  2943          1

从执行的情况来看,还是比较频繁的。从以上的数据就可以表明这条语句对系统造成了一定的影响,需要考虑改进。 改进的思路有两种, 第一种就是从业务角度来看,是否可以引入主键列,如果引入了主键列,性能就会得到极大的提升,这样也从业务上进行了优化。 第二种就是考虑增加相应的索引,综合评估目前的数据分布情况,来判定是否需要增加相应的索引,使得update语句中的where条件部分相关的列在索引列范围之内。 我把我的分析发送给了开发组,很快得到了反馈,他们需要进一步的分析,对我的建议还是认可的。 通过这个案例,我们发现,很多事情时候标准都是活的,不能以看到全表扫描就是性能瓶颈。需要具体问题具体对待,索引,索引列的添加也不能凭感觉,很多时候需要评估是否需要添加索引,添加的索引列是否合理。 添加的索引对现有的系统的影响范围,明白了这些,才能对这个问题的把握透彻了,把很多潜在的问题都尽量避免。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2015-01-31

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏IT技术精选文摘

MySQL索引设计概要

在关系型数据库中设计索引其实并不是复杂的事情,很多开发者都觉得设计索引能够提升数据库的性能,相关的知识一定非常复杂。 ? 然而这种想法是不正确的,索引其实并不是...

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

关于收缩数据文件的尝试(r5笔记第34天)

在数据库中对于数据文件都是提前规划,不够就加的情况,很少会留意到其实有些数据文件那么大,其实条件允许也是可以收缩收缩的。 这种情况在本地测试环境中尤为突出,本来...

3506
来自专栏有趣的Python和你

Python爬虫之Xpath学习问题解决用xpath方法爬取豆瓣图书top250分析总结

1494
来自专栏jouypub

MySQL查询语句优化

在项目中经常和MySQL数据库打交道,写过各种各样的SQL,也遇到过各种问题,针对遇到的各种场景,记录一些解决方案,主要是MySQL索引问题。

1031
来自专栏数据和云

解锁不可见索引新特性,处理ORA-01555故障

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

1225
来自专栏学习有记

聚集索引:SQL Server 进阶 Level 3

1533
来自专栏逸鹏说道

维护索引(3)——通过重建索引提高性能

前言: 重建一个索引只是在内部删除并重建索引,使得碎片消失、统计信息更新、物理顺序重新排列组织。它会压缩数据页,按照填充因子填充适当的数据。如果有需要,也会添加...

2574
来自专栏Hadoop实操

如何在Impala中实现拉链表

拉链表是针对数据仓库设计中表存储数据的方式而定义的,即是记录历史。记录一个事物从开始,一直到当前状态的所有变化的信息。传统数据仓库一般采用拉链的方式保留主数据(...

1K9
来自专栏软件测试经验与教训

测试工程师SQL面试题

测试人员工作在工作中会用到SQL来辅助测试,求职时也常常会在笔试环节遇到各种各样的sql设计题目,张老师整理了一些工作中常用的sql知识点,希望对大家有所帮助。

1.4K3
来自专栏idba

如何阅读死锁日志

一 前言 工欲善其事必先利其器,前面分析了很多死锁案例,并没有详细的介绍如何通过死锁日志来诊断死锁的成因。本文将介绍如何读懂死锁日志,尽可能的获取信息来辅助我...

1303

扫码关注云+社区

领取腾讯云代金券