Oracle中怎樣用SQL解決CPU消耗過度

本文就實際業務中提出一個診斷CPU消耗問題的方法.通過SQL語句解決。

本案例平台為UNIX，所以不可避免的應用了一些Unix下常用的工具.如vmstat,top等.
本文適宜讀者范圍:中高級.

系統環境:
OS: Solaris8
Oracle: 8.1.7.4

問題描述
開發人員報告系統運行緩慢,已經影響業務系統正常使用及CPU消耗過度.請求協助診斷.

1.登陸數據庫主機

使用vmstat檢查，發現CPU資源已經耗盡，大量任務位於運行隊列:

bash-2.03$ vmstat 3   
procs memory page disk faults cpu   
r b w swap free re mf pi po fr de sr s6 s9 s1 sd in sy cs us sy id   
0 0 0 5504232 1464112 0 0 0 0 0 0 0 0 1 1 0 4294967196 0 0 -84 -5 -145   
131 0 0 5368072 1518360 56 691 0 2 2 0 0 0 1 0 0 3011 7918 2795 97 3 0   
131 0 0 5377328 1522464 81 719 0 2 2 0 0 0 1 0 0 2766 8019 2577 96 4 0   
130 0 0 5382400 1524776 67 682 0 0 0 0 0 0 0 0 0 3570 8534 3316 97 3 0   
134 0 0 5373616 1520512 127 1078 0 2 2 0 0 0 1 0 0 3838 9584 3623 96 4 0   
136 0 0 5369392 1518496 107 924 0 5 5 0 0 0 0 0 0 2920 8573 2639 97 3 0   
132 0 0 5364912 1516224 63 578 0 0 0 0 0 0 0 0 0 3358 7944 3119 97 3 0   
129 0 0 5358648 1511712 189 1236 0 0 0 0 0 0 0 0 0 3366 10365 3135 95 5 0   
129 0 0 5354528 1511304 120 1194 0 0 0 0 0 0 0 4 0 3235 8864 2911 96 4 0   
128 0 0 5346848 1507704 99 823 0 0 0 0 0 0 0 3 0 3189 9048 3074 96 4 0   
125 0 0 5341248 1504704 80 843 0 2 2 0 0 0 6 1 0 3563 9514 3314 95 5 0   
133 0 0 5332744 1501112 79 798 0 0 0 0 0 0 0 1 0 3218 8805 2902 97 3 0   
129 0 0 5325384 1497368 107 643 0 2 2 0 0 0 1 4 0 3184 8297 2879 96 4 0   
126 0 0 5363144 1514320 81 753 0 0 0 0 0 0 0 0 0 2533 7409 2164 97 3 0   
136 0 0 5355624 1510512 169 566 786 0 0 0 0 0 0 1 0 3002 8600 2810 96 4 0   
130 1 0 5351448 1502936 267 580 1821 0 0 0 0 0 0 0 0 3126 7812 2900 96 4 0   
129 0 0 5347256 1499568 155 913 2 2 2 0 0 0 0 1 0 2225 8076 1941 98 2 0   
116 0 0 5338192 1495400 177 1162 0 0 0 0 0 0 0 1 0 1947 7781 1639 97 3 0   

2.使用Top命令

觀察進程CPU消耗,發現沒有明顯過高CPU使用的進程

