環境：
主機：HPUX IA64
資料庫：10.2.0.4 沒有打過任何補丁

現象：
客戶反應系統慢，無法登入等情況

檢查：
從ash報告中，可以看到如下資訊
Top Event P1/P2/P3 Values
Event % Event P1 Value, P2 Value, P3 Value % Activity Parameter 1 Parameter 2 Parameter 3
row cache lock 91.88 "7","0","3" 91.33 cache id mode request
cursor: pin S wait on X 7.03 "2747636884","0","12884901920" 0.55 idn value where|sleeps

SQL> col name format a30
SQL> col parameter1 format a20
SQL> col parameter2 format a20
SQL> col parameter3 format a20
SQL> select event#,NAME,PARAMETER1,PARAMETER2,PARAMETER3 from v$event_name where name='row cache lock';
EVENT# NAME PARAMETER1 PARAMETER2 PARAMETER3
---------- ------------------------------ -------------------- -------------------- --------------------
208 row cache lock cache id mode request
SQL> select parameter,gets,getmisses,MODIFICATIONS from v$rowcache where cache#=7;
PARAMETER GETS GETMISSES MODIFICATIONS
-------------------------------- ---------- ---------- -------------
dc_users 10142409 67 0
dc_users 0 0 0
dc_users 237459 35 0
dc_users 0 0 0

我們這裡看到是dc_users並不是常見的dc_sequences ，也就不是由於sequences 的cache小引起的row cache lock.這時初步判斷是bug引起的。

使用oradebug工具對系統做systemstate 266，下面是使用ass109.awk對trace檔案進行分析的結果
[oracle@r11g ~]$ awk -f /home/oracle/ass109.awk /home/oracle/ora10g_ora_19040.trc
Starting Systemstate 1
..............................................................................
...............................................................................
...............................................................................
...............................................................................
.................................................
Ass.Awk Version 1.0.9 - Processing /home/oracle/ora10g_ora_19040.trc

