cursor: mutex S 等待严重，请帮看一下awr报告
本帖最后由 taoy_2008 于 2014-2-13 12:12 编辑oracle 11.2.0.1 64位，aix 6.1。
奇怪的是，堵塞时杀进程也解决不了，但是只要重启，就能正常地跑一段时间。
等待的SQL就是正常的登录系统的SQL，每天要运行很多遍。 Host CPU (CPUs: 32 Cores: 16 Sockets: )
Load Average Begin Load Average End %User %System %WIO %Idle
175.06 3.41 19.1 0.9 0.0 80.0
Instance CPU
%Total CPU %Busy CPU %DB time waiting for CPU (Resource Manager)
8.0 40.0 0.0
Operating System Statistics - Detail
Snap Time Load %busy %user %sys %idle %iowait
11-Feb 17:00:02 175.06
11-Feb 18:00:05 3.41 20.01 19.15 0.86 79.99 0.02
快照开始时负载很高 load 达到 175
需要OSW 搞清楚 ， 到底是否是DB 引起了这么高的负载
另一个方面：
SQL ordered by Version Count
Only Statements with Version Count greater than 20 are displayed
Version Count Executions SQL Id SQL Module SQL Text
1,687 4vs91dcv7u1p6 ** SQL Text Not Available **
1,438 6379c22jdhsy5 ** SQL Text Not Available **
717 f711myt0q6cma ** SQL Text Not Available **
416 343 8z29bv52txdcq JDBC Thin Client insert into sq_vin_key (barcod...
271 116pqrxqvauqj update sq_plan_frame_rive set ...
260 1,960 53bm50yn938sb AppServer.exe Insert Into SQ_MES.SQ_IF_WMS_P...
200 75 969rvw4dhrpuy JDBC Thin Client update sq_plan_cab_produ_daq s...
170 7 68483hp4q83h9 JDBC Thin Client select * from ( select * from ...
156 8 237nxdk24vja0 JDBC Thin Client select * from ( select * from ...
156 8 d40hjpzx1m3sf JDBC Thin Client select count(*) from (select *...
145 1,610 gu03yuhwd17qp JDBC Thin Client insert into sq_material_receiv...
140 8 2bn5qv325n23p JDBC Thin Client select count(*) from (select *...
115 1 7gybf9hragyu1 JDBC Thin Client select count(*) from (select *...
109 80 9abs06kydt85h JDBC Thin Client update SQ_MES.sq_plan_cab_prod...
很高的version count 需要搞清楚原因 没法下载附件？ Maclean Liu(刘相兵 发表于 2014-2-13 10:49 static/image/common/back.gif
Host CPU (CPUs: 32 Cores: 16 Sockets: )
Load Average Begin Load Average End %User %System %WIO %Id ...
很高的 version count ，sql id 为4vs91dcv7u1p6 ，6379c22jdhsy5 我发现是这些语句引起的
insert into sys.aud$( sessionid,entryid,statement,ntimestamp#, userid,userhost,t
erminal,action#,returncode, obj$creator,obj$name,auth$privileges,auth$grantee, n
ew$owner,new$name,ses$actions,ses$tid,logoff$pread, logoff$lwrite,logoff$dead,co
mment$text,spare1,spare2, priv$used,clientid,sessioncpu,proxy$sid,user$guid, in 这些语句都为非业务数据 怀疑是不是bug导致？
另外 SQL> show parameter cursor_sharing;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT （该值是否要改为force） 参考这2个：
http://www.askmaclean.com/archives/script-oracle-diag-parse-wait-high-version-count.html
http://www.askmaclean.com/archives/11-2-obsolete-parent-cursors-_cursor_features_enabled-106001-event.html oracle 11.2.0.1 有很多BUG，建议升级 至11.2.0.3或 11.2.0.4. 生生不息 发表于 2014-2-13 12:04 static/image/common/back.gif
oracle 11.2.0.1 有很多BUG，建议升级 至11.2.0.3或 11.2.0.4.
关键是现在系统上线了，比较重要，不考虑升级。升级也许有新的风险。
页:
[1]