差SQL引起CPU使用率100%的性能分析

今天主机同事告诉我数据库主机CPU使用率都到90%以上了。我登录主机一看，确实使用率挺高的，由于是windows系统就看到oracle进程高居首位。

首先，我先从等待事件入手，查了一下现在的等待事件，数据库的等待事件还是比较少的，在20个一下，主要有一下几个
jobq slave wait
latch: cache buffers chains
db file sequential read

针对jobq slave wait 等待事件，我看了一下job的数量
SQL> select count(*) from dba_jobs;
  COUNT(*)
----------
        30

有检查了一下job_queue_processes参数，当时这个参数设置为10，有点偏低。使用下面语句调整该参数到40
alter system set job_queue_processes=40 scope=both;
调整后，jobq slave wait等待的数量有所下降，但是不明显，说明CPU使用率过高同jobq slave wait等待事件关系不大。

为了准确定位问题，我抓取了一下ash报告，从报告中，我获取的两条SQL，如下：

SQL ID	Planhash	% Activity	Event	% Event	SQL Text
awvdpgr8rb4wp	2569724710	4.16	CPU + Wait for CPU	4.15	select tevaluate0_.ID as ID5_,...

Plan Hash	% Activity	# of Sampled SQL Versions	Example SQL 1	Example SQL TEXT 1	Example SQL 2	Example SQL TEXT 2
3498509795	72.31	9732	0004whx9n040v	select tuserstatu0_.ID as ID39...	gzzsftgvpq973	select tuserstatu0_.ID as ID39...

由于我对业务不是很了解，对SQL优化没有把握。所以抱着试试的态度进行了下面的优化过程。
先来看awvdpqr8rb4wp:
select tevaluate0_.ID as ID5_, tevaluate0_.BAR_ID as BAR2_5_, tevaluate0_.EVALUATE_MAN as EVALUATE3_5_, tevaluate0_.EVALUATE_RESULT as EVALUATE4_5_, tevaluate0_.EVALUATE_COMMENT as EVALUATE5_5_, tevaluate0_.TIME as TIME5_, tevaluate0_.ORIGIN as ORIGIN5_, tevaluate0_.EVALUATE_TYPE as EVALUATE8_5_, tevaluate0_.SCAN_ID as SCAN9_5_, tevaluate0_.OTHER_SCAN_ID as OTHER10_5_, tevaluate0_.SCAN_PAGE as SCAN11_5_, tevaluate0_.IS_FINISH as IS12_5_, tevaluate0_.OPERATOR as OPERATOR5_, tevaluate0_.IS_APPROVE as IS14_5_, tevaluate0_.IS_PROCESS as IS15_5_, tevaluate0_.CREATION_DATE as CREATION16_5_, tevaluate0_.LAST_UPDATE_DATE as LAST17_5_, tevaluate0_.CURRENT_USER_NAME as CURRENT18_5_, tevaluate0_.type_flag as type19_5_, tevaluate0_.EVALUATE_REASON as EVALUATE20_5_, tevaluate0_.IS_FILL as IS21_5_ 
from NMDX.T_EVALUATE tevaluate0_ 
where tevaluate0_.OPERATOR=2 
and tevaluate0_.IS_FINISH='0' 
and tevaluate0_.IS_APPROVE='1' 
and tevaluate0_.IS_PROCESS=2;
的执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 2569724710
--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |   118 |   170   (2)| 00:00:03 |
|*  1 |   TABLE ACCESS FULL| T_EVALUATE |     1 |   118 |   170   (2)| 00:00:03 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TEVALUATE0_"."OPERATOR"=2 AND
              "TEVALUATE0_"."IS_FINISH"='0' AND TO_NUMBER("TEVALUATE0_"."IS_PROCESS")=
              2 AND "TEVALUATE0_"."IS_APPROVE"='1')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        758  consistent gets
          0  physical reads
          0  redo size
       1349  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
