Oracle数据库数据恢复、性能优化

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › 系统中长期存在enq: DX - contention和。。。，怎么处理 ...
发新帖
武汉-SSH

316

积分

0

好友

0

主题

系统中长期存在enq: DX - contention和。。。，怎么处理？

1#
发表于 2012-2-2 22:37:54 | 查看: 16356| 回复: 15
系统中长期存在enq: DX - contention和SQL*Net more data to client，怎么处理？
未命名.jpg
未命名2.jpg
未命名3.jpg
2#
发表于 2012-2-2 22:50:50
请上传问题时段的 AWR 、ASH 、ADDM报告

回复 只看该作者 道具 举报

3#
发表于 2012-2-3 15:42:31
awr_report_14042_14043.zip (34.88 KB, 下载次数: 2359)

回复 只看该作者 道具 举报

4#
发表于 2012-2-3 16:00:02
ASH REPORT 也需要

同时强烈怀疑 网络 配置或设备存在问题， 可以的话做个RDA上传，参考

RDA 4 - Health Check / Validation Engine Guide (Doc ID 250262.1)

++ Download the RDA for your operating system version.

++ Run the RDA (rda.sh -T HCVE)

++ It will list the options,Now select the appropriate option according to the Database version

++ Then provide the path for Oracle home and JDK Home.

++ Then the results will be displayed and an html file is generated.Upload the generated HTML file.

回复 只看该作者 道具 举报

5#
发表于 2012-2-3 19:50:39
ASH_report_1328266473120.zip (3.65 KB, 下载次数: 2428)
addm_24611.zip (2.01 KB, 下载次数: 2402)

回复 只看该作者 道具 举报

6#
发表于 2012-2-3 20:30:37
Action Plan:

select * from V$GLOBAL_TRANSACTION;
select * from DBA_PENDING_TRANSACTIONS;

上传以上 输出

oradebug setmypid;
oradebug dump systemstate 266;
oradebug tracefile_name;

把 trace 压缩后上传

回复 只看该作者 道具 举报

7#
发表于 2012-2-3 21:07:59
DBA_PENDING_TRANSACTIONS 无返回
GLOBAL_TRANSACTION.zip (1.59 KB, 下载次数: 2423)

回复 只看该作者 道具 举报

8#
发表于 2012-2-3 21:15:10
cpmis_ora_1487062.tar.gz (4.62 MB, 下载次数: 1169)

回复 只看该作者 道具 举报

