Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!
发新帖
Maclean Liu(刘相兵

2135

积分

502

好友

184

主题

WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!

1#
发表于 2012-2-9 19:19:16 | 查看: 9108| 回复: 3
Question:

   问题简述如下：       AIX上运行的双节点RAC数据库Hang住（2012年1月20号3点52分左右，后重启数据库解决），实例2的警告日志中出现了WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!的错误信息，随后查看相应的trace文件，


*** 2012-01-20 03:52:15.042
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 70000033933c2b8, mode: N, request: S


文件中并未发现任何其他session以x模式持有对应的row cache enqueue，也查看了MOS上的相关文档，其上提供的例子都是可以找出blocking session的（以x模式持有其他session要请求的的row cache）。之后，我只能猜可能是Bug导致的，但是找不到特别符合的。客户追问问题的确定原因，我无法回答。


       附件是数据库的警告日志以及相应的trace文件，烦请您帮忙分析一下问题产生的原因，不胜感激。
下载专业ORACLE数据库恢复工具PRM-DUL  For Oracle http://www.parnassusdata.com/zh-hans/emergency-services

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638  QQ: 47079569   
2#
发表于 2012-2-9 19:45:15
ODM Finding:

*** 2012-01-20 03:52:15.042
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 70000033933c2b8, mode: N, request: S

SO: 70000033933c2b8, type: 4, owner: 70000033924d378, flag: INIT/-/-/0x00
    (session) sid: 517 trans: 0, creator: 70000033924d378, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0002-001F-00000002, short-term DID: 0002-001F-00000003
              txn branch: 0
              oct: 3, prv: 0, sql: 70000032bcf3358, psql: 70000032bcf3358, user: 0/SYS
    service name: SYS$USERS
    O/S info: user: oracle, term: , ospid: 1687624, machine: GKdb2
              program: racgimon@GKdb2 (TNS V1-V3)
    application name: racgimon@GKdb2 (TNS V1-V3), hash value=3775208945
    last wait for 'ksdxexeotherwait' blocking sess=0x0 seq=6236 wait_time=2 seconds since wait started=18
                =0, =0, =0

racgimon@GKdb2 (TNS V1-V3) wait for ROW CACHE ENQUEUE LOCK!

racgimon 是RAC中监控 instance的进程，




      SO: 70000032cfe6358, type: 53, owner: 70000033933c2b8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=70000032cfe6358 handle=70000032bcf3358 mode=N
      call pin=0 session pin=0 hpc=0000 hlc=0000
      htl=70000032cfe63d8[70000032aae7c18,70000032aae7c18] htb=70000032aae7c18 ssga=70000032aae6c10
      user=70000033933c2b8 session=70000033933c2b8 count=1 flags=[0000] savepoint=0x4f186a0d
      LIBRARY OBJECT HANDLE: handle=70000032bcf3358 mtx=70000032bcf3488(2) cdp=2
      name=select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N') ,DECODE(UPPER(i.STATUS),'OPEN','O','N') into :b0,:b1  from v$database d ,gv$instance i where i.INSTAN
CE_NAME=:b2
      hash=23b6ea0ac674d70a775ed8ffdba461ef timestamp=01-16-2012 19:46:39
      namespace=CRSR flags=RON/KGHP/TIM/KEP/PN0/MED/KST/DBN/MTX/[508100d0]
      kkkk-dddd-llll=0041-0001-0001 lock=N pin=0 latch#=4 hpc=251e hlc=251e
      lwt=70000032bcf3400[70000032bcf3400,70000032bcf3400] ltm=70000032bcf3410[70000032bcf3410,70000032bcf3410]
      pwt=70000032bcf33c8[70000032bcf33c8,70000032bcf33c8] ptm=70000032bcf33d8[70000032bcf33d8,70000032bcf33d8]
      ref=70000032bcf3430[70000032bcf3430,70000032bcf3430] lnd=70000032bcf3448[70000032bcf3448,70000032bcf3448]
        LIBRARY OBJECT: object=70000032bcf2e88
        type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
        CHILDREN: size=16
        child#    table reference   handle
        ------ -------- --------- --------
             0 70000032bcf2950 70000032bcf25c0 70000032bcf2428
             1 70000032bcf2950 70000032bcf2858 70000032bce6bc8
        DATA BLOCKS:
        data#     heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 70000032bcf3298 70000032bcf2fa0 I/P/A/-/-    0 NONE   00


racgimon 正在解析的语句是

select DECODE(UPPER(d.OPEN_MODE),'READ WRITE','R','N') ,DECODE(UPPER(i.STATUS),'OPEN','O','N') into :b0,:b1  from v$database d ,gv$instance i where i.INSTANCE_NAME=:b2




SO: 70000033924d378, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=31, calls cur/top: 70000032aae66a8/70000032aae66a8, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 55
              last post received-location: kjata: wake up enqueue owner
              last process to post me: 70000033a2293a8 1 6
              last post sent: 0 0 24
              last post sent-location: ksasnd
              last process posted by me: 70000033b236468 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 7000003392875d0
    O/S info: user: oracle, term: UNKNOWN, ospid: 2121752
    OSD pid info: Unix process pid: 2121752, image: oracle@GKdb2 (TNS V1-V3)
    Short stack dump:
ksdxfstk+002c<-ksdxdocmdmult+0fc4<-ksudmp_proc+02ac<-ksudss+0724<-kqrigt+0568<-kqrpre1+0960<-kqrpre+001c<-koknmpn+01ac<-opikod+04c0<-opiodr+0ae0<-ttcpip+1020<-opitsk+1
124<-opiino+0990<-opiodr+0ae0<-opidrv+0484<-sou2o+0090<-opimai_real+01bc<-main+0098<-__start+0098




PID=31

ASS.AWK analysis
  1. [oracle@vrh8 ~]$ awk -f ass109.awk.1 cwgkvpd2_ora_2121752.trc

  3. Starting Systemstate 1
  4. ...............................................
  5. Ass.Awk Version 1.0.9 - Processing cwgkvpd2_ora_2121752.trc

  7. System State 1
  8. ~~~~~~~~~~~~~~~~
  9. 1:                                      
  10. 2:  waiting for 'pmon timer'            wait
  11. 3:  waiting for 'DIAG idle wait'        wait
  12. 4:  waiting for 'rdbms ipc message'     wait
  13. 5:  waiting for 'rdbms ipc message'     wait
  14. 6:  waiting for 'ges remote message'    wait
  15. 7:  waiting for 'gcs remote message'    wait
  16. 8:  waiting for 'gcs remote message'    wait
  17. 9:  waiting for 'gcs remote message'    wait
  18. 10: waiting for 'gcs remote message'    wait
  19. 11: waiting for 'rdbms ipc message'     wait
  20. 12: waiting for 'rdbms ipc message'     wait
  21. 13: waiting for 'rdbms ipc message'     wait
  22. 14: waiting for 'rdbms ipc message'     wait
  23. 15: waiting for 'rdbms ipc message'     wait
  24. 16: waiting for 'cursor: pin S wait on X' wait
  25. 17: waiting for 'cursor: pin S wait on X' wait
  26. 18: waiting for 'cursor: pin S wait on X' wait
  27. 19: waiting for 'rdbms ipc reply'       wait
  28. 20: waiting for 'rdbms ipc message'     wait
  29. 21: waiting for 'latch: row cache objects'[Latch 70000032f912408] wait
  30. 22: waiting for 'cursor: pin S wait on X' wait
  31. 23: waiting for 'rdbms ipc message'     wait
  32. 24: waiting for 'rdbms ipc message'     wait
  33. 25: waiting for 'latch: row cache objects'[Rcache object=700000328d14fe0,] wait
  34.      Cmd: Select
  35. 26: waiting for 'Streams AQ: qmn coordinator idle wait' wait
  36. 27: waiting for 'latch: row cache objects'[Rcache object=7000002b6df0ab0,] wait
  37. 29: waiting for 'SQL*Net message from client' wait
  38. 30: waiting for 'latch: row cache objects'[Rcache object=7000002cdc4a358,] wait
  39. 31: last wait for 'ksdxexeotherwait'   [Rcache object=7000002dae97c58,]
  40.      Cmd: Select
  41. 32: last wait for 'latch: row cache objects'
  42.      Cmd: PL/SQL Execute
  43. 33: waiting for 'SQL*Net message from client' wait
  44. 34: waiting for 'cursor: pin S wait on X' wait
  45. 35: waiting for 'latch: row cache objects'[Rcache object=700000328950f28,] wait
  46. 37: waiting for 'cursor: pin S wait on X' wait
  47. 38: waiting for 'cursor: pin S wait on X' wait
  48. 39: waiting for 'cursor: pin S wait on X' wait
  49. 40: waiting for 'cursor: pin S wait on X' wait
  50. 41: waiting for 'cursor: pin S wait on X' wait
  51. 42: waiting for 'latch: row cache objects'[Rcache object=7000002cdb335c8,] wait
  52. 43: for 'Streams AQ: waiting for time management or cleanup tasks' wait
  53. 44: waiting for 'Streams AQ: qmn slave idle wait' wait
  54. 45:                                    
  55. 46: waiting for 'cursor: pin S wait on X' wait
  56. 47: waiting for 'cursor: pin S wait on X' wait
  57. 48: waiting for 'cursor: pin S wait on X' wait
  58. 50: waiting for 'cursor: pin S wait on X' wait
  59. Blockers
  60. ~~~~~~~~

  62.         Above is a list of all the processes. If they are waiting for a resource
  63.         then it will be given in square brackets. Below is a summary of the
  64.         waited upon resources, together with the holder of that resource.
  65.         Notes:
  66.         ~~~~~
  67.          o A process id of '???' implies that the holder was not found in the
  68.            systemstate.

  70.                     Resource Holder State
  71.        Latch 70000032f912408    ??? Blocker
  72. Rcache object=700000328d14fe0,    ??? Blocker
  73. Rcache object=7000002b6df0ab0,    ??? Blocker
  74. Rcache object=7000002cdc4a358,    ??? Blocker
  75. Rcache object=7000002dae97c58,    ??? Blocker
  76. Rcache object=700000328950f28,    ??? Blocker
  77. Rcache object=7000002cdb335c8,    ??? Blocker

  79. Object Names
  80. ~~~~~~~~~~~~
  81. Latch 70000032f912408   Child row cache objects      
  82. Rcache object=700000328d14fe0,                                
  83. Rcache object=7000002b6df0ab0,                                
  84. Rcache object=7000002cdc4a358,                                
  85. Rcache object=7000002dae97c58,                                
  86. Rcache object=700000328950f28,                                
  87. Rcache object=7000002cdb335c8,                                


  90. 231196 Lines Processed.
复制代码
因为是RAC环境，且不是267 level的systemstate dump 所以部分Rcache object的holder 不可知。

其他服务进程的一些信息：

PID=35

PROCESS 35:
  ----------------------------------------
  SO: 70000033924db58, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=35, calls cur/top: 7000003282dd728/70000032cfe18b0, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 163
              last post received-location: kqrbtm
              last process to post me: 70000033b236468 1 6
              last post sent: 0 0 24
              last post sent-location: ksasnd
              last process posted by me: 70000033b236468 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 7000003392875d0
    O/S info: user: oracle, term: UNKNOWN, ospid: 2191584
    OSD pid info: Unix process pid: 2191584, image: oracle@GKdb2
    Short stack dump:
ksdxfstk+002c<-ksdxcb+04e4<-sspuser+0074<-000044C0<-skgpwwait+00bc<-ksliwat+06c0<-kslwaitns_timed+0024<-kskthbwt+022c<-kslwait+00f4<-kqrigt+05e0<-kqrpre1+0960<-kqrpre+
001c<-kkercs+0244<-kkehrd+0e1c<-kkoarl+0350<-kkoiqb+5838<-kkooqb+0a40<-kkoqbc+20ac<-apakkoqb+0084<-apaqbdDescendents+03d4<-apaqbdListReverse+006c<-apaqbd+000c<-apadrv+
045c<-opitca+13fc<-kksFullTypeCheck+001c<-rpiswu2+034c<-kksSetBindType+0d28<-kksfbc+1054<-opiexe+098c<-opiefn0+0198<-opiefn+00dc<-opiall0+0c0c<-opikpr+02cc<-opiodr+0ae
0<-rpidrus+01bc<-skgmstack+00c8<-rpidru+0088<-rpiswu2+034c<-kprball+06a4<-kkpolpd_load_part_descr+02e4<-kqlctxlod+0094<-kqllod+1a54<-kglobld+05ac<-kglobpn+09f8<-kglpim
+0294<-kglpin+0e60<-kkdllpoOpt+095c<-kkdllpo+003c<-kokliccx+0084<-kokacmc+0070<-kksLoadChild+0f78<-kkslod+0120<-kglobld+05ac<-kglobpn+09f8<-kglpim+0294<-kglpin+0e60<-k
xsGetRuntimeLock+0458<-kksfbc+28b0<-kkspsc0+0ffc<-kokapvpr+02a8<-kokacau+06b0<-kokasuc+0104<-kokavpr+01e4<-koklcprv+03b4<-koklpgsz+00f4<-kpolob+13bc<-opiodr+0ae0<-ttcp
ip+1020<-opitsk+1124<-opiino+0990<-opiodr+0ae0<-opidrv+0484<-sou2o+0090<-opimai_real+01bc<-main+0098<-__start+0098



    SO: 70000033c334d40, type: 4, owner: 70000033924db58, flag: INIT/-/-/0x00
    (session) sid: 514 trans: 0, creator: 70000033924db58, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0002-0023-00000378, short-term DID: 0002-0023-00000377
              txn branch: 0
              oct: 0, prv: 0, sql: 7000002ec963e18, psql: 700000328700c70, user: 72/NBI
    service name: SYS$USERS
    O/S info: user: , term: , ospid: 1234, machine: dccwbak
              program:
    waiting for 'latch: row cache objects' blocking sess=0x0 seq=2894 wait_time=0 seconds since wait started=0
                address=70000032f912408, number=c8, tries=0
    Dumping Session Wait History
     for 'row cache lock' count=1 wait_time=2759215
                cache id=10, mode=0, request=3
     for 'latch: row cache objects' count=1 wait_time=59490
                address=70000032f912408, number=c8, tries=0
     for 'row cache lock' count=1 wait_time=2929707
                cache id=10, mode=0, request=3
     for 'latch: row cache objects' count=1 wait_time=47825
                address=70000032f912408, number=c8, tries=0
     for 'row cache lock' count=1 wait_time=2929706
                cache id=10, mode=0, request=3
     for 'latch: row cache objects' count=1 wait_time=16640
                address=70000032f912408, number=c8, tries=0
     for 'row cache lock' count=1 wait_time=2929706
                cache id=10, mode=0, request=3
     for 'latch: row cache objects' count=1 wait_time=72580
                address=70000032f912408, number=c8, tries=0
     for 'row cache lock' count=1 wait_time=2929704
                cache id=10, mode=0, request=3
     for 'latch: row cache objects' count=1 wait_time=33290
                address=70000032f91

    SO: 7000003282dd728, type: 3, owner: 70000032cfe7628, flag: INIT/-/-/0x00
        (call) sess: cur 700000339336d18, rec 700000339336d18, usr 70000033c334d40; depth: 2
          ----------------------------------------
          SO: 7000002e5240638, type: 50, owner: 7000003282dd728, flag: INIT/-/-/0x00
          row cache enqueue: count=1 session=70000033c334d40 object=700000328950f28, request=S
          savepoint=0x3df76
          row cache parent object: address=700000328950f28 cid=16(dc_histogram_defs)
          hash=32f645c6 typ=11 transaction=0 flags=00000000
          own=700000328950ff8[700000328950ff8,700000328950ff8] wat=700000328951008[7000002e5240668,7000002e5240668] mode=N
          status=-/-/-/-/-/-/-/-/-
          request=N release=FALSE flags=4
          instance lock id=QQ 355e72a6 feac22e5
          set=0, complete=FALSE
          set=1, complete=FALSE
          data=
          00000106 00060000 00000000 00000000 00000000 00000000 00000000 00000000
          00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
          00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
          00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
          00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
          00000000 00000000


就等待事件来看 这个实例存在大量的硬解析 ， row cache lock的主要对象类别是 dc_histogram_defs 即 histogram信息。


建议你上传问题时段  这2个实例的AWR报告， 通过调优解析来 避免row cache lock hang。

回复 只看该作者 道具 举报

3#
发表于 2012-2-9 20:02:00
1月20号实例2上1点至3点的AWR报告请查看附件
ps：由于后来数据库重启，造成AWR快照出现断档，所以目前只能提供这两份报告，请见谅

AWR_20120120_1点--3点.rar

64.52 KB, 下载次数: 1130

回复 只看该作者 道具 举报

4#
发表于 2012-2-9 21:26:42
1. 参照AWR 来看  不管是 1点-2点 还是 2点-3点 实例2的负载都很低 , 主要等待事件是



硬解析并不多 Hard parses:        0.29/s         0.20/trans  

Event        Waits        Time(s)        Avg Wait(ms)        % Total Call Time        Wait Class
CPU time                 734                 81.7         
latch: shared pool        641        179        279        19.9        Concurrency
latch: library cache        129        35        269        3.9        Concurrency
enq: TM - contention        211        14        64        1.5        Application
os thread startup        16        9        554        1.0        Concurrency



latch: shared pool 这个latch 的miss source主要是 kghdmp这个函数  ，这是一个heap manager dump 函数(Location from where latch is held: kghdmp

This means that pmon cannot get a shared_pool latch, and that
the latch holder is in kghdmp, the heap dumper. So pmon is
block by a session taking shared pool dumps because of the event.)


shared pool存在大量的 FREE memory， 所以不存在shared pool空间不足引起
latch: shared pool和row cache lock的可能

可能这个AWR报告并没有 参考意义， 因为在systemstate dump中反复出现多次的 cursor pin S on X 、row cache lock等并未在此AWR中体现



2. 观察 参数会发现 optimizer_mode=CHOOSE ，  该参数指定了oracle优化器的模式：
choose

The optimizer chooses between a cost-based approach and a rule-based approach based on whether statistics are available.
If the data dictionary contains statistics for at least one of the accessed tables, then the optimizer uses a cost-based approach and optimizes with a goal of best throughput. If the data dictionary contains only some statistics, then the cost-based approach is used, and the optimizer must guess the statistics for the subjects without any statistics. This can result in sub-optimal execution plans. If the data dictionary contains no statistics for any of the accessed tables, then the optimizer uses a rule-based approach.


因为10g 中默认会有job gather_stats 所以，建议你们改回 ALL_ROWS


3. 就systemstate 中无法找出 相关resource 的holder， 因为row cache lock 引发较低级别的systemstate dump， 建议你在下次遇到该问题时 采用以下命令收集dump:

set echo on

-- Taking Hang Analyze dumps
-- This may take a little while...
oradebug setmypid
oradebug unlimit
oradebug -g all hanganalyze 3
-- This part may take the longest, you can monitor bdump or udump to see if
-- the file is being generated.
oradebug -g all dump systemstate 267



4. 收集到的相关类似的bug 信息：
  1. Bug 8507295: RAC DATABASE HANGS, WAITED TOO LONG FOR ROW CACHE ENQUEUE LOCK

  3. Hdr: 8507295 10.2.0.4 RDBMS 10.2.0.4.0 DICTIONARY PRODID-5 PORTID-23
  4. Abstract: RAC DATABASE HANGS, WAITED  TOO LONG FOR ROW CACHE ENQUEUE LOCK

  6. The Waited too long for row cache enqueue is generally caused by
  7. a deadlock, and on rare occasions by an extremely slow system.

  9. The key to solving this problem is finding out what the holder
  10. is doing or why the holder is blocked also.

  12. Thanks for not transferring all trace files but just a relevant
  13. selection. Having a look:

  15. mysqld5_ora_25110.trc:


  18. >>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
  19. row cache enqueue: session: 193647cd88, mode: N, request: S

  21. The waiting session:
  22. SO: 193647cd88, type: 4, owner: 19383a74c8, flag: INIT/-/-/0x00
  23.     (session) sid: 706 trans: 0, creator: 19383a74c8, flag: (100041) USR/-
  24. BSY/-/-/-/-/-

  26. And it requests a dc_histogram_defs rowcache enqueue
  27.           SO: 14fbc42ec0, type: 50, owner: 18ccde5688, flag: INIT/-/-/0x00
  28.           row cache enqueue: count=1 session=193647cd88 object=14feb84c28,
  29. request=S
  30.           savepoint=0x10e89
  31.           row cache parent object: address=14feb84c28 cid=16(dc_histogram_
  32. defs)
  33.           hash=4dc05438 typ=11 transaction=0 flags=00000000
  34.           own=14feb84cf8[14feb84cf8,14feb84cf8] wat=14feb84d08[14fbc42ef0,
  35. 14fbc42ef0] mode=N
  36.           status=-/-/-/-/-/-/-/-/-
  37.           request=N release=FALSE flags=4
  38.           instance lock id=QQ 94d46a18 ea3ded29
  39.           set=0, complete=FALSE
  40.           set=1, complete=FALSE
  41.           data=

  43. The call stack shows we are optimizing a query.

  45. The request is a Share lock (for reading), this means that someone
  46. else is having an incompatible X lock (for writing).
  47. This means that some analyze or so is going on and that is blocking
  48. the parse of queries. Normally an analyze would not take this long
  49. so its probably blocked itself.

  51. I could not find the holder of this enqueue in the system state dump
  52. this means the holder is on a different instance.

  54. Looks like the problems started earlier:
  55. alert_mysqld2.log
  56. Mon May 11 06:46:44 2009
  57. Trace dumping is performing id=[cdmp_20090511064634]
  58. Mon May 11 06:49:31 2009
  59. Errors in file /u01/app/oracle/admin/mysqld/udump/mysqld2_ora_8795.trc:
  60. ORA-600: internal error code, arguments: [12870],
  61. [kkdlgon: waited too long for object id], [], [], [], [], [], []

  63. And even earlier in alert_mysqld4.log:
  64. Mon May 11 06:16:23 2009
  65. Errors in file /u01/app/oracle/admin/mysqld/udump/mysqld4_ora_18189.trc:
  66. ORA-600: internal error code, arguments: [12870],
  67. [kkdlgon: waited too long for object id], [], [], [], [], [], []

  69. 1:  Please provide the trace file mysqld4_ora_18189.trc
  70. 2:  On instance 1 and 5 there must slightly older diag traces,
  71. please get those also.



  75. $ unzip ../diag_1_5_20090512.zip
  76. Archive:  ../diag_1_5_20090512.zip
  77. error [../diag_1_5_20090512.zip]:  missing 4200 bytes in zipfile
  78. Please check and retransfer this file.

  80. Looking at mysqld5_ora_8438.trc:
  81. Most processes are blocked by process 164, that
  82. itself is blocked waiting for dc_sequences.
  83. The lock name for it is  QN-5e015672-2a955fbd

  85. row cache parent object: address=154569f0a0 cid=13(dc_sequences)
  86.               hash=9bac7e02 typ=11 transaction=0 flags=00000002
  87.               own=154569f170[154569f170,154569f170] wat=154569f180[18bde97
  88. 230,18bde97230] mode=S
  89.               status=VALID/-/-/-/-/-/-/-/-
  90.               request=N release=FALSE flags=4
  91.               instance lock id=QN 5e015672 2a955fbd

  93. The master for that lock is node 1:
  94.    lp 154569f280 gl KJUSERPR rp 18169890a8 [0x5e015672][0x2a955fbd],[QN]
  95.       master 1 pid 13000 bast 1 rseq 7943 mseq 0 history 0x55555555

  97. I could not find the holder of this enqueue in any of the trace files.
  98. Please if you can find a holder of this lock in any of the files
  99. that generated around that time, for instance in any of
  100. the globally generated SYSTEM STATE dumps.


  103. Applying patch 6966286 causes conflict with 7039896.
  104. This is addressed by merge patch
  105. ~~~~~~~~~~~~~~~~~~
  106. Hdr: 8222298 10.2.0.4 RDBMS 10.2.0.4 VOS HEAP MGMT PRODID-5 PORTID-46
  107. Abstract: MERGE LABEL REQUEST ON TOP OF 10.2.0.4 FOR BUGS 7039896 6966286
  108. ~~~~~~~~~~~~~~~~~
  109. and customer is now applying 8222298 instead of 6966286
复制代码
该bug也是类似的 row cache lock for  dc_histogram_defs IN S mode :

"The request is a Share lock (for reading), this means that someone
else is having an incompatible X lock (for writing).
This means that some analyze or so is going on and that is blocking
the parse of queries. Normally an analyze would not take this long
so its probably blocked itself.

I could not find the holder of this enqueue in the system state dump
this means the holder is on a different instance."

类似的这个bug 在10.2.0.4 上也是找不到 resource的holder ，可能在另一个实例中。 所以需要global的 267 level的 systemstate dump分析。

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-23 19:32 , Processed in 0.086924 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569