Linux安全网 - Linux操作系统_Linux 命令_Linux教程_Linux黑客

绿色网站无广告
会员投稿 投稿指南 本期推荐:
搜索:
您的位置: Linux安全网 > Linux编程 > 数据库管理 > » 正文

latch:library cache 引起的数据库短暂hang(BUG 7122093)

来源: xiaofan23z 分享至:

 

latchlibrary cache引起的数据库短暂hang

2011-12-8

Environment

OS: HP-UX B.11.31 U ia64

Oracle:10.2.0.4

 

Symptoms

 下午一点半用户反映系统卡住,监视OEM发现node 2 concurrency 陡然升高

 

 

 

查询系统等待事件发现有大量的 cursorpin S wait on Xlibrary cache lock

当我再想该如何处理时,再次查询,这些等待事件已经消失了,OEM中也是一个短暂的波峰。因为以前也曾经出现过这种事情,也没有太在意。

没有想到,两点钟,两点半都出现了短暂的database hang

 数据库hang OEM可以看到很高的 latchlibrary cachematelink查询看到这样的一个bugBug 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

在跑一个查询当前时间有无deadlockpowershell脚本,是每隔半点执行一次,

果断停掉这个脚本。继续观察之后的几个半点都没有出现这种情况。

 

Question:这个脚本已经跑了很久了,为什么今天下午集中爆发?

 如果想彻底解决这个问题似乎要按MOS上给出solutionpatch 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
********************************************************************************

 


Tags:
分享至:
最新图文资讯
1 2 3 4 5 6
验证码:点击我更换图片 理智评论文明上网,拒绝恶意谩骂 用户名:
关于我们 - 联系我们 - 广告服务 - 友情链接 - 网站地图 - 版权声明 - 发展历史