System State 1
~~~~~~~~~~~~~~~~
1:
2: waiting for 'pmon timer' wait
3: waiting for 'rdbms ipc message' wait
4: waiting for 'rdbms ipc message' wait
5: waiting for 'rdbms ipc message' wait
6: waiting for 'rdbms ipc message' wait
7: waiting for 'rdbms ipc message' wait
8: waiting for 'smon timer' wait
9: waiting for 'rdbms ipc message' wait
10: last wait for 'rdbms ipc message'
11: waiting for 'rdbms ipc message' wait
12: waiting for 'rdbms ipc message' wait
13:
14:
15: waiting for 'row cache lock' [Rcache object=c00000027e9059a8,] wait
Cmd: Select
16: waiting for 'Streams AQ: qmn coordinator idle wait' wait
17: waiting for 'row cache lock' [Rcache object=c00000027e9059a8,] wait
Cmd: Select
18: waiting for 'SQL*Net message from client' wait
19: waiting for 'row cache lock' [Rcache object=c00000027e9059a8,] wait
20: last wait for 'cursor: pin S wait on X'[Rcache object=c00000027e9059a8,]
21: waiting for 'jobq slave wait' wait
22: waiting for 'SQL*Net message from client' wait
23: waiting for 'SQL*Net message from client' wait
24: waiting for 'SQL*Net message from client' wait
25: waiting for 'SQL*Net message from client' wait
26: waiting for 'SQL*Net message from client' wait
27: waiting for 'SQL*Net message from client' wait
28: waiting for 'SQL*Net message from client' wait
29: waiting for 'SQL*Net message from client' wait
30: waiting for 'jobq slave wait' wait
31: waiting for 'SQL*Net message from client' wait
32: waiting for 'SQL*Net message from client' wait
Cmd: Update
33:
35: waiting for 'SQL*Net message from client' wait
36: for 'Streams AQ: waiting for time management or cleanup tasks' wait
37: waiting for 'SQL*Net message from client' wait
38: waiting for 'SQL*Net message from client' wait
39: waiting for 'SQL*Net message from client' wait
40: waiting for 'SQL*Net message from client' wait
41: waiting for 'jobq slave wait' wait
42: waiting for 'SQL*Net message from client' wait
43: waiting for 'SQL*Net message from client' wait
44: waiting for 'SQL*Net message from client' wait
45: waiting for 'SQL*Net message from client' wait
46: waiting for 'SQL*Net message from client' wait
47: waiting for 'SQL*Net message from client' wait
48: waiting for 'SQL*Net message from client' wait
49: waiting for 'SQL*Net message from client' wait
Cmd: PL/SQL Execute
50: waiting for 'SQL*Net message from client' wait
51: waiting for 'SQL*Net message from client' wait
52: waiting for 'SQL*Net message from client' wait
53: waiting for 'SQL*Net message from client' wait
54: waiting for 'SQL*Net message from client' wait
55: waiting for 'SQL*Net message from client' wait
56: waiting for 'SQL*Net message from client' wait
57: waiting for 'SQL*Net message from client' wait
58: waiting for 'SQL*Net message from client' wait
59: waiting for 'SQL*Net message from client' wait
60: waiting for 'SQL*Net message from client' wait
61: waiting for 'SQL*Net message from client' wait
62: waiting for 'SQL*Net message from client' wait
63: waiting for 'SQL*Net message from client' wait
64: waiting for 'SQL*Net message from client' wait
65: waiting for 'SQL*Net message from client' wait
66: waiting for 'SQL*Net message from client' wait
67: waiting for 'SQL*Net message from client' wait
68: waiting for 'SQL*Net message from client' wait
69: waiting for 'jobq slave wait' wait
70: waiting for 'SQL*Net message from client' wait
71: waiting for 'SQL*Net message from client' wait
72: waiting for 'SQL*Net message from client' wait
73: waiting for 'jobq slave wait' wait
74: waiting for 'SQL*Net message from client' wait
75: waiting for 'SQL*Net message from client' wait
76: waiting for 'SQL*Net message from client' wait
77: waiting for 'SQL*Net message from client' wait
78: waiting for 'SQL*Net message from client' wait
79: waiting for 'SQL*Net message from client' wait
80: waiting for 'SQL*Net message from client' wait
81: waiting for 'SQL*Net message from client' wait
82: waiting for 'SQL*Net message from client' wait
83: waiting for 'SQL*Net message from client' wait
84: waiting for 'SQL*Net message from client' wait
85: waiting for 'SQL*Net message from client' wait
86: waiting for 'SQL*Net message from client' wait
87: waiting for 'SQL*Net message from client' wait
88: waiting for 'SQL*Net message from client' wait
89: waiting for 'Streams AQ: qmn slave idle wait' wait
90: waiting for 'SQL*Net message from client' wait
91: waiting for 'SQL*Net message from client' wait
Cmd: Update
92: waiting for 'SQL*Net message from client' wait
93: waiting for 'SQL*Net message from client' wait
94: waiting for 'SQL*Net message from client' wait
95: waiting for 'SQL*Net message from client' wait
Cmd: Update
97: waiting for 'SQL*Net message from client' wait
99: waiting for 'SQL*Net message from client' wait
100:waiting for 'SQL*Net message from client' wait
101:waiting for 'SQL*Net message from client' wait
102:waiting for 'SQL*Net message from client' wait
103:waiting for 'SQL*Net message from client' wait
104:waiting for 'SQL*Net message from client' wait
105:waiting for 'SQL*Net message from client' wait
106:waiting for 'SQL*Net message from client' wait
107:waiting for 'SQL*Net message from client' wait
Cmd: Insert
108:waiting for 'SQL*Net message from client' wait
109:waiting for 'SQL*Net message from client' wait
110:waiting for 'SQL*Net message from client' wait
111:waiting for 'SQL*Net message from client' wait
112:waiting for 'SQL*Net message from client' wait
113:last wait for 'ksdxexeotherwait'
114:waiting for 'SQL*Net message from client' wait
115:waiting for 'SQL*Net message from client' wait
116:waiting for 'SQL*Net message from client' wait
117:waiting for 'SQL*Net message from client' wait
118:waiting for 'SQL*Net message from client' wait
119:waiting for 'SQL*Net message from client' wait
Cmd: Insert
120:waiting for 'SQL*Net message from client' wait
121:waiting for 'SQL*Net message from client' wait
122:waiting for 'SQL*Net message from client' wait
123:waiting for 'SQL*Net message from client' wait
124:waiting for 'SQL*Net message from client' wait
Cmd: Insert
125:waiting for 'SQL*Net message from client' wait
126:waiting for 'SQL*Net message from client' wait
128:waiting for 'SQL*Net message from client' wait
129:waiting for 'SQL*Net message from client' wait
130:waiting for 'SQL*Net message from client' wait
131:waiting for 'SQL*Net message from client' wait
Cmd: Delete
133:waiting for 'SQL*Net message from client' wait
134:waiting for 'SQL*Net message from client' wait
136:waiting for 'SQL*Net message from client' wait
137:waiting for 'SQL*Net message from client' wait
138:waiting for 'db file scattered read' (22,26bed,10) wait
Cmd: Select
139:waiting for 'SQL*Net message from client' wait
140:waiting for 'SQL*Net message from client' wait
141:waiting for 'SQL*Net message from client' wait
142:waiting for 'SQL*Net message from client' wait
143:waiting for 'SQL*Net message from client' wait
144:waiting for 'SQL*Net message from client' wait
145:waiting for 'SQL*Net message from client' wait
146:waiting for 'SQL*Net message from client' wait
147:waiting for 'SQL*Net message from client' wait
148:waiting for 'SQL*Net message from client' wait
149:waiting for 'SQL*Net message from client' wait
150:waiting for 'SQL*Net message from client' wait
Cmd: Update
151:waiting for 'SQL*Net message from client' wait
152:waiting for 'SQL*Net message from client' wait
153:waiting for 'SQL*Net message from client' wait
154:waiting for 'SQL*Net message from client' wait
155:waiting for 'SQL*Net message from client' wait
156:waiting for 'SQL*Net message from client' wait
157:waiting for 'SQL*Net message from client' wait
158:waiting for 'SQL*Net message from client' wait
159:waiting for 'SQL*Net message from client' wait
160:waiting for 'SQL*Net message from client' wait
161:waiting for 'SQL*Net message from client' wait
162:waiting for 'SQL*Net message from client' wait
163:waiting for 'SQL*Net message from client' wait
164:waiting for 'SQL*Net message from client' wait
165:waiting for 'SQL*Net message from client' wait
166:waiting for 'SQL*Net message from client' wait
167:waiting for 'SQL*Net message from client' wait
168:waiting for 'SQL*Net message from client' wait
169:waiting for 'SQL*Net message from client' wait
170:waiting for 'SQL*Net message from client' wait
171:waiting for 'SQL*Net message from client' wait
172:waiting for 'SQL*Net message from client' wait
173:waiting for 'SQL*Net message from client' wait
174:waiting for 'SQL*Net message from client' wait
175:waiting for 'SQL*Net message from client' wait
176:waiting for 'SQL*Net message from client' wait
177:waiting for 'SQL*Net message from client' wait
178:waiting for 'SQL*Net message from client' wait
179:waiting for 'SQL*Net message from client' wait
180:waiting for 'SQL*Net message from client' wait
181:waiting for 'SQL*Net message from client' wait
182:waiting for 'SQL*Net message from client' wait
183:waiting for 'SQL*Net message from client' wait
184:waiting for 'SQL*Net message from client' wait
185:waiting for 'SQL*Net message from client' wait
186:waiting for 'SQL*Net message from client' wait
187:waiting for 'SQL*Net message from client' wait
188:waiting for 'SQL*Net message from client' wait
189:waiting for 'SQL*Net message from client' wait
190:waiting for 'SQL*Net message from client' wait
191:waiting for 'SQL*Net message from client' wait
192:waiting for 'SQL*Net message from client' wait
193:waiting for 'SQL*Net message from client' wait
194:waiting for 'SQL*Net message from client' wait
195:waiting for 'SQL*Net message from client' wait
196:waiting for 'SQL*Net message from client' wait
197:waiting for 'SQL*Net message from client' wait
198:waiting for 'SQL*Net message from client' wait
199:waiting for 'SQL*Net message from client' wait
200:waiting for 'SQL*Net message from client' wait
201:waiting for 'SQL*Net message from client' wait
202:waiting for 'SQL*Net message from client' wait
203:waiting for 'SQL*Net message from client' wait
Cmd: Insert
204:waiting for 'SQL*Net message from client' wait
205:waiting for 'SQL*Net message from client' wait
206:waiting for 'SQL*Net message from client' wait
207:waiting for 'SQL*Net message from client' wait
Cmd: Update
208:waiting for 'SQL*Net message from client' wait
209:waiting for 'SQL*Net message from client' wait
210:waiting for 'SQL*Net message from client' wait
211:waiting for 'SQL*Net message from client' wait
212:waiting for 'SQL*Net message from client' wait
213:waiting for 'SQL*Net message from client' wait
214:waiting for 'SQL*Net message from client' wait
215:waiting for 'SQL*Net message from client' wait
216:waiting for 'SQL*Net message from client' wait
217:waiting for 'SQL*Net message from client' wait
218:waiting for 'SQL*Net message from client' wait
219:waiting for 'SQL*Net message from client' wait
220:waiting for 'SQL*Net message from client' wait
221:waiting for 'SQL*Net message from client' wait
222:waiting for 'SQL*Net message from client' wait
Cmd: PL/SQL Execute
223:waiting for 'SQL*Net message from client' wait
224:waiting for 'SQL*Net message from client' wait
225:waiting for 'SQL*Net message from client' wait
226:waiting for 'SQL*Net message from client' wait
227:waiting for 'SQL*Net message from client' wait
228:waiting for 'SQL*Net message from client' wait
229:waiting for 'SQL*Net message from client' wait
230:waiting for 'SQL*Net message from client' wait
231:waiting for 'SQL*Net message from client' wait
232:waiting for 'SQL*Net message from client' wait
233:waiting for 'SQL*Net message from client' wait
234:waiting for 'SQL*Net message from client' wait
235:waiting for 'SQL*Net message from client' wait
236:waiting for 'SQL*Net message from client' wait
Cmd: Insert
237:waiting for 'SQL*Net message from client' wait
238:waiting for 'SQL*Net message from client' wait
239:waiting for 'SQL*Net message from client' wait
240:waiting for 'SQL*Net message from client' wait
241:waiting for 'SQL*Net message from client' wait
242:waiting for 'SQL*Net message from client' wait
243:waiting for 'SQL*Net message from client' wait
244:waiting for 'SQL*Net message from client' wait
245:waiting for 'SQL*Net message from client' wait
246:waiting for 'SQL*Net message from client' wait
247:waiting for 'SQL*Net message from client' wait
248:waiting for 'SQL*Net message from client' wait
249:waiting for 'SQL*Net message from client' wait
250:waiting for 'SQL*Net message from client' wait
251:waiting for 'SQL*Net message from client' wait
252:waiting for 'SQL*Net message from client' wait
253:waiting for 'SQL*Net message from client' wait
254:waiting for 'SQL*Net message from client' wait
255:waiting for 'SQL*Net message from client' wait
256:waiting for 'SQL*Net message from client' wait
257:waiting for 'SQL*Net message from client' wait
258:waiting for 'SQL*Net message from client' wait
259:waiting for 'SQL*Net message from client' wait
260:waiting for 'SQL*Net message from client' wait
261:waiting for 'SQL*Net message from client' wait
262:last wait for 'SQL*Net message to client'
263:waiting for 'SQL*Net message from client' wait
264:waiting for 'SQL*Net message from client' wait
265:waiting for 'SQL*Net message from client' wait
267:waiting for 'SQL*Net message from client' wait
268:waiting for 'SQL*Net message from client' wait
269:waiting for 'SQL*Net message from client' wait
270:waiting for 'SQL*Net message from client' wait
271:waiting for 'SQL*Net message from client' wait
272:waiting for 'SQL*Net message from client' wait
Cmd: Update
273:waiting for 'SQL*Net message from client' wait
274:waiting for 'SQL*Net message from client' wait
275:waiting for 'SQL*Net message from client' wait
276:waiting for 'SQL*Net message from client' wait
277:waiting for 'SQL*Net message from client' wait
278:waiting for 'SQL*Net message from client' wait
279:waiting for 'SQL*Net message from client' wait
280:waiting for 'SQL*Net message from client' wait
281:waiting for 'SQL*Net message from client' wait
282:waiting for 'SQL*Net message from client' wait
283:waiting for 'SQL*Net message from client' wait
284:waiting for 'SQL*Net message from client' wait
285:waiting for 'SQL*Net message from client' wait
Cmd: Insert
286:waiting for 'SQL*Net message from client' wait
287:waiting for 'SQL*Net message from client' wait
288:waiting for 'SQL*Net message from client' wait
Cmd: Select
289:waiting for 'SQL*Net message from client' wait
290:waiting for 'SQL*Net message from client' wait
291:waiting for 'SQL*Net message from client' wait
292:waiting for 'SQL*Net message from client' wait
293:waiting for 'SQL*Net message from client' wait
294:waiting for 'SQL*Net message from client' wait
295:waiting for 'SQL*Net message from client' wait
296:waiting for 'SQL*Net message from client' wait
297:waiting for 'SQL*Net message from client' wait
298:waiting for 'SQL*Net message from client' wait
299:waiting for 'SQL*Net message from client' wait
300:waiting for 'SQL*Net message from client' wait
301:waiting for 'SQL*Net message from client' wait
302:waiting for 'SQL*Net message from client' wait
303:waiting for 'SQL*Net message from client' wait
304:waiting for 'SQL*Net message from client' wait
305:waiting for 'SQL*Net message from client' wait
306:waiting for 'SQL*Net message from client' wait
307:waiting for 'SQL*Net message from client' wait
308:waiting for 'SQL*Net message from client' wait
309:waiting for 'SQL*Net message from client' wait
310:waiting for 'SQL*Net message from client' wait
311:waiting for 'SQL*Net message from client' wait
312:waiting for 'SQL*Net message from client' wait
313:waiting for 'SQL*Net message from client' wait
314:waiting for 'SQL*Net message from client' wait
315:waiting for 'SQL*Net message from client' wait
316:waiting for 'SQL*Net message from client' wait
317:waiting for 'SQL*Net message from client' wait
318:waiting for 'SQL*Net message from client' wait
319:waiting for 'SQL*Net message from client' wait
320:waiting for 'SQL*Net message from client' wait
321:waiting for 'SQL*Net message from client' wait
322:waiting for 'SQL*Net message from client' wait
323:waiting for 'SQL*Net message from client' wait
Cmd: Insert
324:waiting for 'SQL*Net message from client' wait
325:waiting for 'SQL*Net message from client' wait
326:waiting for 'SQL*Net message from client' wait
327:waiting for 'SQL*Net message from client' wait
328:waiting for 'SQL*Net message from client' wait
329:waiting for 'SQL*Net message from client' wait
330:waiting for 'SQL*Net message from client' wait
331:waiting for 'SQL*Net message from client' wait
332:waiting for 'SQL*Net message from client' wait
334:waiting for 'SQL*Net message from client' wait
336:waiting for 'SQL*Net message from client' wait
337:waiting for 'SQL*Net message from client' wait
338:waiting for 'SQL*Net message from client' wait
339:waiting for 'SQL*Net message from client' wait
340:waiting for 'SQL*Net message from client' wait
341:waiting for 'SQL*Net message from client' wait
342:waiting for 'SQL*Net message from client' wait
343:waiting for 'SQL*Net message from client' wait
344:waiting for 'SQL*Net message from client' wait
345:waiting for 'SQL*Net message from client' wait
346:waiting for 'SQL*Net message from client' wait
347:waiting for 'SQL*Net message from client' wait
348:waiting for 'SQL*Net message from client' wait
349:waiting for 'SQL*Net message from client' wait
350:waiting for 'SQL*Net message from client' wait
351:waiting for 'SQL*Net message from client' wait
352:waiting for 'SQL*Net message from client' wait
Cmd: PL/SQL Execute
354:waiting for 'SQL*Net message from client' wait
355:waiting for 'SQL*Net message from client' wait
356:waiting for 'SQL*Net message from client' wait
357:waiting for 'SQL*Net message from client' wait
358:waiting for 'SQL*Net message from client' wait
359:waiting for 'SQL*Net message from client' wait
360:waiting for 'SQL*Net message from client' wait
362:waiting for 'SQL*Net message from client' wait
Cmd: Update
363:waiting for 'SQL*Net message from client' wait
364:waiting for 'SQL*Net message from client' wait
Cmd: Update
365:waiting for 'SQL*Net message from client' wait
Cmd: PL/SQL Execute
366:waiting for 'SQL*Net message from client' wait
368:waiting for 'SQL*Net message from client' wait
Cmd: Update
373:waiting for 'SQL*Net message from client' wait
374:waiting for 'SQL*Net message from client' wait
375:waiting for 'SQL*Net message from client' wait
376:waiting for 'SQL*Net message from client' wait
377:waiting for 'SQL*Net message from client' wait
378:waiting for 'SQL*Net message from client' wait
379:waiting for 'SQL*Net message from client' wait
380:waiting for 'SQL*Net message from client' wait
Blockers
~~~~~~~~

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

