latch:library cache 引起的数据库短暂hang（BUG 7122093）

 

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	10.2.0.3 10.2.0.4 11.1.0.7
Platforms affected	Generic (all / most platforms affected)

Fixed:

This issue is fixed in		10.2.0.5 (Server Patch Set) 11.2.0.1 (Base Release)
Symptoms:		Related To:
Performance Affected (General) Performance Of Query/ies Affected Shared Pool Affected Hang (Involving Shared Resource) Latch Contention		(None Specified) X$KGLDP

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 /*+RULE */ 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 .