一条全表扫描sql语句的分析 (r4笔记第32天)
一条全表扫描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条件部分相关的列在索引列范围之内。 我把我的分析发送给了开发组，很快得到了反馈，他们需要进一步的分析，对我的建议还是认可的。 通过这个案例，我们发现，很多事情时候标准都是活的，不能以看到全表扫描就是性能瓶颈。需要具体问题具体对待，索引，索引列的添加也不能凭感觉，很多时候需要评估是否需要添加索引，添加的索引列是否合理。 添加的索引对现有的系统的影响范围，明白了这些，才能对这个问题的把握透彻了，把很多潜在的问题都尽量避免。