Oracle中捕获问题SQL解决CPU过渡消耗.docx
《Oracle中捕获问题SQL解决CPU过渡消耗.docx》由会员分享，可在线阅读，更多相关《Oracle中捕获问题SQL解决CPU过渡消耗.docx（13页珍藏版）》请在冰豆网上搜索。
Oracle中捕获问题SQL解决CPU过渡消耗
本文通过实际业务系统中调整的一个案例，试图给出一个常见CPU消耗问题的一个诊断方法.大多数情况下，系统的性能问题都是由不良SQL代码引起的，那么作为DBA，怎样发现和解决这些SQL问题就显得尤为重要.
本案例平台为UNIX，所以不可避免的应用了一些Unix下常用的工具.如vmstat,top等.
本文适宜读者范围:
中高级.
系统环境:
OS:
Solaris8
Oracle:
8.1.7.4
问题描述:
开发人员报告系统运行缓慢,已经影响业务系统正常使用.请求协助诊断.
1.登陆数据库主机
使用vmstat检查，发现CPU资源已经耗尽，大量任务位于运行队列:
bash-2.03$vmstat3
procsmemorypagediskfaultscpu
rbwswapfreeremfpipofrdesrs6s9s1sdinsycsussyid
0005504232146411200000000110429496719600-84-5-145
1310053680721518360566910220001003011791827959730
1310053773281522464817190220001002766801925779640
1300053824001524776676820000000003570853433169730
134005373616152051212710780220001003838958436239640
13600536939215184961079240550000002920857326399730
1320053649121516224635780000000003358794431199730
1290053586481511712189123600000000033661036531359550
129005354528151130412011940000000403235886429119640
1280053468481507704998230000000303189904830749640
1250053412481504704808430220006103563951433149550
1330053327441501112797980000000103218880529029730
12900532538414973681076430220001403184829728799640
1260053631441514320817530000000002533740921649730
1360053556241510512169566786000000103002860028109640
13010535144815029362675801821000000003126781229009640
12900534725614995681559132220000102225807619419820
116005338192149540017711620000000101947778116399730
2.使用Top命令
观察进程CPU耗用,发现没有明显过高CPU使用的进程
$top
lastpid:
28313;loadaverages:
99.90,117.54,125.7123:
28:
38
296processes:
186sleeping,99running,2zombie,9oncpu
CPUstates:
0.0%idle,96.5%user,3.5%kernel,0.0%iowait,0.0%swap
Memory:
4096Mreal,1404Mfree,2185Mswapinuse,5114Mswapfree
PIDUSERNAMETHRPRINICESIZERESSTATETIMECPUCOMMAND
27082oracle8i13301328M1309Mrun0:
171.29%oracle
26719oracle8i15501327M1306Msleep0:
291.11%oracle
28103oracle8i13501327M1304Mrun0:
061.10%oracle
28161oracle8i12501327M1305Mrun0:
041.10%oracle
26199oracle8i14501328M1309Mrun0:
421.10%oracle
26892oracle8i13301328M1310Mrun0:
241.09%oracle
27805oracle8i14501327M1306Mcpu/10:
101.04%oracle
23800oracle8i12301327M1306Mrun1:
281.03%oracle
25197oracle8i13401328M1309Mrun0:
571.03%oracle
21593oracle8i13301327M1306Mrun2:
121.01%oracle
27616oracle8i14501329M1311Mrun0:
141.01%oracle
27821oracle8i14301327M1306Mrun0:
101.00%oracle
26517oracle8i13301328M1309Mrun0:
330.97%oracle
25785oracle8i14401328M1309Mrun0:
460.96%oracle
26241oracle8i14501327M1306Mrun0:
420.96%oracle
3.检查进程数量
bash-2.03$ps-ef|grepora|wc-l
258
bash-2.03$ps-ef|grepora|wc-l
275
bash-2.03$ps-ef|grepora|wc-l
274
bash-2.03$ps-ef|grepora|wc-l
278
bash-2.03$ps-ef|grepora|wc-l
277
bash-2.03$ps-ef|grepora|wc-l
366
发现系统存在大量Oracle进程,大约在300左右，大量进程消耗了几乎所有CPU资源,而正常情况下Oracle连接数应该在100左右.
4.检查数据库
查询v$session_wait获取各进程等待事件
SQL>selectsid,event,p1,p1textfromv$session_wait;
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
124latchfree1.6144E+10address
1pmontimer300duration
2rdbmsipcmessage300timeout
3rdbmsipcmessage300timeout
11rdbmsipcmessage30000timeout
6rdbmsipcmessage180000timeout
4rdbmsipcmessage300timeout
134rdbmsipcmessage6000timeout
147rdbmsipcmessage6000timeout
275rdbmsipcmessage17995timeout
274rdbmsipcmessage6000timeout
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
118rdbmsipcmessage6000timeout
7bufferbusywaits17file#
56bufferbusywaits17file#
161bufferbusywaits17file#
195bufferbusywaits17file#
311bufferbusywaits17file#
314bufferbusywaits17file#
205bufferbusywaits17file#
269bufferbusywaits17file#
200bufferbusywaits17file#
164bufferbusywaits17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
140bufferbusywaits17file#
66bufferbusywaits17file#
10dbfilesequentialread17file#
18dbfilesequentialread17file#
54dbfilesequentialread17file#
49dbfilesequentialread17file#
48dbfilesequentialread17file#
46dbfilesequentialread17file#
45dbfilesequentialread17file#
35dbfilesequentialread17file#
30dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
29dbfilesequentialread17file#
22dbfilesequentialread17file#
178dbfilesequentialread17file#
175dbfilesequentialread17file#
171dbfilesequentialread17file#
123dbfilesequentialread17file#
121dbfilesequentialread17file#
120dbfilesequentialread17file#
117dbfilesequentialread17file#
114dbfilesequentialread17file#
113dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
111dbfilesequentialread17file#
107dbfilesequentialread17file#
80dbfilesequentialread17file#
222dbfilesequentialread17file#
218dbfilesequentialread17file#
216dbfilesequentialread17file#
213dbfilesequentialread17file#
199dbfilesequentialread17file#
198dbfilesequentialread17file#
194dbfilesequentialread17file#
192dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
188dbfilesequentialread17file#
249dbfilesequentialread17file#
242dbfilesequentialread17file#
239dbfilesequentialread17file#
236dbfilesequentialread17file#
235dbfilesequentialread17file#
234dbfilesequentialread17file#
233dbfilesequentialread17file#
230dbfilesequentialread17file#
227dbfilesequentialread17file#
336dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
333dbfilesequentialread17file#
331dbfilesequentialread17file#
329dbfilesequentialread17file#
327dbfilesequentialread17file#
325dbfilesequentialread17file#
324dbfilesequentialread17file#
320dbfilesequentialread17file#
318dbfilesequentialread17file#
317dbfilesequentialread17file#
316dbfilesequentialread17file#
313dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
305dbfilesequentialread17file#
303dbfilesequentialread17file#
301dbfilesequentialread17file#
293dbfilesequentialread17file#
290dbfilesequentialread17file#
288dbfilesequentialread17file#
287dbfilesequentialread17file#
273dbfilesequentialread17file#
271dbfilesequentialread17file#
257dbfilesequentialread17file#
256dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
254dbfilesequentialread17file#
252dbfilesequentialread17file#
159dbfilesequentialread17file#
153dbfilesequentialread17file#
146dbfilesequentialread17file#
142dbfilesequentialread17file#
135dbfilesequentialread17file#
133dbfilesequentialread17file#
132dbfilesequentialread17file#
126dbfilesequentialread17file#
79dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
77dbfilesequentialread17file#
72dbfilesequentialread17file#
70dbfilesequentialread17file#
69dbfilesequentialread17file#
67dbfilesequentialread17file#
63dbfilesequentialread17file#
55dbfilesequentialread17file#
102dbfilesequentialread17file#
96dbfilesequentialread17file#
95dbfilesequentialread17file#
91dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
81dbfilesequentialread17file#
15dbfilesequentialread17file#
19dbfilescatteredread17file#
50dbfilescatteredread17file#
285dbfilescatteredread17file#
279dbfilescatteredread17file#
255dbfilescatteredread17file#
243dbfilescatteredread17file#
196dbfilescatteredread17file#
187dbfilescatteredread17file#
170dbfilescatteredread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
162dbfilescatteredread17file#
138dbfilescatteredread17file#
110dbfilescatteredread17file#
108dbfilescatteredread17file#
92dbfilescatteredread17file#
330dbfile
升级会员 