latch：library cache引起的資料庫短暫hang
2011-12-8
Environment
OS: HP-UX B.11.31 U ia64
Oracle:10.2.0.4
Symptoms
下午一點半使用者反映系統卡住，監視OEM發現node 2 concurrency 陡然升高
查詢系統等待事件發現有大量的 cursor：pin S wait on X，library cache lock
當我再想該如何處理時，再次查詢，這些等待事件已經消失了，OEM中也是一個短暫的波峰。因為以前也曾經出現過這種事情，也沒有太在意。
沒有想到，兩點鐘，兩點半都出現了短暫的database hang，
資料庫hang時 OEM可以看到很高的 latch：library cache，matelink查詢看到這樣的一個bug：Bug 7122093 'latch: library cache' contention caused by queries on V$ views. （附檔1） ，和系統的動态視圖有關查詢引起？又觀察了一下異常發生的時間為每隔半個小時出現一次，查詢系統中的 jobs發現沒有是半點執行的，而且系統中的job也不會使用V$視圖。
根據附檔1中的資訊查詢發現有Library Cache waits with Full Table Scan on X$KGLDP when Querying v$session, v$access. [ID 860915.1]（附檔2）
半個小時執行一次？用到V$SESSION視圖，想到為了查詢系統中的deadlock
在跑一個查詢目前時間有無deadlock的powershell腳本，是每隔半點執行一次，
果斷停掉這個腳本。繼續觀察之後的幾個半點都沒有出現這種情況。
Question：這個腳本已經跑了很久了，為什麼今天下午集中爆發？
如果想徹底解決這個問題似乎要按MOS上給出solution打patch 7122093
附檔1
Bug 7122093 'latch: library cache' contention caused by queries on V$ views.
This note gives a brief overview of bug 7122093.
The content was last updated on: 28-APR-2010
Click here for details of each of the sections below.
Affects:
| Product (Component) | Oracle Server (Rdbms) |
| Range of versionsbelieved to be affected | Versions >= 10.2.0.1 but < 11.2 |
| Versionsconfirmed as being affected |
|
| Platforms affected | Generic (all / most platforms affected) |
Fixed:
| This issue is fixed in |
|
| Symptoms: | Related To: |
|
|
Description
Querying [G]V$ views based on X$KGLDP can hold library cache child latches for
too long potentially causing 'latch: library cache' contention.
HOOKS VIEW:X$KGLDP LIKELYAFFECTS XAFFECTS_A201 AFFECTS=10.2.0.1 XAFFECTS_A202 AFFECTS=10.2.0.2 XAFFECTS_A203 AFFECTS=10.2.0.3 XAFFECTS_A204 AFFECTS=10.2.0.4 XAFFECTS_A2041 AFFECTS=10.2.0.4.1 Patch Set Update XAFFECTS_A2042 AFFECTS=10.2.0.4.2 Patch Set Update XAFFECTS_A2043 AFFECTS=10.2.0.4.3 Patch Set Update XAFFECTS_A2044 AFFECTS=10.2.0.4.4 Patch Set Update XAFFECTS_A2045 AFFECTS=10.2.0.4.5 Patch Set Update XAFFECTS_B106 AFFECTS=11.1.0.6 XAFFECTS_B107 AFFECTS=11.1.0.7 XAFFECTS_B1071 AFFECTS=11.1.0.7.1 Patch Set Update XAFFECTS_B1072 AFFECTS=11.1.0.7.2 Patch Set Update XAFFECTS_B1073 AFFECTS=11.1.0.7.3 Patch Set Update XAFFECTS_B1074 AFFECTS=11.1.0.7.4 Patch Set Update XPRODID_5 PRODUCT_ID=5 PRODID-5 RDBMS XCOMP_RDBMS COMPONENT=RDBMS TAG_HANG/DB TAG_LATCHC TAG_PERF TAG_POOL TAG_QPERF FIXED_A205 FIXED_B201
| 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. Always consult with Oracle Support for advice. |
References
Bug:7122093 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article
| Related Products · Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition |
附檔2
| Library Cache waits with Full Table Scan on X$KGLDP when Querying v$session, v$access. [ID 860915.1] |
| Modified 16-MAR-2011 Type PROBLEM Status PUBLISHED |
In this Document
Symptoms
Cause
Solution
References
Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.3 to 11.2.0.0 - Release: 10.2 to 11.2
Information in this document applies to any platform.
Symptoms
High Library cache waits when querying v$session and v$access.
Excessive time spent on scan of the X$KGLDP fixed table can cause very long latch hold
times of library cache latch causing an instance wide hang.
Select v1.sid SID, count(v2.owner) ACCOBJECTS
From
v$session v1, v$access v2 Where v1.sid = v2.sid(+) Group By v1.sid
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 7 231.60 442.25 9343 0 5 86
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 9 231.60 442.25 9343 0 5 86
Misses in library cache during parse: 1
Optimizer mode: RULE
Parsing user id: SYS
Rows Row Source Operation
------- ---------------------------------------------------
86 SORT GROUP BY (cr=0 pr=9343 pw=9343 time=442253350 us)
1141 MERGE JOIN OUTER (cr=0 pr=9343 pw=9343 time=442251930 us)
86 SORT JOIN (cr=0 pr=0 pw=0 time=1233 us)
86 MERGE JOIN (cr=0 pr=0 pw=0 time=1032 us)
275 FIXED TABLE FULL X$KSLED (cr=0 pr=0 pw=0 time=12 us)
86 SORT JOIN (cr=0 pr=0 pw=0 time=787 us)
86 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=490 us)
1118 SORT JOIN (cr=0 pr=9343 pw=9343 time=442250796 us)
1118 VIEW GV$ACCESS (cr=0 pr=9343 pw=9343 time=442249458 us)
1118 SORT UNIQUE (cr=0 pr=9343 pw=9343 time=442249443 us)
1774 MERGE JOIN (cr=0 pr=9343 pw=9343 time=442245100 us)
1774 SORT JOIN (cr=0 pr=9343 pw=9343 time=442241112 us)
1774 MERGE JOIN (cr=0 pr=9343 pw=9343 time=442237515 us)
1774 SORT JOIN (cr=0 pr=0 pw=0 time=428868179 us)
1774 MERGE JOIN (cr=0 pr=0 pw=0 time=428865734 us)
2421 SORT JOIN (cr=0 pr=0 pw=0 time=6198 us)
2421 FIXED TABLE FULL X$KGLLK (cr=0 pr=0 pw=0 time=2566 us)
1774 SORT JOIN (cr=0 pr=0 pw=0 time=428857220 us)
71580 FIXED TABLE FULL X$KGLDP (cr=0 pr=0 pw=0 time=428543496 us)
|
====> Full table access of X$KGLDP time=428543496 us
1774 SORT JOIN (cr=0 pr=9343 pw=9343 time=13368502 us)
101345 FIXED TABLE FULL X$KGLOB (cr=0 pr=0 pw=0 time=10641299 us)
1774 SORT JOIN (cr=0 pr=0 pw=0 time=3040 us)
335 FIXED TABLE FULL X$KSUSE (cr=0 pr=0 pw=0 time=16 us)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- -----------
SQL*Net message to client 8 0.00 0.00
SQL*Net message from client 8 80.40 136.25
latch: library cache 23 119.13 216.12 ==> high waits on Library cache latch
enq: TT - contention 1 0.00 0.00
direct path write temp 13 0.00 0.01
direct path read temp 1356 0.00 0.28
********************************************************************************
.
Cause
This is due toBug 7122093.
Solution
Apply patch forBug 7122093 .