9#
发表于 2012-2-3 22:46:26
试用 ass.awk对systemstate dump做分析
  1. [oracle@vrh8 ~]$ awk -f ass109.awk.1 cpmis_ora_1487062.trc

  3. Starting Systemstate 1
  4. ..............................................................................
  5. ...............................................................................
  6. ...............................................................................
  7. .......................................................
  8. Ass.Awk Version 1.0.9 - Processing cpmis_ora_1487062.trc

  10. System State 1
  11. ~~~~~~~~~~~~~~~~
  12. 1:                                      
  13. 2:  waiting for 'pmon timer'            wait
  14. 3:  waiting for 'rdbms ipc message'     wait
  15. 4:  waiting for 'rdbms ipc message'     wait
  16. 5:  waiting for 'rdbms ipc message'     wait
  17. 6:  waiting for 'rdbms ipc message'     wait
  18. 7:  waiting for 'rdbms ipc message'     wait
  19. 8:  waiting for 'rdbms ipc message'     wait
  20. 9:  last wait for 'smon timer'         
  21. 10: waiting for 'rdbms ipc message'     wait
  22. 11: waiting for 'rdbms ipc message'     wait
  23. 12: waiting for 'rdbms ipc message'     wait
  24. 13: waiting for 'rdbms ipc message'     wait
  25. 14:                                    
  26. 15:                                    
  27. 16: waiting for 'SQL*Net message from dblink' wait
  28.      Cmd: Select
  29. 17: waiting for 'SQL*Net message from client' wait
  30. 18: waiting for 'Streams AQ: qmn coordinator idle wait' wait
  31. 19: waiting for 'SQL*Net message from client' wait
  32. 20: waiting for 'SQL*Net message from client' wait
  33. 21: last wait for 'ksdxexeotherwait'   
  34. 22: waiting for 'SQL*Net message from dblink' wait
  35.      Cmd: Select
  36. 23: last wait for 'SQL*Net more data to client'
  37.      Cmd: Select
  38. 24: waiting for 'SQL*Net message from dblink' wait
  39.      Cmd: Select
  40. 25: waiting for 'SQL*Net message from dblink' wait
  41.      Cmd: Select
  42. 26: waiting for 'jobq slave wait'       wait
  43. 27: waiting for 'SQL*Net message from client' wait
  44. 28: waiting for 'SQL*Net message from client' wait
  45. 29: for 'Streams AQ: waiting for time management or cleanup tasks' wait
  46. 30: waiting for 'Streams AQ: qmn slave idle wait' wait
  47. 31: waiting for 'SQL*Net message from client' wait
  48. 32: waiting for 'enq: DX - contention' [Enqueue DX-0000001A-00000000] wait
  49. 34: waiting for 'SQL*Net message from client' wait
  50. 35: waiting for 'SQL*Net message from client' wait
  51. 36: waiting for 'SQL*Net message from client' wait
  52. 37: waiting for 'SQL*Net message from client' wait
  53. 38: for 'Streams AQ: waiting for messages in the queue' wait
  54. 41: waiting for 'SQL*Net message from client' wait
  55.      Cmd: Insert
  56. 42: waiting for 'SQL*Net message from dblink' wait
  57.      Cmd: Select
  58. 43: waiting for 'SQL*Net message from client' wait
  59. 45: waiting for 'SQL*Net message from client' wait
  60. 47: waiting for 'SQL*Net message from dblink' wait
  61.      Cmd: Select
  62. 48: waiting for 'SQL*Net message from client' wait
  63. 49: waiting for 'SQL*Net message from dblink' wait
  64.      Cmd: Select
  65. 50: waiting for 'SQL*Net message from dblink' wait
  66.      Cmd: Select
  67. 51: waiting for 'SQL*Net message from dblink' wait
  68.      Cmd: Select
  69. 52: waiting for 'SQL*Net message from dblink' wait
  70.      Cmd: Select
  71. 54: waiting for 'SQL*Net message from dblink' wait
  72.      Cmd: Select
  73. 55: waiting for 'SQL*Net message from dblink' wait
  74.      Cmd: Select
  75. 57: waiting for 'SQL*Net message from dblink' wait
  76.      Cmd: Select
  77. 58: waiting for 'SQL*Net message from client' wait
  78.      Cmd: Insert
  79. 60: waiting for 'SQL*Net message from dblink' wait
  80.      Cmd: Select
  81. 61: waiting for 'SQL*Net message from dblink' wait
  82.      Cmd: Select
  83. 62: waiting for 'SQL*Net message from client' wait
  84. 63: waiting for 'SQL*Net message from client' wait
  85.      Cmd: Delete
  86. 64: waiting for 'SQL*Net message from client' wait
  87. 65: waiting for 'SQL*Net message from client' wait
  88.      Cmd: Insert
  89. 66: waiting for 'SQL*Net message from client' wait
  90.      Cmd: Delete
  91. 67: last wait for 'SQL*Net more data to client'
  92.      Cmd: Select
  93. 68: waiting for 'SQL*Net message from dblink' wait
  94.      Cmd: Select
  95. 69: waiting for 'SQL*Net message from client' wait
  96.      Cmd: Delete
  97. 70: waiting for 'SQL*Net message from client' wait
  98.      Cmd: Delete
  99. 71: waiting for 'SQL*Net message from client' wait
  100.      Cmd: Insert
  101. 72: waiting for 'SQL*Net message from client' wait
  102.      Cmd: Insert
  103. 73: waiting for 'SQL*Net message from client' wait
  104.      Cmd: Insert
  105. 74: waiting for 'SQL*Net message from client' wait
  106. 75: waiting for 'SQL*Net message from dblink' wait
  107.      Cmd: Select
  108. 76: waiting for 'SQL*Net message from client' wait
  109. 77: waiting for 'SQL*Net message from client' wait
  110. 78: waiting for 'SQL*Net message from dblink' wait
  111.      Cmd: Select
  112. 80: waiting for 'SQL*Net message from client' wait
  113.      Cmd: Insert
  114. 81: waiting for 'SQL*Net message from dblink' wait
  115.      Cmd: Select
  116. 82: waiting for 'SQL*Net message from client' wait
  117.      Cmd: Insert
  118. 83: waiting for 'SQL*Net message from client' wait
  119.      Cmd: Insert
  120. 84: last wait for 'SQL*Net more data to client'
  121.      Cmd: Select
  122. 85: waiting for 'SQL*Net message from client' wait
  123.      Cmd: Insert
  124. 86: waiting for 'SQL*Net message from client' wait
  125.      Cmd: Insert
  126. 87: waiting for 'SQL*Net message from client' wait
  127.      Cmd: Insert
  128. 88: waiting for 'SQL*Net message from client' wait
  129.      Cmd: Insert
  130. 90: waiting for 'SQL*Net message from client' wait
  131.      Cmd: Insert
  132. 92: waiting for 'SQL*Net message from client' wait
  133. 93: waiting for 'SQL*Net message from client' wait
  134.      Cmd: Insert
  135. 94: waiting for 'SQL*Net message from client' wait
  136.      Cmd: Insert
  137. 95: last wait for 'SQL*Net more data to client'
  138.      Cmd: Select
  139. 97: waiting for 'SQL*Net message from client' wait
  140. 99: waiting for 'SQL*Net message from client' wait
  141. 102:last wait for 'SQL*Net more data to client'
  142.      Cmd: Select
  143. 103:waiting for 'SQL*Net message from client' wait
  144.      Cmd: Insert
  145. 104:waiting for 'SQL*Net message from client' wait
  146.      Cmd: Insert
  147. 105:waiting for 'enq: DX - contention' [Enqueue DX-00000018-00000000] wait
  148. 106:waiting for 'SQL*Net message from client' wait
  149.      Cmd: Insert
  150. 107:waiting for 'SQL*Net message from client' wait
  151. 108:last wait for 'SQL*Net more data to client'
  152.      Cmd: Select
  153. 109:last wait for 'SQL*Net more data to client'
  154.      Cmd: Select
  155. 111:waiting for 'SQL*Net message from client' wait
  156. 112:waiting for 'SQL*Net message from client' wait
  157. 114:waiting for 'SQL*Net message from client' wait
  158. 120:waiting for 'SQL*Net message from client' wait
  159. 122:waiting for 'SQL*Net message from client' wait
  160. 123:waiting for 'inactive transaction branch' wait
  161. 124:waiting for 'SQL*Net message from client' wait
  162. 126:waiting for 'SQL*Net message from client' wait
  163. 128:waiting for 'inactive transaction branch' wait
  164. 131:waiting for 'enq: DX - contention' [Enqueue DX-00000013-00000000] wait
  165. 133:waiting for 'SQL*Net message from client' wait
  166. 134:waiting for 'SQL*Net message from client' wait
  167. 136:waiting for 'SQL*Net message from client' wait
  168. 137:last wait for 'SQL*Net more data to client'
  169.      Cmd: Select
  170. 138:waiting for 'SQL*Net message from client' wait
  171. 142:waiting for 'SQL*Net message from client' wait
  172.      Cmd: Delete
  173. 144:waiting for 'SQL*Net message from client' wait
  174. 147:waiting for 'SQL*Net message from client' wait
  175. 149:last wait for 'SQL*Net more data to client'
  176.      Cmd: Select
  177. 150:waiting for 'SQL*Net message from client' wait
  178.      Cmd: Insert
  179. 151:waiting for 'SQL*Net message from client' wait
  180.      Cmd: Insert
  181. 153:waiting for 'SQL*Net message from client' wait
  182. 154:waiting for 'SQL*Net message from client' wait
  183.      Cmd: Insert
  184. 155:waiting for 'SQL*Net message from client' wait
  185. 156:waiting for 'SQL*Net message from client' wait
  186.      Cmd: Insert
  187. 157:waiting for 'SQL*Net message from client' wait
  188. 158:waiting for 'SQL*Net message from client' wait
  189.      Cmd: Insert
  190. 162:waiting for 'SQL*Net message from client' wait
  191.      Cmd: Select
  192. 164:waiting for 'SQL*Net message from client' wait
  193. 165:waiting for 'SQL*Net message from client' wait
  194. 166:waiting for 'SQL*Net message from client' wait
  195.      Cmd: Select
  196. 168:waiting for 'SQL*Net message from client' wait
  197.      Cmd: Select
  198. 171:waiting for 'SQL*Net message from client' wait
  199. 172:waiting for 'SQL*Net message from client' wait
  200. 173:waiting for 'SQL*Net message from client' wait
  201. 175:waiting for 'SQL*Net message from client' wait
  202. 176:waiting for 'SQL*Net message from client' wait
  203. 177:waiting for 'SQL*Net message from client' wait
  204. 179:waiting for 'SQL*Net message from client' wait
  205. 182:waiting for 'SQL*Net message from client' wait
  206. 183:waiting for 'SQL*Net message from client' wait
  207. 185:waiting for 'enq: DX - contention' [Enqueue DX-0000001F-00000000] wait
  208. 187:waiting for 'SQL*Net message from client' wait
  209. 188:waiting for 'SQL*Net message from client' wait
  210. 189:waiting for 'SQL*Net message from client' wait
  211. 191:waiting for 'SQL*Net message from client' wait
  212. 193:waiting for 'SQL*Net message from client' wait
  213. 194:waiting for 'wait for unread message on broadcast channel' wait
  214.      Cmd: PL/SQL Execute
  215. 196:waiting for 'SQL*Net message from client' wait
  216. 197:waiting for 'SQL*Net message from client' wait
  217. 201:waiting for 'SQL*Net message from client' wait
  218. 202:waiting for 'SQL*Net message from client' wait
  219. 204:waiting for 'SQL*Net message from client' wait
  220. 205:waiting for 'SQL*Net message from client' wait
  221. 207:waiting for 'SQL*Net message from client' wait
  222. 208:waiting for 'SQL*Net message from client' wait
  223. 209:waiting for 'SQL*Net message from client' wait
  224. 210:waiting for 'SQL*Net message from client' wait
  225. 211:waiting for 'SQL*Net message from client' wait
  226. 212:last wait for 'SQL*Net more data to client'
  227.      Cmd: Select
  228. 213:waiting for 'SQL*Net message from client' wait
  229. 214:waiting for 'SQL*Net message from client' wait
  230. 215:waiting for 'SQL*Net message from client' wait
  231.      Cmd: Select
  232. 217:waiting for 'SQL*Net message from client' wait
  233. 219:waiting for 'SQL*Net message from client' wait
  234. 220:waiting for 'SQL*Net message from client' wait
  235. 221:waiting for 'SQL*Net message from client' wait
  236. 222:waiting for 'SQL*Net message from client' wait
  237. 223:waiting for 'SQL*Net message from client' wait
  238. 224:waiting for 'SQL*Net message from client' wait
  239. 226:waiting for 'SQL*Net message from client' wait
  240. 227:last wait for 'SQL*Net more data to client'
  241.      Cmd: Select
  242. 228:waiting for 'SQL*Net message from client' wait
  243. 229:waiting for 'SQL*Net message from client' wait
  244. 230:waiting for 'SQL*Net message from client' wait
  245. 233:waiting for 'SQL*Net message from client' wait
  246. 238:waiting for 'SQL*Net message from client' wait
  247. 239:waiting for 'SQL*Net message from client' wait
  248. 241:waiting for 'SQL*Net message from dblink' wait
  249.      Cmd: Select
  250. 242:waiting for 'SQL*Net message from client' wait
  251. 243:waiting for 'enq: DX - contention' [Enqueue DX-0000001C-00000000] wait
  252. 245:waiting for 'SQL*Net message from client' wait
  253. 246:last wait for 'SQL*Net more data to client'
  254.      Cmd: Select
  255. 252:waiting for 'SQL*Net message from client' wait
  256. 253:waiting for 'SQL*Net message from client' wait
  257. 260:waiting for 'SQL*Net message from client' wait
  258. 261:last wait for 'SQL*Net more data to client'
  259.      Cmd: Select
  260. 262:last wait for 'SQL*Net more data to client'
  261.      Cmd: Select
  262. 265:waiting for 'SQL*Net message from client' wait
  263. 269:waiting for 'SQL*Net message from client' wait
  264. 275:waiting for 'SQL*Net message from client' wait
  265. 279:waiting for 'SQL*Net message from client' wait
  266. 280:waiting for 'SQL*Net message from client' wait
  267. 281:waiting for 'SQL*Net message from client' wait
  268. 285:waiting for 'SQL*Net message from client' wait
  269. 286:waiting for 'SQL*Net message from client' wait
  270. 289:waiting for 'SQL*Net message from client' wait
  271. 293:waiting for 'SQL*Net message from client' wait
  272. 294:waiting for 'SQL*Net message from client' wait
  273. 297:waiting for 'enq: DX - contention' [Enqueue DX-00000112-00000000] wait
  274. 298:last wait for 'SQL*Net more data to client'
  275.      Cmd: Select
  276. 300:waiting for 'SQL*Net message from client' wait
  277. 301:last wait for 'SQL*Net more data to client'
  278.      Cmd: Select
  279. 302:waiting for 'SQL*Net message from client' wait
  280. 303:waiting for 'SQL*Net message from client' wait
  281. 304:last wait for 'SQL*Net more data to client'
  282.      Cmd: Select
  283. 306:waiting for 'enq: DX - contention' [Enqueue DX-0000021F-00000000] wait
  284. 307:last wait for 'SQL*Net more data to client'
  285.      Cmd: Select
  286. 308:waiting for 'SQL*Net message from client' wait
  287. 309:waiting for 'SQL*Net message from client' wait
  288. 310:waiting for 'SQL*Net message from client' wait
  289. 311:last wait for 'SQL*Net more data to client'
  290.      Cmd: Select
  291. 312:waiting for 'SQL*Net message from client' wait
  292.      Cmd: Insert
  293. 315:waiting for 'SQL*Net message from client' wait
  294. 316:last wait for 'SQL*Net more data to client'
  295.      Cmd: Select
  296. 317:waiting for 'SQL*Net message from client' wait
  297. 319:waiting for 'SQL*Net message from client' wait
  298. 320:waiting for 'SQL*Net message from client' wait
  299. 323:waiting for 'SQL*Net message from client' wait
  300.      Cmd: Insert
  301. 324:waiting for 'SQL*Net message from client' wait
  302. 326:waiting for 'SQL*Net message from client' wait
  303. 331:waiting for 'SQL*Net message from client' wait
  304.      Cmd: Select
  305. 339:waiting for 'SQL*Net message from client' wait
  306. 340:waiting for 'SQL*Net message from client' wait
  307. 343:waiting for 'SQL*Net message from client' wait
  308. 346:waiting for 'SQL*Net message from client' wait
  309. 349:waiting for 'SQL*Net message from client' wait
  310. 350:waiting for 'SQL*Net message from client' wait
  311. 352:waiting for 'SQL*Net message from client' wait
  312. 353:waiting for 'SQL*Net message from client' wait
  313. 355:waiting for 'SQL*Net message from client' wait
  314. 356:waiting for 'enq: DX - contention' [Enqueue DX-00000028-00000000] wait
  315. 359:waiting for 'SQL*Net message from client' wait
  316. 360:waiting for 'SQL*Net message from client' wait
  317. 361:waiting for 'SQL*Net message from client' wait
  318.      Cmd: Insert
  319. 363:waiting for 'SQL*Net message from client' wait
  320. 365:waiting for 'SQL*Net message from client' wait
  321. 368:waiting for 'SQL*Net message from client' wait
  322. 370:waiting for 'SQL*Net message from client' wait
  323. 375:waiting for 'SQL*Net message from client' wait
  324. 377:waiting for 'SQL*Net message from client' wait
  325.      Cmd: Select
  326. 378:waiting for 'SQL*Net more data to client' wait
  327.      Cmd: Select
  328. 379:waiting for 'SQL*Net message from client' wait
  329. 380:waiting for 'SQL*Net message from client' wait
  330. 383:waiting for 'SQL*Net message from client' wait
  331. 386:waiting for 'SQL*Net message from client' wait
  332. 387:waiting for 'SQL*Net message from client' wait
  333. 388:waiting for 'SQL*Net message from client' wait
  334. 389:waiting for 'SQL*Net message from client' wait
  335. 390:waiting for 'SQL*Net message from client' wait
  336. 391:last wait for 'SQL*Net more data to client'
  337.      Cmd: Select
  338. 395:waiting for 'SQL*Net message from client' wait
  339. 397:waiting for 'SQL*Net message from client' wait
  340. 404:waiting for 'SQL*Net message from client' wait
  341. 411:waiting for 'SQL*Net break/reset to client' wait
  342. 412:waiting for 'SQL*Net message from client' wait
  343.      Cmd: Select
  344. 414:waiting for 'SQL*Net message from client' wait
  345. 416:waiting for 'SQL*Net message from client' wait
  346. 417:waiting for 'SQL*Net message from client' wait
  347. 424:waiting for 'SQL*Net message from client' wait
  348. 425:waiting for 'SQL*Net message from client' wait
  349. 427:waiting for 'SQL*Net message from client' wait
  350. 428:waiting for 'SQL*Net message from client' wait
  351. 430:waiting for 'SQL*Net message from client' wait
  352. 431:waiting for 'SQL*Net message from client' wait
  353. 432:last wait for 'SQL*Net more data to client'
  354.      Cmd: Select
  355. 433:waiting for 'SQL*Net message from client' wait
  356. 434:waiting for 'SQL*Net message from client' wait
  357. 435:waiting for 'SQL*Net message from client' wait
  358. 439:waiting for 'SQL*Net message from client' wait
  359. 440:waiting for 'SQL*Net message from client' wait
  360. 441:waiting for 'SQL*Net message from client' wait
  361. 442:waiting for 'SQL*Net message from client' wait
  362. 443:waiting for 'SQL*Net message from client' wait
  363. 444:waiting for 'SQL*Net message from client' wait
  364. 445:waiting for 'SQL*Net message from client' wait
  365. 447:waiting for 'SQL*Net message from client' wait
  366. 448:waiting for 'SQL*Net message from client' wait
  367. 449:waiting for 'SQL*Net message from client' wait
  368. 450:waiting for 'SQL*Net message from client' wait
  369. 451:waiting for 'SQL*Net message from client' wait
  370. 452:waiting for 'SQL*Net message from client' wait
  371. 453:waiting for 'SQL*Net message from client' wait
  372. 454:waiting for 'SQL*Net message from client' wait
  373. 455:waiting for 'SQL*Net message from client' wait
  374. 456:waiting for 'SQL*Net message from client' wait
  375. 458:waiting for 'SQL*Net message from client' wait
  376. 459:waiting for 'SQL*Net message from client' wait
  377. 460:waiting for 'SQL*Net message from client' wait
  378. 461:waiting for 'SQL*Net message from client' wait
  379.      Cmd: Insert
  380. 462:waiting for 'SQL*Net message from client' wait
  381. 463:waiting for 'SQL*Net message from client' wait
  382.      Cmd: Insert
  383. 464:waiting for 'SQL*Net message from client' wait
  384.      Cmd: Delete
  385. 465:waiting for 'SQL*Net message from client' wait
  386.      Cmd: Insert
  387. 466:waiting for 'SQL*Net message from client' wait
  388.      Cmd: Insert
  389. 467:waiting for 'SQL*Net message from client' wait
  390. 540:waiting for 'SQL*Net message from client' wait
  391. Blockers
  392. ~~~~~~~~

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

  402.                     Resource Holder State
  403. Enqueue DX-0000001A-00000000    ??? Blocker
  404. Enqueue DX-00000018-00000000    24: waiting for 'SQL*Net message from dblink'
  405. Enqueue DX-00000013-00000000    78: waiting for 'SQL*Net message from dblink'
  406. Enqueue DX-0000001F-00000000    52: waiting for 'SQL*Net message from dblink'
  407. Enqueue DX-0000001C-00000000    60: waiting for 'SQL*Net message from dblink'
  408. Enqueue DX-00000112-00000000   241: waiting for 'SQL*Net message from dblink'
  409. Enqueue DX-0000021F-00000000    16: waiting for 'SQL*Net message from dblink'
  410. Enqueue DX-00000028-00000000    51: waiting for 'SQL*Net message from dblink'
  411. Enqueue DX-00000028-00000000   378: waiting for 'SQL*Net more data to client'

  413. Object Names
  414. ~~~~~~~~~~~~
  415. Enqueue DX-0000001A-00000000                                 
  416. Enqueue DX-00000018-00000000                                 
  417. Enqueue DX-00000013-00000000                                 
  418. Enqueue DX-0000001F-00000000                                 
  419. Enqueue DX-0000001C-00000000                                 
  420. Enqueue DX-00000112-00000000                                 
  421. Enqueue DX-0000021F-00000000                                 
  422. Enqueue DX-00000028-00000000                                 

  424. WARNING: The following is a list of process id's that have state
  425.          objects that are NOT owned by the parent state object and as
  426.          such have been SKIPPED during processing. (These are typically
  427.          SQL*Net loopback sessions).
  428. 20: 23: 32: 35: 67: 77: 84: 102: 108: 128: 137: 168: 185: 210: 212: 215: 217: 220: 221: 222: 227: 246: 261: 262: 297: 298: 301: 306: 316: 339: 340: 343: 365: 375: 377: 378: 386: 388: 391: 411: 432:


  431. 469067 Lines Processed.
