本文共 5590 字,大约阅读时间需要 18 分钟。
SID SERIAL# USERNAME OSUSER MACHINE PROCESS TERMINAL TYPE LOGIN_TIME
5270 687 APP_USER_TEST WEB_XXX_xxx 1234 USER 2015-09-29 01:23:43 .SQL_ID SQL_TEXT
------------------------------ ------------------------------------------------------------ gx34ap7fbwuvx select count(*) from user_test35 where old_id=:1 原来这是一个简单的查询语句,确有大量的CPU消耗,相必是走了全表扫描了。 查看执行计划,发现还真是。Plan hash value: 1393919968
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1543 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | TABLE ACCESS FULL| USER_TEST | 1 | 7 | 1543 (1)| 00:00:19 | -------------------------------------------------------------------------------------- 这个语句USER_TEST35其实是指向另外一个schema下的USER_TEST表。 查看索引情况,发现确实没有索引列在OLD_ID上面。至于这个部分,我们现在可以得到一个初步的结论就是表USER_TEST中缺少了列OLD_ID相关的索引,结果导致了全表扫描。 问题到此似乎也是合乎情理了,不过继续抓取了另外几个问题sql,发现表名都是一致的,但是属于不同的用户,这个时候因为是备库,索性就抓取了一个8小时的ash报告。 这个时候得到了一个相关的列表。SQL ID Planhash of Executions % Activity
----------------------- -------------------- -------------------- -------------- Event % Event Top Row Source % RwSrc ------------------------------ ------- --------------------------------- ------- 27bt3qxbjzmzn 1393919968 12153 8.47 CPU + Wait for CPU 8.45 TABLE ACCESS - FULL 8.44 select count(*) from user_test02 where old_id=:13qcg54j5kx190 1393919968 12110 8.44
CPU + Wait for CPU 8.41 TABLE ACCESS - FULL 8.41 select count(*) from user_test24 where old_id=:1d59kmgha5azmv 1393919968 11767 8.21
CPU + Wait for CPU 8.18 TABLE ACCESS - FULL 8.18 select count(*) from user_test15 where old_id=:17ug3juzr5ta3z 1393919968 11750 8.20
CPU + Wait for CPU 8.17 TABLE ACCESS - FULL 8.16 select count(*) from user_test13 where old_id=:176jg6df5tvqgp 1393919968 11726 8.18
CPU + Wait for CPU 8.15 TABLE ACCESS - FULL 8.14 select count(*) from user_test22 where old_id=:1 这些操作都是在做一个查询数据条数的操作,其实关联的表名都是同一个。但是归属于不同的schema下。 那么到底有多少呢,是不是碰到一个问题就修复一个,还是抓住本质,根本上解决呢。 其实这些用户基本都是在做一个分库分表的操作,根据不同的规则把数据分布到不同的schema上去,但是表名是同一个。 按照目前的规则,相关的用户就有10多个了,而不是ash报告中抓取到的那几个了。 而查看这些用户下的表数据情况,也基本是均匀的,但是数据量还不够大,百万级。 所以我们可以在这些相关的用户上对于问题表创建索引基于列OLD_ID, create index AdB00.IDX_USER_TEST_OLDID ON ADB00.USER_TEST(OLD_ID); create index AdB02.IDX_USER_TEST_OLDID ON ADB02.USER_TEST(OLD_ID); .... 但是在操作之前还是看看是否有其它的相关sql,执行频率怎么样。如果要创建,主库是否有相关的dml操作,主库的负载等等,是否可以online操作,是否需要开并行等等。 所以简单的评估之后,认为还是在主库是可以操作的,就开启了并行,开始创建索引。 创建索引的时间倒不长,然后在备库中去抓取sql的执行计划是否改变。Plan hash value: 1638289453
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| IDX_USER_TEST_OLDID | 1 | 7 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- 可以看到效果还是很明显的。 通过sar可以看到CPU利用率直线下降。02:00:01 PM all 70.51 0.00 1.95 0.20 0.00 27.34
02:10:01 PM all 70.40 0.00 2.08 0.25 0.00 27.2702:20:01 PM all 5.06 0.00 0.62 0.39 0.00 93.92
02:20:01 PM all 5.06 0.00 0.62 0.39 0.00 93.92 问题修复之后,可以看到CPU使用率马上下降了,资源已经提前解放了出来。 相信之后的几天里,也不会有很明显的CPU使用异常了。 通过这个案例可以看到一个很小的报警还是可能蕴含着一些值得去处理的问题,如果发现了问题,还是需要来看看问题的分析是否全面,从手头拿到的数据来看,是否已经掌握了完整的信息。 如果把这个问题和业务结合起来,还是发现问题还是有一些规律,尽管一些问题sql并没有在报告中体现出来。当然问题需要修复,什么时候修复,怎么修复,怎么来评估,还是需要自己好好琢磨一下。 ##附上问题后期的持续跟进,可以看到效果确实是杠杠的。来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-1811961/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23718752/viewspace-1811961/