WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=62
本帖最后由 wengtf 于 2014-1-1 17:27 编辑10.2.0.4 RAC ON Linux 64bit
alert 如下：
Tue Dec 31 22:40:34 2013
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=62
System State dumped to trace file /oracle/app/admin/rcs1prd/bdump/rcs1prd1_j001_24726.trc
Wed Jan 1 01:52:04 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=48
System State dumped to trace file /oracle/app/admin/rcs1prd/udump/rcs1prd1_ora_28904.trc
Wed Jan 1 14:39:07 2014
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=49
System State dumped to trace file /oracle/app/admin/rcs1prd/udump/rcs1prd1_ora_24471.trc
附件是3个trc：
系统里面却是在每天18点会对2个用户的表做统计信息收集。
oradebug hanganalyze 3：
oradebug setmypid
oradebug unlimit
oradebug hanganalyze 3
wait about 2 mins
oradebug hanganalyze 3
wait about 2 mins
oradebug hanganalyze 3
oradebug dump systemstate 266
Wait about 2 mins
oradebug dump systemstate 266
Wait about 2 mins
oradebug dump systemstate 266
SO: 0x283731138, type: 53, owner: 0x29c84c1f8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=283731138 handle=29e9a8470 mode=N
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x2837311b8 htb=0x2837306a0 ssga=0x28372fc88
user=29c84c1f8 session=29f820968 count=1 flags= savepoint=0x52c3ac87
the rest of the object was already dumped
----------------------------------------
SO: 0x283591358, type: 53, owner: 0x29c84c1f8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=283591358 handle=29d7a9dd0 mode=N
call pin=0x27c8a2268 session pin=(nil) hpc=0000 hlc=0000
htl=0x2835913d8 htb=0x283730970 ssga=0x28372fc88
user=29c84c1f8 session=29c84c1f8 count=1 flags=CBK savepoint=0x0
LIBRARY OBJECT HANDLE: handle=29d7a9dd0 mtx=0x29d7a9f00(0) cdp=0
namespace=CRSR flags=RON/KGHP/PN0/EXP/
kkkk-dddd-llll=0000-0001-0001 lock=N pin=X latch#=7 hpc=0002 hlc=0002
lwt=0x29d7a9e78 ltm=0x29d7a9e88
pwt=0x29d7a9e40 ptm=0x29d7a9e50
ref=0x29d7a9ea8 lnd=0x29d7a9ec0
LIBRARY OBJECT: object=263e96690
type=CRSR flags=EXS pflags= status=VALD load=0
DEPENDENCIES: count=4 size=16
TRANSLATIONS: count=1 size=16
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 206f5c330 263e967a8 I/P/A/-/- 0 NONE 00
6 23493b2a8 22ed70bc0 I/P/A/-/E 0 NONE 00
----------------------------------------
KGX Atomic Operation Log 0x22aac2860
Mutex 0x23493b1a8(865, 0) idn b5271bbd oper EXCL
Cursor Pin uid 865 efd 0 whr 1 slp 0
opr=3 pso=0x283591358 flg=0
pcs=0x23493b1a8 nxt=(nil) flg=35 cld=0 hd=0x29d7a9dd0 par=0x20cc4a4b0
ct=0 hsh=0 unp=(nil) unn=0 hvl=cc4a788 nhv=1 ses=0x29c84c1f8
hep=0x23493b230 flg=80 ld=1 ob=0x263e96690 ptr=0x22ed70bc0 fex=0x22ed6fed0
----------------------------------------
SO: 0x283738210, type: 53, owner: 0x29c84c1f8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=283738210 handle=27a1b6a28 mode=N
call pin=(nil) session pin=(nil) hpc=0000 hlc=0000
htl=0x283738290 htb=0x283730970 ssga=0x28372fc88
user=29c84c1f8 session=29c84c1f8 count=1 flags= savepoint=0x52c3ac87
LIBRARY OBJECT HANDLE: handle=27a1b6a28 mtx=0x27a1b6b58(0) cdp=1
name=
select /*OracleDatabaseImpl.ALL_NLS_PARAMS_QUERY*/
s.parameter, s.value
from nls_session_parameters s
union all
select d.parameter, d.value
from nls_database_parameters d
where not exists (select 1 from nls_session_parameters s2
where s2.parameter = d.parameter)
union all
select 'NLS_CHARSET_ID' parameter, to_char(NLS_CHARSET_ID(charset.value)) value
from (select value
from nls_database_parameters
where parameter = 'NLS_CHARACTERSET') charset
hash=a99c48c50672864e291176b6b5271bbd timestamp=01-01-2014 13:49:59
namespace=CRSR flags=RON/KGHP/TIM/PN0/LRG/DBN/
kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=7 hpc=0006 hlc=0006
lwt=0x27a1b6ad0 ltm=0x27a1b6ae0
pwt=0x27a1b6a98 ptm=0x27a1b6aa8
ref=0x27a1b6b00 lnd=0x27a1b6b18
LIBRARY OBJECT: object=20cc4a398
type=CRSR flags=EXS pflags= status=VALD load=0
CHILDREN: size=16
child# table reference handle
------ -------- --------- --------
0 23493af48 23493abb8 29d7a9dd0
DATA BLOCKS:
data# heap pointer status pins change whr
----- -------- -------- --------- ---- ------ ---
0 29dd532c8 20cc4a4b0 I/P/A/-/- 0 NONE 00
----------------------------------------
SO: 0x29f820968, type: 4, owner: 0x29f540408, flag: INIT/-/-/0x00
(session) sid: 826 trans: 0x2926d5dc0, creator: 0x29f540408, flag: (48110041) USR/- BSY/-/-/-/-/-
DID: 0001-003E-000340E2, short-term DID: 0001-003E-000340E3
txn branch: (nil)
oct: 3, prv: 0, sql: 0x29e9264b8, psql: 0x27b31a538, user: 0/SYS
service name: SYS$USERS
O/S info: user: oracle, term: UNKNOWN, ospid: 24726, machine: cs-db-1-pr
program: oracle@cs-db-1-pr (J001)
application name: DBMS_SCHEDULER, hash value=2478762354
action name: GATHER_STATS_JOB, hash value=930355498
last wait for 'ksdxexeotherwait' blocking sess=0x(nil) seq=732 wait_time=1 seconds since wait started=21
=0, =0, =0
22:40在做 收集统计信息 怀疑本身硬解析很多，当一些游标失效或者其他因素发挥作用时 都可能出现row cache lock和 cursor pin S on X
需要上面原帖几个时间点的AWR 31日做统计信息的awr，“INSERT INTO SYS.WRI$_ADV_PARAM...”祸首
Statistic Name Time (s) % of DB Time
sql execute elapsed time 6,823.49 99.98
sequence load elapsed time 3,407.75 49.93
dc_sequences 173 1 0
SELECT ORA_TQ_BASE$.NEXTVAL FROM DUAL 一次居然要117.51秒
Avg global enqueue get time (ms): 231.1
强烈 怀疑 RAC interconnect存在问题 Liu Maclean(刘相兵 发表于 2014-1-3 19:50 static/image/common/back.gif
Statistic Name Time (s) % of DB Time
sql execute elapsed time 6,823.49 99.98
sequence load elapsed t ...
这个RAC 不只一个库，其他2个数据库没有出现类似的问题，但我不确定是否是统计信息收集的job触发了此次你怀疑的interconnect 问题
页:
[1]