您现在的位置是：主页 > news > 天津智能网站建设/网址seo查询
天津智能网站建设/网址seo查询
admin2025/5/16 12:06:22【news】
简介天津智能网站建设,网址seo查询,互联网营销师报考,北京网站建设团队最近一周服务器再一次发现了cpu100%的问题，看来上次对sql语句的优化没有彻底根治这个问题。话不多说，继续开始调查这个问题。首先查看了当天发生问题的一小时内的AWR报告：SQL ordered by CPU TimeResources reported for PL/SQL code include…
最近一周服务器再一次发现了cpu100%的问题，看来上次对sql语句的优化没有彻底根治这个问题。话不多说，继续开始调查这个问题。
首先查看了当天发生问题的一小时内的AWR报告：
SQL ordered by CPU Time
Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
%Total – CPU Time as a percentage of Total DB CPU
%CPU – CPU Time as a percentage of Elapsed Time
%IO – User I/O Time as a percentage of Elapsed Time
Captured SQL account for 1.9% of Total CPU Time (s): 9,545
Captured PL/SQL account for 0.2% of Total CPU Time (s): 9,545
CPU Time (s)
Executions
CPU per Exec (s)
%Total
Elapsed Time (s)
%CPU
%IO
SQL Id
SQL Module
SQL Text
67.58
0.71
80.38
84.08
insert into sys.aud$( sessioni…
58.84
23
2.56
0.62
180.38
32.62
0.00
Business.exe
SELECT * FROM( SELECT * FROM( …
11.61
9,418
0.00
0.12
12.17
95.34
0.01
OST_StockInHelper.exe
SELECT “GroupBy1”.”A1″ AS “C1″…
9.08
21
0.43
0.10
22.83
39.76
5.15
DECLARE job BINARY_INTEGER := …
4.76
2
2.38
0.05
10.34
46.04
54.09
Oracle Enterprise Manager.Metric Engine
SELECT TO_CHAR(current_timesta…
4.76
15,415
0.00
0.05
90.67
5.25
0.00
Business.exe
SELECT NVL(SUM(RESB_BDMNG), 0)…
3.85
15,415
0.00
0.04
17.63
21.85
0.00
Business.exe
SELECT NVL(SUM(QTY), 0) FROM K…
3.48
18
0.19
0.04
4.47
77.76
21.34
??????????????????.exe
select SCDTAJ01, SCDTAJ02, SCD…
3.46
13
0.27
0.04
23.54
14.71
28.77
HRS.Kanban1.exe
select RBDTAA07, RBDTAA14, SCD…
2.76
572
0.00
0.03
2.92
94.60
0.00
DashBoard.exe
SELECT “GroupBy1”.”A1″ AS “C1″…
发现sql语句的资源开销极低，但是总的cpu时间仍旧很高，这是为什么呢？
经过网上资料的查找，去查看了Top 5 Timed Foreground Events，发现cursor: mutex S的占比异常的高。
Top 5 Timed Foreground Events
Event
Waits
Time(s)
Avg wait (ms)
% DB time
Wait Class
cursor: mutex S
252,007
76,460
303
39.45
Concurrency
library cache lock
72,662
52,791
727
27.24
Concurrency
library cache: mutex X
74,912
37,272
498
19.23
Concurrency
resmgr:cpu quantum
32,328
19,099
591
9.86
Scheduler
DB CPU
9,545
4.93
在经过调查以后，发现cursor: mutex S这个异常升高极有可能是Oracle自身的bug，为了彻底断言问题发现的根源sql，我又去生成了当天一个小时内的ASH报告：
Top User Events
Event
Event Class
% Event
Avg Active Sessions
cursor: mutex S
Concurrency
34.68
0.33
library cache lock
Concurrency
30.32
0.29
library cache: mutex X
Concurrency
13.25
0.13
resmgr:cpu quantum
Scheduler
11.52
0.11
CPU + Wait for CPU
CPU
6.21
0.06
果然是cursor: mutex S引起的问题，但是却查不到具体的语句：
Top SQL with Top Events
SQL ID
Planhash
Sampled # of Executions
% Activity
Event
% Event
Top Row Source
% RwSrc
SQL Text
206972611
168
1.00
CPU + Wait for CPU
0.79
TABLE ACCESS – FULL
0.39
SELECT * FROM( SELECT * FROM( …
Top Events不是cursor: mutex S的，所以也无法确定是否是这句sql语句所导致的。
总结
查找了一些博客上对这个问题的解决方案，基本上就是给Oracle打补丁或者升级Oracle的版本，最终结果可能需要在升级以后再观察一段时间。