$ top   
last pid: 28313; load averages: 99.90, 117.54, 125.71 23:28:38   
296 processes: 186 sleeping, 99 running, 2 zombIE, 9 on cpu   
CPU states: 0.0% idle, 96.5% user, 3.5% kernel, 0.0% iowait, 0.0% swap   
Memory: 4096M real, 1404M free, 2185M swap in use, 5114M swap free   
PID USERNAME THR PRI NICE SIZE RES STATE TIME CPU COMMAND   
27082 oracle8i 1 33 0 1328M 1309M run 0:17 1.29% Oracle   
26719 oracle8i 1 55 0 1327M 1306M sleep 0:29 1.11% Oracle   
28103 oracle8i 1 35 0 1327M 1304M run 0:06 1.10% Oracle   
28161 oracle8i 1 25 0 1327M 1305M run 0:04 1.10% Oracle   
26199 oracle8i 1 45 0 1328M 1309M run 0:42 1.10% Oracle   
26892 oracle8i 1 33 0 1328M 1310M run 0:24 1.09% Oracle   
27805 oracle8i 1 45 0 1327M 1306M cpu/1 0:10 1.04% Oracle   
23800 oracle8i 1 23 0 1327M 1306M run 1:28 1.03% Oracle   
25197 oracle8i 1 34 0 1328M 1309M run 0:57 1.03% Oracle   
21593 oracle8i 1 33 0 1327M 1306M run 2:12 1.01% Oracle   
27616 oracle8i 1 45 0 1329M 1311M run 0:14 1.01% Oracle   
27821 oracle8i 1 43 0 1327M 1306M run 0:10 1.00% Oracle   
26517 oracle8i 1 33 0 1328M 1309M run 0:33 0.97% Oracle   
25785 oracle8i 1 44 0 1328M 1309M run 0:46 0.96% Oracle   
26241 oracle8i 1 45 0 1327M 1306M run 0:42 0.96% Oracle  

3.檢查進程數量

bash-2.03$ ps -ef|grep ora|wc -l   
258   
bash-2.03$ ps -ef|grep ora|wc -l   
275   
bash-2.03$ ps -ef|grep ora|wc -l   
274   
bash-2.03$ ps -ef|grep ora|wc -l   
278   
bash-2.03$ ps -ef|grep ora|wc -l   
277   
bash-2.03$ ps -ef|grep ora|wc -l   
366   

發現系統存在大量Oracle進程,大約在300左右，大量進程幾乎讓CPU消耗所有資源,而正常情況下Oracle連接數應該在100左右.

4.檢查數據庫

查詢v$session_wait獲取各進程等待事件

SQL＞ select sid,event,p1,p1text from v$session_wait;   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------  
124 latch free 1.6144E+10 address   
1 pmon timer 300 duration   
2 rdbms ipc message 300 timeout   
3 rdbms ipc message 300 timeout   
11 rdbms ipc message 30000 timeout   
6 rdbms ipc message 180000 timeout   
4 rdbms ipc message 300 timeout   
134 rdbms ipc message 6000 timeout   
147 rdbms ipc message 6000 timeout   
275 rdbms ipc message 17995 timeout   
274 rdbms ipc message 6000 timeout   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------  
118 rdbms ipc message 6000 timeout   
7 buffer busy waits 17 file#   
56 buffer busy waits 17 file#   
161 buffer busy waits 17 file#   
195 buffer busy waits 17 file#   
311 buffer busy waits 17 file#   
314 buffer busy waits 17 file#   
205 buffer busy waits 17 file#   
269 buffer busy waits 17 file#   
200 buffer busy waits 17 file#   
164 buffer busy waits 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
140 buffer busy waits 17 file#   
66 buffer busy waits 17 file#   
10 db file sequential read 17 file#   
18 db file sequential read 17 file#   
54 db file sequential read 17 file#   
49 db file sequential read 17 file#   
48 db file sequential read 17 file#   
46 db file sequential read 17 file#   
45 db file sequential read 17 file#   
35 db file sequential read 17 file#   
30 db file sequential read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
29 db file sequential read 17 file#   
22 db file sequential read 17 file#   
178 db file sequential read 17 file#   
175 db file sequential read 17 file#   
171 db file sequential read 17 file#   
123 db file sequential read 17 file#   
121 db file sequential read 17 file#   
120 db file sequential read 17 file#   
117 db file sequential read 17 file#   
114 db file sequential read 17 file#   
113 db file sequential read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
111 db file sequential read 17 file#   
107 db file sequential read 17 file#   
80 db file sequential read 17 file#   
222 db file sequential read 17 file#   
218 db file sequential read 17 file#   
216 db file sequential read 17 file#   
213 db file sequential read 17 file#   
199 db file sequential read 17 file#   
198 db file sequential read 17 file#   
194 db file sequential read 17 file#   
192 db file sequential read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
188 db file sequential read 17 file#   
249 db file sequential read 17 file#   
 
