【故障解决】enq: PS

【故障解决】enq: PS - contention

一.1  BLOG文档结构图

 

 

 

一.2  前言部分

 

一.2.1  导读和注意事项

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

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

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

 

  Tips：

       ① 若文章代码格式有错乱，推荐使用QQ或360浏览器，也可以下载pdf格式的文档来查看，pdf文档下载地址：http://yunpan.cn/cdEQedhCs2kFz（提取码：ed9b） 

       ② 本篇BLOG中代码部分需要特别关注的地方我都用黄色背景和红色字体来表示，比如下边的例子中，thread 1的最大归档日志号为33，thread 2的最大归档日志号为43是需要特别关注的地方，命令一般使用粉红颜色标注，注释一般采用蓝色字体表示。

 

  List of Archived Logs in backup set 11

  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time

  ---- ------- ---------- ------------------- ---------- ---------

  1    32      1621589    2015-05-29 11:09:52 1625242    2015-05-29 11:15:48

  1    33      1625242    2015-05-29 11:15:48 1625293    2015-05-29 11:15:58

  2    42      1613951    2015-05-29 10:41:18 1625245    2015-05-29 11:15:49

  2    43      1625245    2015-05-29 11:15:49 1625253    2015-05-29 11:15:53

 

 

 

[ZFXDESKDB1:root]:/>lsvg -o

T_XDESK_APP1_vg

rootvg

[ZFXDESKDB1:root]:/>

[ZFXDESKDB1:root]:/>lsvg rootvg

 

 

====》2097152*512/1024/1024/1024=1G 

 

 

 

 

 

本文如有错误或不完善的地方请大家多多指正，ITPUB留言或QQ皆可，您的批评指正是我写作的最大动力。

 

 

 

一.2.2  相关参考文章链接

 

 

一.2.3  本文简介

 

 

 

 

一.3  相关知识点扫盲

 

 

 

 

 

 

一.4  故障分析及解决过程

 

一.4.1  故障环境介绍

 

项目	source db
db 类型	RAC
db version	10.2.0.4.0
db 存储	RAW
ORACLE_SID	XXX
db_name	XXX
主机IP地址：	XXX
OS版本及kernel版本	AIX 5.3.0.0
OS hostname	XXX

 

 

 

 

一.4.2  故障发生现象及报错信息

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

 

 

 

 

 

 

一.4.3  故障分析及解决过程

根据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掉后主机的情况如下：

 

 

 

一.4.3.1  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:

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions BELOW 11.2
Versions confirmed as being affected	· 10.2.0.4 · 10.2.0.3
Platforms affected	Generic (all / most platforms affected)

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.

The fix for 5476091 is first included in

· 11.2.0.1 (Base Release) · 11.1.0.7 (Server Patch Set) · 10.2.0.5 (Server Patch Set)

 

Symptoms:	Related To:
· Deadlock · Hang (Process Hang) · Mutex Contention · Waits for "cursor: pin X" · Waits for "enq: PS - contention"	· Parallel Query (PQO)

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

 

Please note: The above is a summary description only. Actual symptoms can vary. Matching to any symptoms here does not confirm that you are encountering this problem. For questions about this bug please consult Oracle Support.

References

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

 

 

一.5  故障处理总结

 

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

 

 

 

一.6  About Me

 

...........................................................................................................................................................................................

本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用

ITPUB BLOG：http://blog.itpub.net/26736162

本文地址：http://blog.itpub.net/26736162/viewspace-1985380/

本文pdf版：http://yunpan.cn/cdEQedhCs2kFz （提取码：ed9b）

QQ：642808185 若加QQ请注明您所正在读的文章标题

于 2016-01-28 10:00~ 2016-01-28 19:00 在中行完成

<版权所有，文章允许转载，但须以链接方式注明源地址，否则追究法律责任!>

...........................................................................................................................................................................................