Oracle等待事件enq: PS - contention案例

前言部分

导读和注意事项

各位技术爱好者，看完本文后，你可以掌握如下的技能，也可以学到一些其它你所不知道的知识，~O(∩_∩)O~：

① 等待事件 enq: PS - contention的解决办法

② 一般等待事件的解决办法

故障分析及解决过程

故障环境介绍

故障发生现象及报错信息

开发人员反馈数据库很慢，让帮忙查查原因，那首当其冲的就是看主机的情况了，主机是AIX系统，采用TOPAS查看主机的情况，如下图，从图中可以看出的确有一个oracle的进程非常占用CPU资源：

故障分析及解决过程

根据os的进程号到数据库中查看相关的会话：

SELECT a.INST_ID, a.SQL_ID, a.EVENT, a.PREV_SQL_ID, a.STATUS,a.USERNAME,a.OSUSER

FROM gv$session a, gv$process b

WHERE a.PADDR = b.ADDR

and b.SPID = 3109012;

可以看到该会话的等待事件是enq: PS - contention，并且有相关的SQL和OSUSER，可以联系到当时的开发人员，据说已经跑了1个小时了，我们先来看看具体的sql内容：

SELECT *

FROM gv$sqlarea a

WHERE a.SQL_ID = 'cg7q9tn7u5vyx'

and a.INST_ID = 1;

SQL文本copy出来：

SELECT t.*, s.sid, s.serial#, s.machine, s.program, s.osuser

FROM (SELECT b.INST_ID,

c.USERNAME,

a.event,

to_char(a.cnt) AS seconds,

a.sql_id,

dbms_lob.substr(b.sql_fulltext, 100, 1) sqltext

FROM (SELECT rownum rn, t.*

FROM (SELECT s.INST_ID,

decode(s.session_state,

'WAITING',

s.event,

'Cpu + Wait For Cpu') Event,

s.sql_id,

s.user_id,

COUNT(*) CNT

FROM gv$active_session_history s

WHERE sample_time > SYSDATE - 30 / 1440

GROUP BY INST_ID,

s.user_id,

decode(s.session_state,

'WAITING',

s.event,

'Cpu + Wait For Cpu'),

s.sql_id

ORDER BY CNT DESC) t

WHERE rownum \< 20) a,

gv$sqlarea b,

dba_users c

WHERE a.sql_id = b.sql_id

AND a.user_id = c.user_id

AND a.INST_ID = b.INST_ID

ORDER BY CNT DESC) t,

gv$session s

WHERE t.sql_id = s.sql_id(+)

AND t.INST_ID = s.INST_ID(+)

ORDER BY t.INST_ID

从文本中可以看出该sql查询的是数据字典，估计是从网上copy过来的，以哥多年的开发经验瞅了一眼就发现一个特殊的地方dbms_lob.substr(b.sql_fulltext, 100, 1)，

这类clob类型的都比较耗费资源，因为比较忙就不深入的分析了，简单看了下把该句修改为b.SQL_TEXT，满足要求即可，没有必要去查询clob。

简单修改后：

SELECT t.*, s.sid, s.serial#, s.machine, s.program, s.osuser

FROM (SELECT b.INST_ID,

c.USERNAME,

a.event,

to_char(a.cnt) AS seconds,

a.sql_id,

--dbms_lob.substr(b.sql_fulltext, 100, 1) sqltext ,

b.SQL_TEXT

FROM (SELECT rownum rn, t.*

FROM (SELECT s.INST_ID,

decode(s.session_state,

'WAITING',

s.event,

'Cpu + Wait For Cpu') Event,

s.sql_id,

s.user_id,

COUNT(*) CNT

FROM gv$active_session_history s

WHERE sample_time > SYSDATE - 30 / 1440

GROUP BY INST_ID,

s.user_id,

decode(s.session_state,

'WAITING',

s.event,

'Cpu + Wait For Cpu'),

s.sql_id

ORDER BY CNT DESC) t

WHERE rownum \< 20) a,

gv$sqlarea b,

dba_users c

后续精彩内容已被小麦苗无情隐藏，请输入验证码解锁本站所有文章

验证码：

请关注本站微信公众号，回复“小麦苗博客”，获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。