【故障解決】enq: PS - contention
各位技術愛好者，看完本文後，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~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皆可，您的批評指正是我寫作的最大動力。
項目
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
開發人員反饋數據庫很慢，讓幫忙查查原因，那首當其沖的就是看主機的情況了，主機是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： 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
到此所有的處理算是基本完畢，過程很簡單，但是不同的場景處理方式有很多種，我們應該學會靈活變通。
...........................................................................................................................................................................................
本文作者：小麥苗，只專注於數據庫的技術，更注重技術的運用
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 在中行完成
<版權所有，文章允許轉載，但須以鏈接方式注明源地址，否則追究法律責任!>
...........................................................................................................................................................................................