复制代码

回复 只看该作者 道具 举报

10#
发表于 2012-2-3 22:56:18
Resource Holder State
Enqueue DX-0000001A-00000000    ??? Blocker
Enqueue DX-00000018-00000000    24: waiting for 'SQL*Net message from dblink'
Enqueue DX-00000013-00000000    78: waiting for 'SQL*Net message from dblink'
Enqueue DX-0000001F-00000000    52: waiting for 'SQL*Net message from dblink'
Enqueue DX-0000001C-00000000    60: waiting for 'SQL*Net message from dblink'
Enqueue DX-00000112-00000000   241: waiting for 'SQL*Net message from dblink'
Enqueue DX-0000021F-00000000    16: waiting for 'SQL*Net message from dblink'
Enqueue DX-00000028-00000000    51: waiting for 'SQL*Net message from dblink'
Enqueue DX-00000028-00000000   378: waiting for 'SQL*Net more data to client'



分析 PID=24

SO: 70000020888f008, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=24, calls cur/top: 700000208fdac60/700000208fdac60, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 109 0 4
              last post received-location: kslpsr
              last process to post me: 70000020f888e18 1 6
              last post sent: 0 0 33
              last post sent-location: ksrpublish
              last process posted by me: 70000020a8ca5e0 1 22
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 70000020f9f9990
    O/S info: user: oracle, term: UNKNOWN, ospid: 1487648
    OSD pid info: Unix process pid: 1487648, image: oracle@cpmisdb

    Short stack dump:
ksdxfstk+002c<-ksdxcb+04e4<-sspuser+0074<-000047B8<-nttrd+0120<-nsprecv+07a0<-nsrdr+0114<-nsdo+1710<-nsbrecv+0040<-nioqrc+04a8<-opikndf2+0688<-npixfc+08b4<-k2rbeg+0180
<-npibeg+0924<-npixfc+04c0<-psdrpc+0448<-prirpc+0234<-pevm_RCAL+0200<-pfrinstr_RCAL+0074<-pfrrun_no_tool+005c<-pfrrun+1014<-plsql_run+06b4<-peicnt+0224<-kkxuexe+0360<-
kkxmpsexe+029c<-kgmexwi+056c<-kgmexec+0bcc<-evapls+054c<-evaopn2+07cc<-evalik+0164<-expepr+01b4<-expeal+0024<-qerhjWalkHashBucket+0144<-qerhjInnerProbeHashTable+02e0<-
qerhjWalkHashBucket+0238<-qerhjInnerProbeHashTable+02e0<-qerrmPFR+0208<-qerrmOpfr+0208<-qerrmOdcb+0014<-ttcfour+12e4<-ttcdrv+2e40<-nioqwa+0058<-upirtrc+0520<-kpurcsc+0
06c<-IPRA.$kpufch0+0ff0<-kpufch+0bb8<-OCIStmtFetch+001c<-qerrmOFBu+01fc<-qerrmFBu+0398<-qerrmFetch+00ec<-rwsfcd+0054<-qerhjFetch+0664<-rwsfcd+0054<-qerhjFetch+0664<-qe
rgsFetch+0278<-kpofrws+019c<-opifch2+13dc<-opifch+003c<-opiodr+0ae0<-ttcpip+1020<-opitsk+1124<-opiino+0990<-opiodr+0ae0<-opidrv+0484<-sou2o+0090<-opimai_real+01bc<-mai
n+0098<-__start+0070       

   SO: 700000209de5a78, type: 4, owner: 70000020888f008, flag: INIT/-/-/0x00
    (session) sid: 2844 trans: 70000020398a600, creator: 70000020888f008, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-0018-03D6D884, short-term DID: 0000-0000-00000000
              txn branch: 700000201224d00
              oct: 3, prv: 0, sql: 7000001cd658918, psql: 7000001e725fb50, user: 59/MATERIAL_HB
    service name: SYS$USERS
    O/S info: user: root, term: , ospid: 1234, machine: hbcpmis.hubeitel.com
              program:
    waiting for 'SQL*Net message from dblink' blocking sess=0x0 seq=59149 wait_time=0 seconds since wait started=9194312
                driver id=54435000, #bytes=1, =0
    Dumping Session Wait History
     for 'SQL*Net message to dblink' count=1 wait_time=1
                driver id=54435000, #bytes=1, =0
     for 'SQL*Net message from dblink' count=1 wait_time=490
         
         