242 db file sequential read 17 file#   
239 db file sequential read 17 file#   
236 db file sequential read 17 file#   
235 db file sequential read 17 file#   
234 db file sequential read 17 file#   
233 db file sequential read 17 file#   
230 db file sequential read 17 file#   
227 db file sequential read 17 file#   
336 db file sequential read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
333 db file sequential read 17 file#   
331 db file sequential read 17 file#   
329 db file sequential read 17 file#   
327 db file sequential read 17 file#   
325 db file sequential read 17 file#   
324 db file sequential read 17 file#   
320 db file sequential read 17 file#   
318 db file sequential read 17 file#   
317 db file sequential read 17 file#   
316 db file sequential read 17 file#   
313 db file sequential read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
305 db file sequential read 17 file#   
303 db file sequential read 17 file#   
301 db file sequential read 17 file#   
293 db file sequential read 17 file#   
290 db file sequential read 17 file#   
288 db file sequential read 17 file#   
287 db file sequential read 17 file#   
273 db file sequential read 17 file#   
271 db file sequential read 17 file#   
257 db file sequential read 17 file#   
256 db file sequential read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
254 db file sequential read 17 file#   
252 db file sequential read 17 file#   
159 db file sequential read 17 file#   
153 db file sequential read 17 file#   
146 db file sequential read 17 file#   
142 db file sequential read 17 file#   
135 db file sequential read 17 file#   
133 db file sequential read 17 file#   
132 db file sequential read 17 file#   
126 db file sequential read 17 file#   
79 db file sequential read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
77 db file sequential read 17 file#   
72 db file sequential read 17 file#   
70 db file sequential read 17 file#   
69 db file sequential read 17 file#   
67 db file sequential read 17 file#   
63 db file sequential read 17 file#   
55 db file sequential read 17 file#   
102 db file sequential read 17 file#   
96 db file sequential read 17 file#   
95 db file sequential read 17 file#   
91 db file sequential read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
81 db file sequential read 17 file#   
15 db file sequential read 17 file#   
19 db file scattered read 17 file#   
50 db file scattered read 17 file#   
285 db file scattered read 17 file#   
279 db file scattered read 17 file#   
255 db file scattered read 17 file#   
243 db file scattered read 17 file#   
196 db file scattered read 17 file#   
187 db file scattered read 17 file#   
170 db file scattered read 17 file#   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
162 db file scattered read 17 file#   
138 db file scattered read 17 file#   
110 db file scattered read 17 file#   
108 db file scattered read 17 file#   
92 db file scattered read 17 file#   
330 db file scattered read 17 file#   
 
310 db file scattered read 17 file#   
302 db file scattered read 17 file#   
299 db file scattered read 17 file#   
89 db file scattered read 17 file#   
5 smon timer 300 sleep time   
SID EVENT P1 P1TEXT   
---------- ------------------------------ ----------   
20 SQL*Net message to clIEnt 1952673792 driver id   
103 SQL*Net message to clIEnt 1650815232 driver id   
....   
148 SQL*Net more data from clIEnt 1952673792 driver id   
291 SQL*Net more data from clIEnt 1952673792 driver id   
244 rows selected.   
 

發現存在大量db file scattered read及db file sequential read等待.顯然全表掃描等操作成為系統最嚴重的性能影響因素。

到此Oracle中怎樣用SQL解決CPU消耗過度講述完畢，要想了解的更多相關問題的解決方案，請留意51cto.com站上的相關論壇.