Oracle中捕获问题SQL解决CPU过渡消耗文档格式.docx

Oracle中捕获问题SQL解决CPU过渡消耗文档格式.docx

《Oracle中捕获问题SQL解决CPU过渡消耗文档格式.docx》由会员分享，可在线阅读，更多相关《Oracle中捕获问题SQL解决CPU过渡消耗文档格式.docx（13页珍藏版）》请在冰豆网上搜索。

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

275

274

278

277

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

118rdbmsipcmessage6000timeout

7bufferbusywaits17file#

56bufferbusywaits17file#

161bufferbusywaits17file#

195bufferbusywaits17file#

311bufferbusywaits17file#

314bufferbusywaits17file#

205bufferbusywaits17file#

269bufferbusywaits17file#

200bufferbusywaits17file#

164bufferbusywaits17file#

140bufferbusywaits17file#

66bufferbusywaits17file#

10dbfilesequentialread17file#

18dbfilesequentialread17file#

54dbfilesequentialread17file#

49dbfilesequentialread17file#

48dbfilesequentialread17file#

46dbfilesequentialread17file#

45dbfilesequentialread17file#

35dbfilesequentialread17file#

30dbfilesequentialread17file#

29dbfilesequentialread17file#

22dbfilesequentialread17file#

178dbfilesequentialread17file#

175dbfilesequentialread17file#

171dbfilesequentialread17file#

123dbfilesequentialread17file#

121dbfilesequentialread17file#

120dbfilesequentialread17file#

117dbfilesequentialread17file#

114dbfilesequentialread17file#

113dbfilesequentialread17file#

111dbfilesequentialread17file#

107dbfilesequentialread17file#

80dbfilesequentialread17file#

222dbfilesequentialread17file#

218dbfilesequentialread17file#

216dbfilesequentialread17file#

213dbfilesequentialread17file#

199dbfilesequentialread17file#

198dbfilesequentialread17file#

194dbfilesequentialread17file#

192dbfilesequentialread17file#

188dbfilesequentialread17file#

249dbfilesequentialread17file#

242dbfilesequentialread17file#

239dbfilesequentialread17file#

236dbfilesequentialread17file#

235dbfilesequentialread17file#

234dbfilesequentialread17file#

233dbfilesequentialread17file#

230dbfilesequentialread17file#

227dbfilesequentialread17file#

336dbfilesequentialread17file#

333dbfilesequentialread17file#

331dbfilesequentialread17file#

329dbfilesequentialread17file#

327dbfilesequentialread17file#

325dbfilesequentialread17file#

324dbfilesequentialread17file#

320dbfilesequentialread17file#

318dbfilesequentialread17file#

317dbfilesequentialread17file#

316dbfilesequentialread17file#

313dbfilesequentialread17file#

305dbfilesequentialread17file#

303dbfilesequentialread17file#

301dbfilesequentialread17file#

293dbfilesequentialread17file#

290dbfilesequentialread17file#

288dbfilesequentialread17file#

287dbfilesequentialread17file#

273dbfilesequentialread17file#

271dbfilesequentialread17file#

257dbfilesequentialread17file#

256dbfilesequentialread17file#

254dbfilesequentialread17file#

252dbfilesequentialread17file#

159dbfilesequentialread17file#

153dbfilesequentialread17file#

146dbfilesequentialread17file#

142dbfilesequentialread17file#

135dbfilesequentialread17file#

133dbfilesequentialread17file#

132dbfilesequentialread17file#

126dbfilesequentialread17file#

79dbfilesequentialread17file#

77dbfilesequentialread17file#

72dbfilesequentialread17file#

70dbfilesequentialread17file#

69dbfilesequentialread17file#

67dbfilesequentialread17file#

63dbfilesequentialread17file#

55dbfilesequentialread17file#

102dbfilesequentialread17file#

96dbfilesequentialread17file#

95dbfilesequentialread17file#

91dbfilesequentialread17file#

81dbfilesequentialread17file#

15dbfilesequentialread17file#

19dbfilescatteredread17file#

50dbfilescatteredread17file#

285dbfilescatteredread17file#

279dbfilescatteredread17file#

255dbfilescatteredread17file#

243dbfilescatteredread17file#

196dbfilescatteredread17file#

187dbfilescatteredread17file#

170dbfilescatteredread17file#

162dbfilescatteredread17file#

138dbfilescatteredread17file#

110dbfilescatteredread17file#

108dbfilescatteredread17file#

92dbfilescatteredread17file#

330dbfile