PID 24 执行 SQL 7000001cd658918  频繁等待事件SQL*Net message from dblink

SQL 7000001cd658918  如下

  SO: 7000000cc25ac20, type: 53, owner: 700000209de5a78, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=7000000cc25ac20 handle=7000001cd658918 mode=N
      call pin=0 session pin=0 hpc=0000 hlc=0000
      htl=7000000cc25aca0[7000001bfbc33c0,7000000cc34df50] htb=7000000cad75510 ssga=7000000cad74ef8
      user=700000209de5a78 session=700000209de5a78 count=1 flags=[0000] savepoint=0x4e9f90b8
      LIBRARY OBJECT HANDLE: handle=7000001cd658918 mtx=7000001cd658a48(1) cdp=1
      name=select count(*) from (select * from vw_contract where 1=1  and areacode in (590,717) and projname like '%èy??·-°ó???ù1??·?íμàCí??2??1¤3ì   %')
      hash=63477761beca9b3652068ef437153f50 timestamp=10-20-2011 11:08:40
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/KST/DBN/MTX/[500100d0]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=9 hpc=0002 hlc=0002
      lwt=7000001cd6589c0[7000001cd6589c0,7000001cd6589c0] ltm=7000001cd6589d0[7000001cd6589d0,7000001cd6589d0]
      pwt=7000001cd658988[7000001cd658988,7000001cd658988] ptm=7000001cd658998[7000001cd658998,7000001cd658998]
      ref=7000001cd6589f0[7000001cd6589f0,7000001cd6589f0] lnd=7000001cd658a08[7000001cd658a08,7000001cd658a08]
        LIBRARY OBJECT: object=7000000ed8b60f0
        type=CRSR flags=EXS[0001] pflags=[0000] status=VALD load=0
        CHILDREN: size=16
        child#    table reference   handle
        ------ -------- --------- --------
             0 7000000b9dcd2e8 7000000b9dccf58 7000001c776a130
        DATA BLOCKS:
        data#     heap  pointer    status pins change whr
        ----- -------- -------- --------- ---- ------ ---
            0 70000020b5492c0 7000000ed8b6208 I/P/A/-/-    0 NONE   00



select count(*) from (select * from vw_contract where 1=1  and areacode in (590,717) and projname like '%èy??·-°ó???ù1??·?íμàCí??2??1¤3ì   %')


vw_contract =>


   SO: 7000000ccb5cb30, type: 53, owner: 70000020fdbbbb8, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=7000000ccb5cb30 handle=7000001a1839ca8 mode=N
      call pin=0 session pin=0 hpc=0000 hlc=0000
      htl=7000000ccb5cbb0[7000001f84d5540,7000001f84d5540] htb=7000001f84d5540 ssga=7000001f84d44a8
      user=70000020fdbbbb8 session=70000020fdbbbb8 count=0 flags=LRU/[4000] savepoint=0x19418a5
      LIBRARY OBJECT HANDLE: handle=7000001a1839ca8 mtx=7000001a1839dd8(0) cdp=0
      name=MATERIAL_HB.VW_CONTRACT
      hash=3db98cbbc0c7f5773b19da88b7cb79f8 timestamp=10-17-2011 22:44:43
      namespace=TABL flags=KGHP/TIM/SML/[02000000]
      kkkk-dddd-llll=0000-0000-0000 lock=N pin=0 latch#=4 hpc=08b8 hlc=08b8
      lwt=7000001a1839d50[7000001a1839d50,7000001a1839d50] ltm=7000001a1839d60[7000001a1839d60,7000001a1839d60]
      pwt=7000001a1839d18[7000001a1839d18,7000001a1839d18] ptm=7000001a1839d28[7000001a1839d28,7000001a1839d28]
      ref=7000001a1839d80[7000001a1839d80,7000001a1839d80] lnd=7000001a1839d98[70000020c29c518,7000001a14fe9f8]
        LIBRARY OBJECT last freed from HPD addn data CBK               


                 namespace=TABL = > TABL     table/view/sequence/synonym

MATERIAL_HB.VW_CONTRACT　可能是表 视图 序列 或同义词


另一PID =78


  SO: 7000002098c0a70, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=78, calls cur/top: 7000001f9800538/7000001f9800538, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 109 0 4
              last post received-location: kslpsr
              last process to post me: 70000020f888e18 1 6
              last post sent: 0 0 24
              last post sent-location: ksasnd
              last process posted by me: 70000020f888e18 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 70000020f9f9990
    O/S info: user: oracle, term: UNKNOWN, ospid: 1368374
    OSD pid info: Unix process pid: 1368374, image: oracle@cpmisdb
    Short stack dump:
ksdxfstk+002c<-ksdxcb+04e4<-sspuser+0074<-000047B8<-nttrd+0120<-nsprecv+07a0<-nsrdr+0114<-nsdo+1710<-nsbrecv+0040<-nioqrc+04a8<-opikndf2+0688<-npixfc+08b4<-k2rbeg+0180<
-npibeg+0924<-npixfc+04c0<-psdrpc+0448<-prirpc+0234<-pevm_RCAL+0200<-pfrinstr_RCAL+0074<-pfrrun_no_tool+005c<-pfrrun+1014<-plsql_run+06b4<-peicnt+0224<-kkxuexe+0360<-kk
xmpsexe+029c<-kgmexwi+056c<-kgmexec+0bcc<-evapls+054c<-evaopn2+07cc<-evalik+0164<-expepr+01b4<-expeal+0024<-qerhjWalkHashBucket+0144<-qerhjInnerProbeHashTable+02e0<-qer
hjInnerProbeHashTable+0218<-qerrmPFR+0208<-qerrmOpfr+0208<-qerrmOdcb+0014<-ttcfour+12e4<-ttcdrv+2e40<-nioqwa+0058<-upirtrc+0520<-kpurcsc+006c<-IPRA.$kpufch0+0ff0<-kpufc
h+0bb8<-OCIStmtFetch+001c<-qerrmOFBu+01fc<-qerrmFBu+0398<-qerrmFetch+00ec<-rwsfcd+0054<-qerhjFetch+0664<-rwsfcd+0054<-qerhjFetch+0664<-qergsFetch+0278<-opifch2+141c<-op
ifch+003c<-opiodr+0ae0<-ttcpip+1020<-opitsk+1124<-opiino+0990<-opiodr+0ae0<-opidrv+0484<-sou2o+0090<-opimai_real+01bc<-main+0098<-__start+0070


   SO: 70000020fe357b0, type: 4, owner: 7000002098c0a70, flag: INIT/-/-/0x00
    (session) sid: 3217 trans: 7000002011d3070, creator: 7000002098c0a70, flag: (100041) USR/- BSY/-/-/-/-/-
              DID: 0001-004E-03073148, short-term DID: 0000-0000-00000000
              txn branch: 700000201224e40
              oct: 3, prv: 0, sql: 7000001e416b0a0, psql: 7000001e725fb50, user: 59/MATERIAL_HB
    service name: SYS$USERS
    O/S info: user: root, term: , ospid: 1234, machine: hbcpmis.hubeitel.com
              program:
    waiting for 'SQL*Net message from dblink' blocking sess=0x0 seq=16009 wait_time=0 seconds since wait started=7613514
                driver id=54435000, #bytes=1, =0
    Dumping Session Wait History
     for 'SQL*Net message to dblink' count=1 wait_time=0
                driver id=54435000, #bytes=1, =0
     for 'SQL*Net message from dblink' count=1 wait_time=485
                driver id=54435000, #bytes=1, =0
     for 'SQL*Net message to dblink' count=1 wait_time=1
                driver id=54435000, #bytes=1, =0
     for 'SQL*Net message from dblink' count=1 wait_time=101
                driver id=54435000, #bytes=1, =0
     for 'SQL*Net message to dblink' count=1 wait_time=1
                driver id=54435000, #bytes=1, =0
     for 'SQL*Net message from dblink' count=1 wait_time=2598
                driver id=54435000, #bytes=1, =0