这里走了全表扫描，感觉应该缺少索引，所以针对条件列进行了分析。
1）表现有的索引
TABLE_OWNER                    INDEX_NAME           INDEX_TYPE           STATUS                     NUM_ROWS COLUMN_NAME
------------------------------ -------------------- -------------------- ------------------------ ---------- ------------------------------
NMDX                           I_EVALUATE_BAR_ID    NORMAL               VALID                         42602 BAR_ID
NMDX                           PK_T_EVALUATE_ID     NORMAL               VALID                         42602 ID
查询的条件列不在其中。
2）查询计条件列的数据分布
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS LAST_ANALYZED
------------------------------ ------------ ---------- -------------------
OPERATOR                                139          0 2013-12-12 22:00:43
IS_APPROVE                                2          0 2013-12-12 22:00:43
IS_PROCESS                                2          0 2013-12-12 22:00:43
IS_FINISH                                 2          0 2013-12-12 22:00:43

表T_EVALUATE 数据量大概在42602行，OPERATOR列有139个不同的值，可以考虑在OPERATOR列建索引。
create index NMDX.i_evaluate_operator on NMDX.T_EVALUATE(OPERATOR);

索引建完以后，我们在来看看执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 851556358
---------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                     |     1 |   118 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T_EVALUATE          |     1 |   118 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_EVALUATE_OPERATOR |     5 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TEVALUATE0_"."IS_FINISH"='0' AND TO_NUMBER("TEVALUATE0_"."IS_PROCESS")=2
              AND "TEVALUATE0_"."IS_APPROVE"='1')
   2 - access("TEVALUATE0_"."OPERATOR"=2)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1349  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed
SQL查询效率应该明显提高。

接下来看看SQL：
select tuserstatu0_.ID as ID39_, tuserstatu0_.USER_NAME as USER2_39_, tuserstatu0_.TICKET as TICKET39_, tuserstatu0_.CREATE_DATE as CREATE4_39_, tuserstatu0_.USER_ID as USER5_39_ 
from NMDX.T_USER_STATUS tuserstatu0_ 
where tuserstatu0_.TICKET='21A102E1806AC127C9D5B5F5DEE7752D' order by tuserstatu0_.CREATE_DATE desc;
还是先看一下执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 3498509795
------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |    61 |  1338   (2)| 00:00:17 |
|   1 |  SORT ORDER BY     |               |     1 |    61 |  1338   (2)| 00:00:17 |
|*  2 |   TABLE ACCESS FULL| T_USER_STATUS |     1 |    61 |  1337   (1)| 00:00:17 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("TUSERSTATU0_"."TICKET"='21A102E1806AC127C9D5B5F5DEE7752D')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       6049  consistent gets
          0  physical reads
          0  redo size
        462  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed
同样是走全表扫描了。

1）现有表索引
TABLE_OWNER                    INDEX_NAME           INDEX_TYPE           STATUS                     NUM_ROWS COLUMN_NAME
------------------------------ -------------------- -------------------- ------------------------ ---------- ------------------------------
NMDX                           PK_USER_STATUS_ID    NORMAL               VALID                        624240 ID
条件列不在索引范围内

2）查询列数据分布
COLUMN_NAME                    NUM_DISTINCT  NUM_NULLS LAST_ANALYZED
------------------------------ ------------ ---------- -------------------
TICKET                               618585          0 2013-12-11 22:01:50

表T_USER_STATUS大概有624240行，而列TICKET有618585个不同记录，很适合在其上建索引。
create index NMDX.i_user_status_ticket on NMDX.T_USER_STATUS(TICKET);

索引建完以后，在来看看其执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 3850045517
-----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                      |     1 |    61 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |                      |     1 |    61 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_USER_STATUS        |     1 |    61 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | I_USER_STATUS_TICKET |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   3 - access("TUSERSTATU0_"."TICKET"='21A102E1806AC127C9D5B5F5DEE7752D')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        462  bytes sent via SQL*Net to client
        437  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

从执行计划可以看出，SQL性能明显提高。

这些调整完后，CPU使用率从90%多下降到30%，优化小高还是比较明显的。

上面涉及的SQL还是比较简单的，如果复杂的SQL，还是需要业务开发来配合的，从这次优化，明显感觉到作为一个DBA，要熟悉业务系统，这样对于性能问题定位处理有很大的帮助。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11590946/viewspace-1063236/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11590946/viewspace-1063236/