Resource Holder State
Rcache object=c00000027e9059a8, ??? Blocker

Object Names
~~~~~~~~~~~~
Rcache object=c00000027e9059a8,

955236 Lines Processed.
[oracle@r11g ~]$

從原始trace檔案中，獲取process 15的詳細資訊
PROCESS 15:
----------------------------------------
SO: c00000027f798fa0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=15, calls cur/top: c00000027fc92e30/c00000027fc949f8, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 171
last post received-location: kqrget: post after requeueing
last process to post me: c00000027f7c2270 148 0
last post sent: 0 0 0
last post sent-location: No post
last process posted by me: none
(latch info) wait_event=0 bits=0
Process Group: DEFAULT, pseudo proc: c00000027f8bd450
O/S info: user: oracle, term: UNKNOWN, ospid: 17056
OSD pid info: Unix process pid: 17056, image: oracle@tjbzxt-2
Short stack dump:
ksdxfstk()+48<-ksdxcb()+5776<-sspuser()+640<-<kernel><-_pw_wait()+48<-pw_wait()+128<-sskgpwwait()+384<-skgpwwait()+208<-ksliwat()+1744<-kslwaitns_timed()+112<-kskthbwt()+368<-kslwait()+640<-kqrget()+1264<-kqrpre1()+1376<-ktatminextsz()+496<-qerhjComputeFanoutAndBPS()+688<-kkejnc()+6992<-kkojnp()+101840<-kkocnp()+512<-kkooqb()+5504<-kkoqbc()+4400<-apakkoqb()+368<-apaqbdDescendents()+768<-apaqbd()+256<-kkqctCostTransfQB()+208<-kkqctdrvSU()+3392<-nsotruns()+560<-nsotruns()+256<-nsoqbc()+1056<-kkqctdrvTD()+1264<-kkqdrv()+2192<-kkqctdrvIT()+992<-apadrv()+832<-opitca()+3040<-kksLoadChild()+15376<-kxsGetRuntimeLock()+2656<-kksfbc()+16640<-kkspsc0()+1984<-kksParseCursor()+352<-opiosq0()+4320<-kpooprx()+416<-kpoal8()+1248<-opiodr()+2128<-ttcpip()+1680<-opitsk()+2336<-opiino()+1840<-opiodr()+2128<-opidrv()+1088<-sou2o()+336<-opimai_real()+224<-main()+368<-main_opd_entry()+80
Dump of memory from 0xC00000027F747448 to 0xC00000027F747650
C00000027F747440 00000004 00000000 [........]
C00000027F747450 C0000002 7D450A10 00000010 000313A7 [....}E..........]
C00000027F747460 C0000002 7FC949F8 00000003 000313A7 [......I.........]
C00000027F747470 C0000002 7FF78D40 0000000B 000313A7 [.......@........]
C00000027F747480 C0000002 7FB96CB0 00000004 0003129B [......l.........]
C00000027F747490 00000000 00000000 00000000 00000000 [................]
Repeat 27 times
----------------------------------------
SO: c00000027fb96cb0, type: 4, owner: c00000027f798fa0, flag: INIT/-/-/0x00
(session) sid: 539 trans: 0000000000000000, creator: c00000027f798fa0, flag: (41) USR/- BSY/-/-/-/-/-
DID: 0001-000F-000000FD, short-term DID: 0000-0000-00000000
txn branch: 0000000000000000
oct: 3, prv: 0, sql: c000000271faf4e0, psql: c00000026f58e720, user: 57/CCATSUPT
service name: SYS$USERS
O/S info: user: root, term: unknown, ospid: 1234, machine: tjbzxt-2
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
waiting for 'row cache lock' blocking sess=0x0000000000000000 seq=200 wait_time=0 seconds since wait started=8
cache id=7, mode=0, request=3
Dumping Session Wait History
for 'row cache lock' count=1 wait_time=2224742
cache id=7, mode=0, request=3
for 'row cache lock' count=1 wait_time=2937051
cache id=7, mode=0, request=3
for 'row cache lock' count=1 wait_time=2940382
cache id=7, mode=0, request=3
for 'latch: row cache objects' count=1 wait_time=14
address=c0000002788337e0, number=c8, tries=1
for 'latch: row cache objects' count=1 wait_time=12307
address=c0000002788337e0, number=c8, tries=0
for 'row cache lock' count=1 wait_time=43952
cache id=7, mode=0, request=3
for 'row cache lock' count=1 wait_time=2939440
cache id=7, mode=0, request=3
for 'row cache lock' count=1 wait_time=2939448
cache id=7, mode=0, request=3
for 'row cache lock' count=1 wait_time=2939421
cache id=7, mode=0, request=3
for 'row cache lock' count=1 wait_time=2939443
cache id=7, mode=0, request=3
temporary object counter: 0