也在等 SQL*Net message to dblink' ，SQL   7000001e416b0a0 如下


    SO: 7000000cd07a6b0, type: 53, owner: 70000020ae02648, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=7000000cd07a6b0 handle=7000001e416b0a0 mode=N
      call pin=0 session pin=0 hpc=0000 hlc=0000
      htl=7000000cd07a730[7000000cd1cb4b0,7000000cd09c738] htb=7000001f84c0bc0 ssga=7000001f84c07f8
      user=70000020ae02648 session=70000020ae02648 count=1 flags=[0000] savepoint=0x4eb7af6a
      LIBRARY OBJECT HANDLE: handle=7000001e416b0a0 mtx=7000001e416b1d0(1) cdp=1
      name=select count(*) from (select * from vw_contract where 1=1  and areacode in (590,728) and contractcode like '%1001%' and projname like '%ÏÉÌÒ¿­±ö˹¾ÆµêÊÒÄÚ·Ö²
¼ÏµÍ³¹¤³Ì£¨RRU£©%')
      hash=d67d07ea76676e6505443d01573c452b timestamp=11-07-2011 18:14:04
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/KST/DBN/MTX/[500100d0]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch#=7 hpc=0004 hlc=0004
      lwt=7000001e416b148[7000001e416b148,7000001e416b148] ltm=7000001e416b158[7000001e416b158,7000001e416b158]
      pwt=7000001e416b110[7000001e416b110,7000001e416b110] ptm=7000001e416b120[7000001e416b120,7000001e416b120]
      ref=7000001e416b178[7000001e416b178,7000001e416b178] lnd=7000001e416b190[7000001e416b190,7000001e416b190]
        LIBRARY OBJECT: object=7000001d4ed6948


同样在查询  vw_contract



PID=105  等待 enq: DX - contention

105:waiting for 'enq: DX - contention' [Enqueue DX-00000018-00000000] wait

----------------------------------------
  SO: 70000020a8d62e0, type: 2, owner: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=105, calls cur/top: 7000001f8872580/7000001f8872580, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
              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: 70000020f9f9990
    O/S info: user: oracle, term: UNKNOWN, ospid: 877082
    OSD pid info: Unix process pid: 877082, image: oracle@cpmisdb
    Short stack dump:
    Short stack dump:
ksdxfstk+002c<-ksdxcb+04e4<-sspuser+0074<-000047B8<-sskgpwwait+0034<-skgpwwait+00bc<-ksliwat+06c0<-kslwaitns_timed+0024<-kskthbwt+022c<-kslwait+00f4<-ksqcmi+2124<-ksqgt
lctx+0db0<-ksqgelctx+0338<-K2GTElock+0268<-k2gins+0444<-k2lbeg+04a8<-k2sbeg+0154<-k2serv+0184<-opiodr+0ae0<-ttcpip+1020<-opitsk+1124<-opiino+0990<-opiodr+0ae0<-opidrv+0
484<-sou2o+0090<-opimai_real+01bc<-main+0098<-__start+0070


   SO: 700000209e6f750, type: 4, owner: 70000020a8d62e0, flag: INIT/-/-/0x00
    (session) sid: 3256 trans: 0, creator: 70000020a8d62e0, flag: (c0000041) USR/- BSY/-/-/-/-/-
              DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
              txn branch: 700000201228ad0
              oct: 0, prv: 0, sql: 0, psql: 0, user: 58/PROJECT_HB
    service name: SYS$USERS
    O/S info: user: oracle, term: , ospid: 1487648, machine: cpmisdb
              program: oracle@cpmisdb (TNS V1-V3)
    application name: oracle@cpmisdb (TNS V1-V3), hash value=759291196
    waiting for 'enq: DX - contention' blocking sess=0x700000209e60bd8 seq=6236 wait_time=0 seconds since wait started=3
                name|mode=44580006, transaction entry #=18, 0=0
    Dumping Session Wait History
     for 'enq: DX - contention' count=1 wait_time=1962984
                name|mode=44580006, transaction entry #=18, 0=0
     for 'inactive transaction branch' count=1 wait_time=976590
                branch#=0, waited=0, =0
     for 'enq: DX - contention' count=1 wait_time=2929717
                name|mode=44580006, transaction entry #=18, 0=0
     for 'enq: DX - contention' count=1 wait_time=2929711
                name|mode=44580006, transaction entry #=18, 0=0
     for 'inactive transaction branch' count=1 wait_time=976583
                branch#=0, waited=0, =0
     for 'enq: DX - contention' count=1 wait_time=2929712
                name|mode=44580006, transaction entry #=18, 0=0
     for 'enq: DX - contention' count=1 wait_time=2929708


PID 105 等待enq: DX - contention 大量时间

回复 只看该作者 道具 举报

11#
发表于 2012-2-3 23:18:38
深入发掘 的话 这个case 还可以找出很多 有趣的信息， 但是时间不早了。。。

ODM Find :

存在  AIX 10.2.0.4 上调用函数远程dblink 出现ENQ: DX hang的Bug


Hdr: 10178608 10.2.0.4.0 RDBMS 10.2.0.4.0 TXN MGMT DIST PRODID-5 PORTID-212
Abstract: SELECT WITH FUNCTION USING DBLINK HANGS WAITS ENQ: DX
  1. ROBLEM:
  2. --------
  3. select with function using dblink hangs.
  4. On remote site two session one session waits enq: DX - contention
  5. and the other waits with SQL*Net more data to client
  6. Could reproduce with Testcase in Bug: 3998386

  8. With 5000 rows it works fine.
  9. With 50000 rows it hangs.

  11. Using optimizer_features_enable  = 8.1.7 doesn't resolve the problem.

  13. It hangs with optimizer_mode=rule on local side and also on both sides.

  15. Also fails with   10.2.0.1
  16. Fails with 10.2.0.4.0 local and remote
  17. Fails with local 8.1.7.2 and remote 10.2.0.4.0
  18. Fails with local 10.2.0.4 and remote 11.2.0.1
  19. Fails with local 11.2.0.1 and remote  11.2.0.1

  21. Fails with:
  22. open_links  = 30
  23. open_links_per_instance = 30


  26. DIAGNOSTIC ANALYSIS:
  27. --------------------
  28. Looks like the patch fix for Bug: 8477973 is needed for 10g

  30. WORKAROUND:
  31. -----------
  32. With the function on the other side it works,
  33. but customer cannot change it because its their
  34. Advanced Replication which executes the queries.

  36. With the patch for Bug: 8477973 applied to environment
  37. local 11.2.0.1 and remote 11.2.0.1 it works, no hang.
  38. Patch for Bug: 8477973 resolves the problem.
  39. Works with local 8.1.7.2 and remote 11.2.0.1 (with patch applied)

  41. Reduce table rows - with 5000 rows it works fine.


  44. RELATED BUGS:
  45. -------------
  46. Bug: 8477973
  47. Bug: 3998386

  49. REPRODUCIBILITY:
  50. ----------------
  51. Yes, reproduced on 10.2.0.4 celcaix4.us.oracle.com
  52. Local Instance = sP124Sa
  53. Remote Instance = sP124Sb
  54. Using Testcase from Bug: 3998386

  56. TEST CASE:
  57. ----------
  58. Uploaded to /upload/bug_10/bug10178608
  59. local.sql
  60. remote.sql
  61. traces.zip


  64. STACK TRACE:
  65. ------------

  67. SUPPORTING INFORMATION:
  68. -----------------------
  69. Related to:
  70. Note: 730423.1  Select With Local Function and Remote Tables Using a Dblink
  71.                    Hangs Due To Enq DX
复制代码
文档
Select With Local Function and Remote Tables Using a Dblink Hangs Due To Enq DX [ID 730423.1] 介绍了更多内容
  1. Applies to:
  2. Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2
  3. Information in this document applies to any platform.
  4. Symptoms
  5. -- Problem Statement:
  6. Two databases interacting via a private DBLINK.

  8. The query at the LOCAL Site includes references for remote objects (via a dblink) as well as a LOCAL FUNCTION (that does not exist at the remote Site):

  10. SELECT p.wey_cc_category_id, pp.wey_cc_phase_id, pp.projectname, pb.wey_cc_bndl_nbr
  11. FROM ps_wey_ccprjdefn@WEYCC8.WORLD p, ps_wey_cc_prj_ph@WEYCC8.WORLD pp, ps_wey_cc_prj_bndl@WEYCC8.WORLD pb
  12. WHERE pp.projectname = p.projectname AND
  13. pb.projectname = pp.projectname AND
  14. f_wey_get_bndl_ready_status (pp.projectname, pp.wey_cc_phase_id , SYSDATE, pb.wey_cc_bndl_nbr) = 'BR';



  18. When executing the query it hangs and systemstate dumps (at the remote site) show something like:

  20. 17: waiting for 'SQL*Net more data to client' seq=138
  21. Cmd: Select
  22. 18: waiting for 'enq: DX - contention' [Enq DX-0000001C-00000000] seq=9
  23. .
  24. .
  25. Blockers
  26. ~~~~~~~~

  28. Resource Holder State
  29. Enq DX-0000001C-00000000 17: waiting for 'SQL*Net more data to client'
  30. .
  31. .


  34. PROCESS 17:
  35. ----------------
  36. .
  37. .
  38. SO: c00000032b285470, type: 4, owner: c00000032b1dd670, flag: INIT/-/-/0x00
  39. (session) sid: 93 trans: c00000032a75c1c8, creator: c00000032b1dd670, flag:
  40. (41) USR/- BSY/-/-/-/-/-
  41. DID: 0001-0011-0000CAE4, short-term DID: 0000-0000-00000000
  42. txn branch: c00000032a794db0
  43. oct: 3, prv: 0, sql: c0000003267ce3d0, psql: c000000326883d68, user: 39/WEYCC88
  44. O/S info: user: oracle, term: pts/tb, ospid: 11772, machine: psdev1
  45. program: oracle@psdev1 (TNS V1-V3)
  46. application name: oracle@psdev1 (TNS V1-V3), hash value=0
  47. waiting for 'SQL*Net more data to client' blocking sess=0x0000000000000000
  48. seq=138 wait_time=0 seconds since wait started=681
  49. driver id=54435000, #bytes=7cf, =0
  50. .
  51. .
  52. SO: c000000326441838, type: 53, owner: c00000032b285470, flag: INIT/-/-/0x00
  53. LIBRARY OBJECT LOCK: lock=c000000326441838 handle=c0000003267ce3d0 mode=N
  54. call pin=0000000000000000 session pin=0000000000000000 hpc=0000 hlc=0000
  55. htl=c0000003264418b8[c0000003267c39b8,c000000326447e30] htb=c0000003267c39b8 ssga=c0000003267c2dc0 user=c00000032b285470 session=c00000032b285470 count=1 flags=[0000] savepoint=0x48447c16
  56. LIBRARY OBJECT HANDLE: handle=c0000003267ce3d0 mtx=c0000003267ce500(1)
  57. cdp=1
  58. name=SELECT "PROJECTNAME","WEY_CC_PHASE_ID" FROM "WEYCC88".
  59. "PS_WEY_CC_PRJ_PH" "PP"
  60. hash=bd1ceef17a931288bed1ec5cc36ca4ae timestamp=05-15-2008 11:21:50
  61. .
  62. .
  63. PROCESS 18:
  64. ---------------
  65. .
  66. .
  67. SO: c00000032b293490, type: 4, owner: c00000032b1dde60, flag: INIT/-/-/0x00
  68. (session) sid: 104 trans: 0000000000000000, creator: c00000032b1dde60, flag:
  69. (c0000041) USR/- BSY/-/-/-/-/-
  70. DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
  71. txn branch: c00000032a794ef0
  72. oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 39/WEYCC88
  73. O/S info: user: oracle, term: pts/tb, ospid: 11772, machine: psdev1
  74. program: oracle@psdev1 (TNS V1-V3)
  75. application name: oracle@psdev1 (TNS V1-V3), hash value=0
  76. waiting for 'enq: DX - contention' blocking sess=0xc00000032b285470 seq=9
  77. wait_time=0 seconds since wait started=680
  78. name|mode=44580006, transaction entry #=1c, 0=0
  79. Dumping Session Wait History
  80. for 'enq: DX - contention' count=1 wait_time=2939328
  81. name|mode=44580006, transaction entry #=1c, 0=0
  82. .
  83. .


  86. Due to this lock, the query hangs.


  89. Changes

  91. Cause
  92. There are some issue reported with similar behavior , though not fixes available so far ..

  94. BUG 6236841 SELECT WITH FUNCTION USING DBLINK HANGS
  95. BUG 6062854 DISTRIBUTED QUERY HANGS IN THE REMOTE SITE
  96. BUG 3998386 SELECT WITH FUNCTION USING DBLINK HANGS


  99. Note: The same query runs fine in 10.1.0.4 and 10.2.0.1 so it was assumed it was fixed, though it hangs in 9i R2 and other 10g R1 , 10g R2, 11g R1 and 11g R2 versions with same behavior as described above .

  101. In 11g R1 and 11g R2, the issue might be also related to:

  103. BUG 8477973 Multiple open DB links / ORA-2020 / distributed deadlock / ORA-600 possible using DB Links


  106. Solution
  107. -- To implement the solution, please execute the following steps::

  109. It may be possible to use either of following potential workarounds:

  111. (1) At LOCAL site and prior to executing the query, set OPTIMIZER_FEATURES_ENABLE to 8.1.7,

  113. or

  115. (2) If the function can be co-located with the tables/views it uses and called remotely, then you
  116. may avoid the problem.

  118. or

  120. (3) If the function can be declared pragma autonomous transaction, you will avoid the problem.
  121. You will have to do a commit or rollback of the autonomous transaction before leaving the function.

  123. or

  125. (4) If in 11g R1 or 11g R2 (up to 11.2.0.1), apply One-Off backport for 8477973
  126. In this particular case, Defect 8477973 is fixed in:

  128. 12.1 (Future Release)
  129. 11.2.0.2 (Server Patch Set)
  130. 11.2.0.1 Patch 2 on Windows Platforms
  131. 11.1.0.7 Patch 28 on Windows Platforms


  134. Still have questions ?
复制代码
lz 请 从 应用程序的角度 分析case是否与以上bug 匹配

回复 只看该作者 道具 举报

12#
发表于 2012-2-7 10:53:36
能否提供ass.awk脚本

回复 只看该作者 道具 举报

13#
发表于 2012-2-7 11:36:50
http://www.oracledatabase12g.com ... -ass-awk-v1-09.html

回复 只看该作者 道具 举报

14#
发表于 2012-2-7 21:16:20
这个问题 有意思的地方在于：

DB TIME 很高

Elapsed:                 60.01 (mins)                  
DB Time:                 863.56 (mins)         

Event        Waits        Time(s)        Avg Wait(ms)        % Total Call Time        Wait Class
enq: DX - contention        24,691        72,313        2,929        139.6        Other
inactive transaction branch        12,348        12,058        976        23.3        Other
CPU time                 2,433                 4.7         


enq: DX - contention和 inactive transaction branch的wait time 也很长

但是观察 time model 统计信息 却发现 SQL 执行耗时 2,422.26 要远小于 DB TIME ，且其他耗时均很少 可以忽略不计

Time Model Statistics

    Total time in database user-calls (DB Time): 51813.7s
    Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
    Ordered by % or DB time desc, Statistic name

Statistic Name        Time (s)        % of DB Time
DB CPU         2,432.95         4.70
sql execute elapsed time         2,422.26         4.67
PL/SQL execution elapsed time         88.12         0.17
parse time elapsed         53.64         0.10
hard parse elapsed time         42.01         0.08
inbound PL/SQL rpc elapsed time         22.01         0.04
connection management call elapsed time         16.92         0.03
PL/SQL compilation elapsed time         6.20         0.01
hard parse (sharing criteria) elapsed time         0.58         0.00
failed parse elapsed time         0.34         0.00
hard parse (bind mismatch) elapsed time         0.15         0.00
repeated bind elapsed time         0.08         0.00
sequence load elapsed time         0.01         0.00
DB time         51,813.74          
background elapsed time         52.30          
background cpu time         24.77          


同时又无法通过AWR 、ASH、ADDM找出 引起大量enq: DX  等待的SQL语句

所以需要通过systemstate 来深入分析， 大家请注意 不是每次性能问题我们都需要用到systemstate， 分析systemstate是很慢的  远不如AWR、ASH、ADDM来的直观， 只有当AWR无法告诉你真相的时候 建议用systemstate errorstack hanganalyze这些手段， 譬如这个例子。

深入分析后发现  vw_contract  这个对象存在问题， 经发帖的网友确认 这是一个view

网友的留言："

视图里面又有DBLINK，指向的是本机，昏。。。
恩，vw_contract 是用户A的视图，视图里面有用户B的两张表，但是开发是通过DBLINK来调的，DBLINK指向本机，应该就是这样。

你是说 DBLINK 又指向 本实例了？

对 可能以前B用户在另外一个数据库里 这种状况，是不是没人管，时间长了，系统会自动处理？我看现在V$GLOBAL_TRANSACTION只有27条输出了"


即这个视图中存在的远程对象 实际指向了 同一个数据库的另一个用户的 一张表上，同时很多并发的对该视图的查询语句， 从而导致了频繁出现 SQL*Net message from dblink 和 enq : DX的等待事件

回复 只看该作者 道具 举报

15#
发表于 2012-2-7 21:40:56
Maclean分析很给力啊，顶一个

回复 只看该作者 道具 举报

16#
发表于 2012-5-29 17:55:47
感谢 刘大 非常感谢  我的系统也出现了此问题 ：
enq: DX - contention和 inactive transaction branch和 SQL*Net more data from client 的wait

回复 只看该作者 道具 举报

返回列表
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 注册

QQ|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-11-15 20:58 , Processed in 0.061594 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部
TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569