问题又现

Thu Jan 12 23:51:54 2012

MMNL absent for 1204 secs; Foregrounds taking over

Fri Jan 13 00:22:20 2012

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=139

System State dumped to trace file /oracle/admin/sptdi/udump/sptdi1_ora_251018.trc

Fri Jan 13 00:24:10 2012

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=74

System State dumped to trace file /oracle/admin/sptdi/udump/sptdi1_ora_230082.trc

Fri Jan 13 00:31:05 2012

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=67

System State dumped to trace file /oracle/admin/sptdi/udump/sptdi1_ora_354096.trc

Fri Jan 13 00:34:15 2012

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=23

System State dumped to trace file /oracle/admin/sptdi/bdump/sptdi1_reco_456690.trc

Fri Jan 13 01:03:32 2012

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=181

System State dumped to trace file /oracle/admin/sptdi/udump/sptdi1_ora_213986.trc

Fri Jan 13 01:13:37 2012

MMNL absent for 6051 secs; Foregrounds taking over
拿出来里面的文件，分析，把大概问题锁定在一个Bug  7649406上。

下面是trace 文件的部分内容。

*** 2012-01-13 00:24:10.658

*** ACTION NAME:(/2012011221/cdrDDetail.txt.ctl) 2012-01-13 00:24:10.649

*** MODULE NAME:(SQL Loader Conventional Path Load) 2012-01-13 00:24:10.649

*** SERVICE NAME:(sptdi) 2012-01-13 00:24:10.649

*** SESSION ID:(939.24962) 2012-01-13 00:24:10.649

>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! <<<

row cache enqueue: session: 70000078233ef40, mode: N, request: X

这里可以看出，等待的会话是  70000078233ef40。

然后查找，这个会话，找到

 SO: 70000078233ef40, type: 4, owner: 700000787268410, flag: INIT/-/-/0x00

    (session) sid: 939 trans: 70000078ffac7b8, creator: 700000787268410, flag: (100041) USR/- BSY/-/-/-/-/-

              DID: 0001-004A-000035A0, short-term DID: 0001-004A-000035A1

              txn branch: 0

              oct: 2, prv: 0, sql: 7000006f2d38198, psql: 7000006f2d38198, user: 49/CDMA

会话当前的sql是上面红体，再找这个sql的内容。

SO: 70000064e827410, type: 53, owner: 70000078233ef40, flag: INIT/-/-/0x00

      LIBRARY OBJECT LOCK: lock=70000064e827410 handle=7000006f2d38198 mode=N

      call pin=0 session pin=0 hpc=0000 hlc=0000

      htl=70000064e827490[7000006526e4b90,700000739a82c10] htb=7000006526e4b90 ssga=7000006526e4618

      user=70000078233ef40 session=70000078233ef40 count=1 flags=[0000] savepoint=0x4f0efce3

      LIBRARY OBJECT HANDLE: handle=7000006f2d38198 mtx=7000006f2d382c8(2) cdp=2

      name=

INSERT INTO CDR_D_DETAIL   (CITY_ID,SYS_PRODUCT_CODE,MSC_CODE,STARTTIME,CALLTIME,CALLTIMEH,ENDTIME,PERIOD,SEQUENCE_NUMBER,CALL_LENGTH,USER_NUMBER,IMSI,INI_BTS,INI_CELL,INI_CHANNEL_NUM,LAST_BTS,LAST_CELL,LAST_CHANNEL_NUM,PSTN_IN_GATE,PSTN_OUT_GATE,ORIGINATE_NUMBER,OT_TYPE,REQ_SERVICE,CALL_FINAL_CLASS,CDMA_ROUND_TRIP_DELAY,TERMINATE_NUMBER,ACCESS_BTS,ACCESS_CELL,MSC_NO,CALL_ESTABLISHED,SEIZURE_CARRIER,ASSIGNED_CARRIER,ENDING_CARRIER,INIT_CCU,INIT_CE,LAST_CCU,LAST_CE,PSTN_TRUNK_GROUP,PSTN_MEMBER,SPEECH_HANDLER

      hash=778391fa29749665bde3155135f6a946 timestamp=01-10-2012 15:52:44

这个sql是用sqlldr加载的时候执行的，正常业务sql.

上面的内容是参数老白的：

http://www.oraclefans.cn/forum/showtopic.jsp?rootid=14815&CPages=1
如有版权问题，请不要找我。 

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

转载于:http://blog.itpub.net/25099483/viewspace-714874/