Shulou(Shulou.com)05/31 Report--

This article shows you what is the analysis of the performance problems caused by row cache lock. The content is concise and easy to understand. It will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

Environment:

Host: HPUX IA64

Database: 10.2.0.4 without any patches

Phenomenon:

Customer response system is slow, unable to log in, etc.

Check:

From the ash report, you can see the following information

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

What we see here is that dc_users is not a common dc_sequences, so it is not the row cache lock caused by the small cache of sequences. At this time, the preliminary judgment is that it is caused by bug.

Use the oradebug tool to do systemstate 266.The following is the result of using ass109.awk to analyze the trace file

[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:

Waiting for 'row cache lock' [Rcache object=c00000027e9059a8,] wait

Cmd: Select

16: waiting for 'Streams AQ: qmn coordinator idle wait' wait

Waiting for 'row cache lock' Rcache object=c00000027e9059a8, wait

Cmd: Select

18: waiting for 'SQL*Net message from client' wait

Waiting for 'row cache lock' Rcache object=c00000027e9059a8, wait

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' (22pr 26bedm 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 ~] $

Get the details of process 15 from the original trace file

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 0171

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.