差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
首先，我先從等待事件入手，查了一下現在的等待事件，資料庫的等待事件還是比較少的，在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最佳化沒有把握。所以抱著試試的態度進行了下面的最佳化過程。
先來看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，要熟悉業務系統，這樣對於效能問題定位處理有很大的幫助。
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 |
|---|---|---|---|---|---|
| 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 | select tuserstatu0_.ID as ID39... | 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/29107230/viewspace-1064178/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle效能優化-資料庫CPU使用率100%Oracle優化資料庫
- CPU 100%負載的效能優化分析負載優化
- 效能分析（2）- 應用程式 CPU 使用率過高案例
- Oracle優化案例-緊急處理一條sql引起cpu使用率99%的問題（十六）Oracle優化SQL
- 效能分析（5）- 軟中斷導致 CPU 使用率過高的案例
- 關於CPU使用率高的awr分析
- sql語句引起的CPU佔用國高SQL
- CPU效能分析
- weblogic程式高CPU使用率分析WebC程式
- 恆訊科技分析：如何解決SQL Server CPU使用率過高的問題？SQLServer
- cpu使用率低負載高，原因分析負載
- WindowsXP系統CPU使用率100%解決辦法Windows
- 效能分析（3）- 短時程式導致使用者 CPU 使用率過高案例
- CPU效能分析工具原理
- 故障分析 | show processlist 引起的效能問題
- Google Chrome發現新Bug CPU使用率飆升至100%GoChrome
- .netcore利用perf分析高cpu使用率NetCore
- 多執行緒引起的效能問題分析執行緒
- 效能分析SQLSQL
- 查出引起等待的sql，並kill該引起等待的sqlSQL
- SQL執行計劃異常引起的效能問題SQL
- SQL執行計劃異常 引起的效能問題SQL
- 【閱讀筆記】jvm效能工具-jstat解決100%CPU佔用率分析筆記JVMJS
- 我是如何解決SQL Server CPU佔100%的SQLServer
- CPU持續100%分析並解決
- mysql例項cpu超過100%分析MySql
- 效能測試必備知識（7）- 深入理解“CPU 使用率”
- Win10怎麼看cpu使用率？Win10檢視CPU使用率的方法Win10
- oracle 12.2.0.1 crs call completion cpu使用率100% library cache lockOracle
- 記一次線上事故，redis 的keys問題，cpu引起的效能問題Redis
- 效能分析（4）- iowait 使用率過高案例AI
- MySQL SQL效能分析MySql
- 案例：某個應用的 CPU 使用率居然達到 100%，我該怎麼辦？
- [20170324]cpu 100%，latch free等待分析
- 分析WordPress中esc_sql函式引起的注入危害SQL函式
- PG資料庫伺服器的CPU使用率突然升高該如何分析資料庫伺服器
- 效能分析之CPU分析-從CPU呼叫高到具體程式碼行（JAVA）Java
- CPU使用率低負載高負載