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

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;

执行一下：

效率还是可以的，从之前的1个小时没有跑出来到现在的6秒，还是很不错的，主要是需要找出SQL中的瓶颈部分，这个就需要经验和多读书、多看报。少吃零食多睡觉了。\^_\^

下来问了下开发人员说可以停掉的，那我就kill掉了，kill掉后主机的情况如下：

metalink解释

参考：Metalink： Bug 5476091
Description
If a session is waiting on a mutex wait (eg: 'cursor: pin X')
then interrupts to the session are ignored.
eg: Ctrl-C does not have any effect.

This issue can show up as a deadlock in a Parallel Query
between the QC (Query coordinator) and one of its slaves
with the QC waiting on "enq: PS - contention" deadlocked
against the slave holding the requested PS enqueue.

Bug 5476091 - Ctrl-C ignored for sessions waiting for mutexes / Deadlock with "enq: PS" - superceded (文档 ID 5476091.8)

Bug 5476091 Ctrl-C ignored for sessions waiting for mutexes / Deadlock with "enq: PS" - superceded

This note gives a brief overview of bug 5476091.
The content was last updated on: 21-JUL-2015
Click here for details of each of the sections below.

Affects:

Note that this fix has been superseded by the fix in Bug:10214450

Fixed:

This fix has been superseded - please see the fixed version information for Bug:10214450 . The box below only shows versions where the code change/s for 5476091 are first included - those versions may not contain the later improved fix.

Description

If a session is waiting on a mutex wait (eg: 'cursor: pin X')

then interrupts to the session are ignored.

eg: Ctrl-C does not have any effect.

This issue can show up as a deadlock in a Parallel Query

between the QC (Query coordinator) and one of its slaves

with the QC waiting on "enq: PS - contention" deadlocked

against the slave holding the requested PS enqueue.

Note:

This fix is superceded by the fix in bug 10214450

References

Bug:5476091 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

故障处理总结

到此所有的处理算是基本完毕，过程很简单，但是不同的场景处理方式有很多种，我们应该学会灵活变通。