這裡ktatminextsz()被呼叫。
----------------------------------------

諮詢oracle原廠給予的答覆：
KM SEARCH
---------------
Keyword =ROW CACHE LOCK
1.Bug 6143420 Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"
Versions confirmed as being affected

10.2.0.4
10.2.0.3
This issue is fixed in

11.1.0.6 (Base Release)
10.2.0.5 (Server Patch Set)
10.2.0.4 Patch 18 on Windows Platforms
Deadlocks between "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"
usually reported by "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK" messages.

INTERNAL PROBLEM DESCRIPTION:
ktatminextsz() calls ksugus() to get top session's user id. This gives wrong
result for recursive sqls whose user id is SYS.

INTERNAL FIX DESCRIPTION:
Change ksugus() to ksugcs() to get current session.

基本可以確定，這個問題是bug引起的。
下面是引述MOC BUG 6143420的文件。也可以參考Troubleshooting: "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! " (文件 ID 278316.1)

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
Bug 6143420 Deadlock involving "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"

This note gives a brief overview of bug 6143420.
The content was last updated on: 24-NOV-2009
Click here for details of each of the sections below.
Affects:

Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions BELOW 11.1
Versions confirmed as being affected
10.2.0.4
10.2.0.3
Platforms affected Generic (all / most platforms affected)
Fixed:

This issue is fixed in
11.1.0.6 (Base Release)
10.2.0.5 (Server Patch Set)
10.2.0.4 Patch 18 on Windows Platforms
Symptoms:

Related To:

Deadlock
Hang (Process Hang)
Waits for "ROW CACHE LOCK"
Waits for "CURSOR: PIN S WAIT ON X"
RAC (Real Application Clusters) / OPS
Instance Startup
Description

This fix addresses 2 scenarios :
1) Startup of a RAC instance may hang with a deadlock on a dc_user row cache entry.

2) Deadlocks between "ROW CACHE LOCK" on dc_users AND "CURSOR: PIN S WAIT ON X"
usually reported by "WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK" messages.

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. For questions about this bug please consult Oracle Support.
References

Bug:6143420 (This link will only work for PUBLISHED bugs)
Note:245840.1 Information on the sections in this article

解決方案：
從下載patch 6143420
給資料庫打補丁

打完補丁後，該問題解決。

記一次row cache lock引起的效能問題分析處理

相關文章