ITPub博客

首页 > 数据库 > Oracle > 翻译metalink关于WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK文章

翻译metalink关于WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK文章

翻译 Oracle 作者:liiinuuux 时间:2014-01-28 16:57:51 0 删除 编辑
翻译metalink关于WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK文章

可能的原因
SGA resize操作的过程中会锁住row cache
相关BUG
Document 7189722.8 Bug 7189722 - Frequent grow/shrink SGA resize operations
Document 9267837.8 Bug 9267837 - Auto-SGA policy may see larger resizes than needed

根据被锁的对象判断原因
DC_TABLESPACES
可能是给表空间分配extent导致的,查看是否存在extent size特别小的对象,导致频繁分配的情况,比如优先找出某个对象的extent特别多特别碎。

DC_SEQUENCES
查看序列的cache是不是太小了。
相关BUG
Document 853652.1 RAC and Sequences
Document 395314.1 RAC Hangs due to small cache size on SYS.AUDSES$ - fixed in 10.2.0.3
Document 6027068.8 Bug 6027068 - Contention on ORA_TQ_BASE sequence -fixed in 10.2.0.5 and 11.2.0.1

DC_USERS
可能是执行grant xxxx to user的时候,这个user正在登陆数据库。
相关BUG
Document 4604972.8 Bug 4604972 - Deadlock on dc_users by Concurrent Grant/Revoke - fixed in 11.1.0.6
Document 6143420.8 Bug 6143420 - Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"- fixed in 10.2.0.5 and 11.1.0.6  DC_OBJECTS
Document 12772404.8 Bug 12772404 - Significant "row cache objects" latch contention when using VPD

DC_OBJECT_IDS
相关BUG
Document 11693365.8 Bug 11693365 - Concurrent Drop table and Select on Reference constraint table hangs(deadlock) - fixed in 12.1


DC_SEGMENTS,DC_ROLLBACK_SEGMENTS
段正在进行分配操作,需要查看持有锁的session具体在做什么。

DC_TABLE_SCNS
相关BUG
Document 5756769.8 Bug 5756769 - Deadlock between Create MVIEW and DML - fixed in 10.2.0.5 ,11.1.07 and 11.2.0.1

DC_AWR_CONTROL
相关BUG
Document 6004916.8 Bug 6004916 - Hang involving row cache enqueues in RAC (ORA-4021) - fixed in 102.0.5 and 11.1.0.6
Document 8666117.8 Bug 8666117 - High row cache latch contention in RAC - fixed in 11.2.0.2 and 12.1
Document 9866045.8 Bug 9866045 - Long wait on 'wait for master scn' in LCK causing long row cache lock waits - fixed in 12.1



systemstat举例:

Oracle process number: 77
Unix process pid: 10846, image: oracle@cpdb4532

*** 2011-05-13 08:08:58.775
*** SERVICE NAME:(ALFCMR_SERVICE) 2011-05-13 08:08:58.775
*** SESSION ID:(1076.796) 2011-05-13 08:08:58.775
> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<
row cache enqueue: session: 0x1df57ade8, mode: N, request: S

上面内容显示了进程信息、session信息,目前没有持有锁(mode: N),请求共享模式锁(request: S)


PROCESS 77
----------------------------------------
.
.
----------------------------------------
SO: 0x1cdf11958, type: 50, owner: 0x17d198288, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=0x1df57ade8 object=0x1dc9a5d30, request=S
savepoint=0x87b70d
row cache parent object: address=0x1dc9a5d30 cid=7(dc_users)

上面信息显示该进程请求的是dc_users


PROCESS 218:
----------------------------------------
.
.
SO: 0x1cdf118f8, type: 50, owner: 0x1ceb0f178, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=0x1da54cf68 object=0x1dc9a5d30, request=X
savepoint=0x11e
row cache parent object: address=0x1dc9a5d30 cid=7(dc_users)

通过object=0x1dc9a5d30等信息,可以发现另有一个进程218请求dc_users的排它锁。


PROCESS 164:
----------------------------------------
.
.
O/S info: user: u1m, term: , ospid: 1234, machine: cpc44711
program:
last wait for 'SQL*Net message from client' blocking sess=0x(nil) seq=36289 wait_time=6943 seconds since wait started=2539
driver id=54435000, #bytes=1, =0
.
.
SO: 0x1cdf11418, type: 50, owner: 0x1ccc26120, flag: INIT/-/-/0x00
row cache enqueue: count=2 session=0x1df578318 object=0x1dc9a5d30, mode=S
savepoint=0xb1bd8e
row cache parent object: address=0x1dc9a5d30 cid=7(dc_users)
hash=fc968070 typ=11 transaction=(nil) flags=00000002
own=0x1dc9a5e00[0x1cdf11448,0x1cdf11448] wat=0x1dc9a5e10[0x1cdf11928,0x17d5192e0] mode=S

继续查看,会发现进程164持有的dc_users的共享锁(mode=S),而seconds since wait started=2539说明它持有这个队列已经很长时间了。



systemstat举例2:

Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /opt/oracle10/product/10.2.0
System name: SunOS
Node name: saecopt51
Release: 5.10
Version: Generic_144488-04
Machine: sun4v
Instance name: PORT_V16
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 6196, image: oracle@sae (MMON)
.
.

PROCESS 18:
----------------------------------------
.
.
last wait for 'ksdxexeotherwait' wait_time=0.000013 sec, seconds since wait started=6
.
.
SO: 39bf1f0e8, type: 50, owner: 3980783a0, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=3be37ea80 object=39a79f090, request=S
savepoint=0x41f0ae
row cache parent object: address=39a79f090 cid=22(dc_awr_control)
hash=6f60197e typ=9 transaction=3bc39f560 flags=0000002a
own=39a79f160[39bf1f178,39bf1f178] wat=39a79f170[39bf1f118,39bf1f118] mode=X

上面信息支出MMON进程正字啊请求以share模式获取dc_awr_control上的锁。


PROCESS 269:
----------------------------------------
.
.

waiting for 'SGA: allocation forcing component growth' wait_time=0, seconds since wait started=3

.
.
SO: 39bf1f148, type: 50, owner: 3bc39f560, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=3be1b7c98 object=39a79f090, mode=X
savepoint=0x41efe8
row cache parent object: address=39a79f090 cid=22(dc_awr_control)
hash=6f60197e typ=9 transaction=3bc39f560 flags=0000002a
own=39a79f160[39bf1f178,39bf1f178] wat=39a79f170[39bf1f118,39bf1f118] mode=X
.
.
通过object=39a79f090查找,发现269进程以排他模式锁住了dc_awr_control
而这个269进程正在进行SGA RESIZE操作。
通过AWR可以发现以下信息:
Top 5 Timed Events                                         Avg %Total              
~~~~~~~~~~~~~~~~~~                                        wait   Call              
Event                                 Waits    Time (s)   (ms)   Time Wait Class   
------------------------------ ------------ ----------- ------ ------ ----------   
SGA: allocation forcing compon   42,067,317      38,469      1    7.6      Other   
CPU time                                          2,796           0.6              
db file sequential read             132,906         929      7    0.2   User I/O   
latch free                        4,282,858         704      0    0.1      Other   
log file switch (checkpoint in          904         560    620    0.1 Configurat   
          -------------------------------------------------------------      


 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26239116/viewspace-1076285/,如需转载,请注明出处,否则将追究法律责任。

上一篇: resource manager
请登录后发表评论 登录
全部评论

注册时间:2012-11-12

  • 博文量
    94
  • 访问量
    308973