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

找回密码
注册
搜索
热搜: 活动 交友 discuz
Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › ORA-00600: internal error code, arguments: [729]
发新帖
monkeybron

0

积分

1

好友

3

主题

[SQL调优] ORA-00600: internal error code, arguments: [729]

1#
发表于 2012-12-26 10:39:11 | 查看: 4586| 回复: 7
刘大好
我这里最近3天2个节点均出现，至少每天产生 5个以上的报错
ORA-00600: internal error code, arguments: [729], [766432], [space leak], [], [], [], [], []

环境 AIX 5.3 10.2.0.4 RAC RAW 设备 2节点

节点1截取一个：日志如下
  1. [oracle@busdbr1] /oracle/admin/busdb/bdump 149> more alert_busdb1.log_2012-12-26_08-40-00
  2. Wed Dec 26 08:02:30 2012
  3. Thread 1 advanced to log sequence 209731 (LGWR switch)
  4.   Current log# 5 seq# 209731 mem# 0: /dev/rlvbuslog131
  5.   Current log# 5 seq# 209731 mem# 1: /dev/rlvbuslog132
  6. Wed Dec 26 08:02:30 2012
  7. LNS: Standby redo logfile selected for thread 1 sequence 209731 for destination LOG_ARCHIVE_DEST_2
  8. Wed Dec 26 08:33:13 2012
  9. Errors in file /oracle/admin/busdb/udump/busdb1_ora_2240784.trc:
  10. ORA-00600: internal error code, arguments: [729], [766432], [space leak], [], [], [], [], []
  11. Wed Dec 26 08:33:14 2012
  12. Trace dumping is performing id=[cdmp_20121226083314]
  13. Wed Dec 26 08:36:34 2012
  14. Thread 1 advanced to log sequence 209732 (LGWR switch)
  15.   Current log# 2 seq# 209732 mem# 0: /dev/rlvbuslog121
  16.   Current log# 2 seq# 209732 mem# 1: /dev/rlvbuslog122
  17. Wed Dec 26 08:36:34 2012
  18. LNS: Standby redo logfile selected for thread 1 sequence 209732 for destination LOG_ARCHIVE_DEST_2
复制代码
节点2 截取一个日志如下：
  1. [oracle@busdbr2] /oracle/admin/busdb/bdump 1448> more alert_busdb2.log_2012-12-26_06-40-00
  2. Wed Dec 26 06:00:25 2012
  3. Thread 2 advanced to log sequence 106520 (LGWR switch)
  4.   Current log# 4 seq# 106520 mem# 0: /dev/rlvbuslog221
  5.   Current log# 4 seq# 106520 mem# 1: /dev/rlvbuslog222
  6. Wed Dec 26 06:00:25 2012
  7. LNS: Standby redo logfile selected for thread 2 sequence 106520 for destination LOG_ARCHIVE_DEST_2
  8. Wed Dec 26 06:33:22 2012
  9. Errors in file /oracle/admin/busdb/udump/busdb2_ora_165784.trc:
  10. ORA-00600: internal error code, arguments: [729], [1472128], [space leak], [], [], [], [], []
  11. Wed Dec 26 06:33:23 2012
  12. Trace dumping is performing id=[cdmp_20121226063323]
  13. Wed Dec 26 06:36:22 2012
  14. Trace dumping is performing id=[cdmp_20121226063633]
复制代码
MOS 上查到相关信息，除了打patch 没有更加治本的方法。
我想问刘大对于此问题的相关经验：
如果不打patch，uga 内存溢出，是否可以通过重启instance 来解决这个问题？

trc在附件中。

busdb1_ora_2240784.zip

401.23 KB, 下载次数: 1295
2#
发表于 2012-12-26 10:46:44
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining

+

System name:        AIX 5.3


Oracle process number: 534  PID 534


******** ERROR: UGA memory leak detected 766432 ********
  1. Process global information:
  2.      process: 70000080e3e30c8, call: 70000070e011530, xact: 0, curses: 7000008095e7030, usrses: 7000008095e7030
  3.   ----------------------------------------
  4.   SO: 70000080e3e30c8, type: 2, owner: 0, flag: INIT/-/-/0x00
  5.   (process) Oracle pid=534, calls cur/top: 70000070e011530/70000070e011530, flag: (0) -
  6.             int error: 0, call error: 0, sess error: 0, txn error 0
  7.   (post info) last post received: 0 0 55
  8.               last post received-location: kjata: wake up enqueue owner
  9.               last process to post me: 70000080a3ed8f8 1 6
  10.               last post sent: 0 0 24
  11.               last post sent-location: ksasnd
  12.               last process posted by me: 70000080c3be670 1 6
  13.     (latch info) wait_event=0 bits=0
  14.     Process Group: DEFAULT, pseudo proc: 70000080b495fa8
  15.     O/S info: user: oracle, term: UNKNOWN, ospid: 2240784
  16.     OSD pid info: Unix process pid: 2240784, image: oracle@busdbr1


  19.     SO: 7000008095e7030, type: 4, owner: 70000080e3e30c8, flag: INIT/-/-/0x00
  20.     (session) sid: 2071 trans: 0, creator: 70000080e3e30c8, flag: (41) USR/- BSY/-/-/DEL/-/-
  21.               DID: 0001-0216-0000008D, short-term DID: 0001-0216-0000008E
  22.               txn branch: 0
  23.               oct: 0, prv: 0, sql: 0, psql: 7000007b9c99e10, user: 254/BUSINESS
  24.     service name: busdb
  25.     O/S info: user: , term: , ospid: 1234, machine: CORE-BIZ-SRV-01
  26.               program:
  27.     last wait for 'SQL*Net message from client' blocking sess=0x0 seq=45596 wait_time=190 seconds since wait started=0
  28.                 driver id=28444553, #bytes=1, =0

  30. KSTDUMP


  33. C5F16A0B:F7853E34   534  2071 10425  33 kjss2m skip([0x37285f95][0x5937b931],[LB])[owner 66070][held-mode 3]
  34. C5F16C3C:F7853E35   534  2071 10706   6 0x000000001000010B 0x000000008D0F5A18 0x0000000039CD34F4 0x0000000000010020 0x0000000000000003 0x0000000000000000
  35. C5F16C43:F7853E36   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  36. C5F16C49:F7853E37   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  37. C5F16C4B:F7853E38   534  2071 10425  33 kjss2m skip([0x8d0f5a18][0x39cd34f4],[LB])[owner 66070][held-mode 3]
  38. C5F16EE7:F7853E39   534  2071 10706   6 0x000000001000010B 0x00000000F367F10B 0x00000000B635C6E0 0x0000000000010020 0x0000000000000003 0x0000000000000000
  39. C5F16EEE:F7853E3A   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000008
  40. C5F16EF5:F7853E3B   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  41. C5F17193:F7853E3C   534  2071 10706   6 0x000000001000010B 0x0000000047CEB5B2 0x00000000CE475EF5 0x0000000000010020 0x0000000000000003 0x0000000000000000
  42. C5F1719A:F7853E3D   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000006
  43. C5F171A1:F7853E3E   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  44. C5F1749D:F7853E41   534  2071 10706   6 0x000000001000010B 0x0000000011EF11F6 0x0000000045E3681C 0x0000000000010020 0x0000000000000003 0x0000000000000000
  45. C5F174A5:F7853E42   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  46. C5F174AB:F7853E43   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  47. C5F174AD:F7853E44   534  2071 10425  33 kjss2m skip([0x11ef11f6][0x45e3681c],[LB])[owner 66070][held-mode 3]
  48. C5F17746:F7853E47   534  2071 10706   6 0x000000001000010B 0x00000000BD9DDD75 0x00000000DEE09CC1 0x0000000000010020 0x0000000000000003 0x0000000000000000
  49. C5F1774C:F7853E48   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  50. C5F17753:F7853E49   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  51. C5F17755:F7853E4A   534  2071 10425  33 kjss2m skip([0xbd9ddd75][0xdee09cc1],[LB])[owner 66070][held-mode 3]
  52. C5F17991:F7853E4B   534  2071 10706   6 0x000000001000010B 0x000000007497D925 0x000000003FAD495B 0x0000000000010020 0x0000000000000003 0x0000000000000000
  53. C5F17998:F7853E4C   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  54. C5F1799E:F7853E4D   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  55. C5F179A0:F7853E4E   534  2071 10425  33 kjss2m skip([0x7497d925][0x3fad495b],[LB])[owner 66070][held-mode 3]
  56. C5F17B86:F7853E4F   534  2071 10706   6 0x000000001000010B 0x0000000023085019 0x0000000066829724 0x0000000000010020 0x0000000000000003 0x0000000000000000
  57. C5F17B8C:F7853E50   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  58. C5F17B94:F7853E51   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  59. C5F17D5F:F7853E52   534  2071 10706   6 0x000000001000010B 0x000000002025E33D 0x00000000B07BC789 0x0000000000010020 0x0000000000000003 0x0000000000000000
  60. C5F17D68:F7853E53   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x000000000000000A
  61. C5F17D6F:F7853E54   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  62. C5F17D71:F7853E55   534  2071 10425  33 kjss2m skip([0x2025e33d][0xb07bc789],[LB])[owner 66070][held-mode 3]
  63. C5F17FA7:F7853E5E   534  2071 10706   6 0x000000001000010B 0x000000006CFAA693 0x000000001D6E0CFC 0x0000000000010020 0x0000000000000003 0x0000000000000000
  64. C5F17FAE:F7853E5F   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  65. C5F17FB5:F7853E60   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  66. C5F18193:F7853E61   534  2071 10706   6 0x000000001000010B 0x00000000DB9D8AB8 0x00000000A49469C2 0x0000000000010020 0x0000000000000003 0x0000000000000000
  67. C5F1819C:F7853E62   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000008
  68. C5F181A2:F7853E63   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  69. C5F181A4:F7853E64   534  2071 10425  33 kjss2m skip([0xdb9d8ab8][0xa49469c2],[LB])[owner 66070][held-mode 3]
  70. C5F18500:F7853E67   534  2071 10706   6 0x000000001000010B 0x0000000021204C85 0x000000005CC78AC2 0x0000000000010020 0x0000000000000003 0x0000000000000000
  71. C5F18507:F7853E68   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  72. C5F1850E:F7853E69   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  73. C5F18EE3:F7853E6A   534  2071 10706   6 0x000000001000010B 0x00000000F459DEF8 0x000000000C918140 0x0000000000010020 0x0000000000000003 0x0000000000000000
  74. C5F18EF0:F7853E6B   534  2071 10401   1 KSXPVSND: client 2 tid(2,257,0x851cbd51) buf 0x70000080bccf988 sz 128
  75. C5F18F05:F7853E6C   534  2071 10005   1 KSL WAIT BEG [library cache lock] 504403191186910952/0x7000007aa44eee8 504403188834505320/0x70000071e0e1268 301/0x12d
  76. C5F18FC4:F7853E6D   534  2071 10401  66 KSXP_SND_CALLBACK:  request 0x0x110441f80, status 30
  77. C5F18FC6:F7853E6E   534  2071 10401  71 KSXPWAIT: Send compl suppressed and No requests. proc 0x70000080e3e30c8 haswrk 0
  78. C5F18FEB:F7853E72   534  2071 10005   2 KSL WAIT END [library cache lock] 504403191186910952/0x7000007aa44eee8 504403188834505320/0x70000071e0e1268 301/0x12d time=229
  79. C5F18FEB:F7853E73   534  2071 10005   3 KSL POST RCVD poster=6 loc='kjata: wake up enqueue owner' id1=0 id2=0 name=   type=0 fac#=3 facpost=1
  80. C5F18FEC:F7853E74   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000109
  81. C5F18FF7:F7853E75   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  82. C5F18FF9:F7853E76   534  2071 10425  33 kjss2m skip([0xf459def8][0xc918140],[LB])[owner 66070][held-mode 3]
  83. C5F19434:F7853E77   534  2071 10706   6 0x000000001000010B 0x00000000F459DEF8 0x000000000C918140 0x0000000000010020 0x0000000000000003 0x0000000000000000
  84. C5F1943C:F7853E78   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000009
  85. C5F19444:F7853E79   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  86. C5F19445:F7853E7A   534  2071 10425  33 kjss2m skip([0xf459def8][0xc918140],[LB])[owner 66070][held-mode 3]
  87. C5F1A1E7:F7853E83   534  2071 10706   6 0x000000001000010B 0x000000007B73AD23 0x00000000F492D808 0x0000000000010020 0x0000000000000003 0x0000000000000000
  88. C5F1A1F4:F7853E84   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x000000000000000E
  89. C5F1A1FF:F7853E85   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  90. C5F1A5B3:F7853E8A   534  2071 10706   6 0x000000001000010B 0x000000007B73AD23 0x00000000F492D808 0x0000000000010020 0x0000000000000003 0x0000000000000000
  91. C5F1A5BB:F7853E8B   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  92. C5F1A5C1:F7853E8C   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  93. C5F1AB09:F7853E8F   534  2071 10706   6 0x000000001000010B 0x000000009F9CD683 0x000000000C38CA16 0x0000000000010020 0x0000000000000003 0x0000000000000000
  94. C5F1AB12:F7853E90   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000008
  95. C5F1AB1A:F7853E91   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  96. C5F1AF9D:F7853E92   534  2071 10706   6 0x000000001000010B 0x000000009F9CD683 0x000000000C38CA16 0x0000000000010020 0x0000000000000003 0x0000000000000000
  97. C5F1AFA3:F7853E93   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  98. C5F1AFAA:F7853E94   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  99. C5F1B20B:F7853E95   534  2071 10401  33 KSXPMPRP: clid-inst 0x10001 buf 0x7000006c2636000 blen 8192 ukey 8 tid 396350 krqh 0x110440428
  100. C5F1B24C:F7853E96   534  2071 10401   1 KSXPVSND: client 2 tid(2,257,0x851cbe5c) buf 0x700000808762d70 sz 432
  101. C5F1B264:F7853E97   534  2071 10005   1 KSL WAIT BEG [gc cr request] 66/0x42 1292628/0x13b954 1/0x1
  102. C5F1B32C:F7853E98   534  2071 10401  66 KSXP_SND_CALLBACK:  request 0x0x110440bf8, status 30
  103. C5F1B403:F7853E99   534  2071 10401  13 KSXPMUPD: memory update krqh 0x110440428 srqh 0x1104404e8 buffer 0x7000006c2636000
  104. C5F1B405:F7853E9A   534  2071 10005   3 KSL POST RCVD poster=6 loc='kjata: wake up enqueue owner' id1=0 id2=0 name=   type=0 fac#=2 facpost=1
  105. C5F1B9BF:F7853E9D   534  2071 10706   6 0x000000001000010B 0x00000000E7AB66ED 0x00000000043DC806 0x0000000000010020 0x0000000000000003 0x0000000000000000
  106. C5F1B9CD:F7853E9E   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x000000000000000E
  107. C5F1BAEF:F7853EA1   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  108. C5F1EB14:F7853ED2   534  2071 10706   6 0x000000001000010B 0x00000000DB46F820 0x00000000B5A75841 0x0000000000010020 0x0000000000000003 0x0000000000000000
  109. C5F1EB24:F7853ED3   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000011
  110. C5F1EB2F:F7853ED4   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  111. C5F1EF40:F7853ED5   534  2071 10706   6 0x000000001000010B 0x00000000DB46F820 0x00000000B5A75841 0x0000000000010020 0x0000000000000003 0x0000000000000000
  112. C5F1EF48:F7853ED6   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000008
  113. C5F1EF4E:F7853ED7   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  114. C5F1F554:F7853EE0   534  2071 10706   6 0x000000001000010B 0x00000000DB46F820 0x00000000B5A75841 0x0000000000010020 0x0000000000000003 0x0000000000000000
  115. C5F1F55B:F7853EE1   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000007
  116. C5F1F562:F7853EE2   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  117. C5F1F91F:F7853EE5   534  2071 10706   6 0x000000001000010B 0x00000000DB46F820 0x00000000B5A75841 0x0000000000010020 0x0000000000000003 0x0000000000000000
  118. C5F1F926:F7853EE6   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000008
  119. C5F1F92D:F7853EE7   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  120. C62F15ED:F7859CE5   534  2071 10706   6 0x000000001000010B 0x00000000691EE25B 0x00000000A57B3587 0x0000000000010020 0x0000000000000003 0x0000000000000000
  121. C62F160E:F7859CE6   534  2071 10401   1 KSXPVSND: client 2 tid(2,257,0x851cbd51) buf 0x70000080bccf988 sz 128
  122. C62F162A:F7859CE7   534  2071 10005   1 KSL WAIT BEG [library cache lock] 504403191282064680/0x7000007aff0dd28 504403189170098056/0x7000007320ecf88 301/0x12d
  123. C62F1749:F7859CE9   534  2071 10401  66 KSXP_SND_CALLBACK:  request 0x0x110440810, status 30
  124. C62F174B:F7859CEA   534  2071 10401  71 KSXPWAIT: Send compl suppressed and No requests. proc 0x70000080e3e30c8 haswrk 0
  125. C62F1776:F7859CF1   534  2071 10005   2 KSL WAIT END [library cache lock] 504403191282064680/0x7000007aff0dd28 504403189170098056/0x7000007320ecf88 301/0x12d time=331
  126. C62F1777:F7859CF2   534  2071 10005   3 KSL POST RCVD poster=6 loc='kjata: wake up enqueue owner' id1=0 id2=0 name=   type=0 fac#=3 facpost=1
  127. C62F1779:F7859CF4   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x000000000000018E
  128. C62F1CD0:F7859D0D   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  129. C62F1CD6:F7859D0E   534  2071 10425  33 kjss2m skip([0x691ee25b][0xa57b3587],[LB])[owner 66070][held-mode 3]
  130. C62F2D5A:F7859D58   534  2071 10706   6 0x000000001000010B 0x00000000691EE25B 0x00000000A57B3587 0x0000000000010020 0x0000000000000003 0x0000000000000000
  131. C62F2D6A:F7859D59   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x0000000000000010
  132. C62F3100:F7859D66   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  133. C62F3104:F7859D67   534  2071 10425  33 kjss2m skip([0x691ee25b][0xa57b3587],[LB])[owner 66070][held-mode 3]
  134. C62F3F3C:F7859D96   534  2071 10706   6 0x000000001000010B 0x00000000691EE25B 0x00000000A57B3587 0x0000000000010020 0x0000000000000003 0x0000000000000000
  135. C62F3F4A:F7859D98   534  2071 10706   7 0x070000080E982308 0x0000000000000000 0x000000000000000E
  136. C62F42D7:F7859DA9   534  2071 10706  15 0x070000080E982308 0x0000000000000000
  137. C62F42DA:F7859DAA   534  2071 10425  33 kjss2m skip([0x691ee25b][0xa57b3587],[LB])[owner 66070][held-mode 3]
复制代码
KSTDUMP 中较多 library cache lock + LB kjss2m等待

回复 只看该作者 道具 举报

3#
发表于 2012-12-26 10:49:04
请上传问题时段的AWR和ADDM

回复 只看该作者 道具 举报

4#
发表于 2012-12-26 11:09:21

awr

本帖最后由 monkeybron 于 2012-12-26 13:36 编辑

好的。刘大。现在比较忙。下午发给您

已经上传awr

awrrpt_1_22217_22218.zip

59.81 KB, 下载次数: 1351

回复 只看该作者 道具 举报

5#
发表于 2012-12-26 13:43:36
  1. SQL> show parameter pga_aggregate_target

  3. NAME                                 TYPE        VALUE
  4. ------------------------------------ ----------- ------------------------------
  5. pga_aggregate_target                 big integer 6287M
  6. SQL>
  7. SQL> show parameter work

  9. NAME                                 TYPE        VALUE
  10. ------------------------------------ ----------- ------------------------------
  11. fileio_network_adapters              string
  12. workarea_size_policy                 string      AUTO
复制代码
另外：数据库是专有模式数据库pga 是自动管理的
在awr 中TOP5也没可能到明显的LC cache 的问题，更多的是并发：cursor: pin S wait on X

回复 只看该作者 道具 举报

6#
发表于 2012-12-26 14:53:48
monkeybron 发表于 2012-12-26 13:43
另外：数据库是专有模式数据库pga 是自动管理的
在awr 中TOP5也没可能到明显的LC cache 的问题，更多的是并 ...



Snap Id
Snap Time
Sessions
Cursors/Session
Begin Snap:
22217
26-Dec-12 08:00:09
1184
8.2
End Snap:
22218
26-Dec-12 09:00:15
1203
9.2
Elapsed:
60.11 (mins)
DB Time:
607.80 (mins)




Per Second
Per Transaction
Redo size:
56,659.15
5,108.76
Logical reads:
199,930.54
18,027.03
Block changes:
269.30
24.28
Physical reads:
15,478.91
1,395.68
Physical writes:
44.26
3.99
User calls:
3,317.67
299.14
Parses:
726.77
65.53
Hard parses:
241.02
21.73
Sorts:
10,561.42
952.29
Logons:
0.06
0.01
Executes:
2,063.78
186.08
Transactions:
11.09



这个快照负载不低啊， 尤其指定注意的是 每秒硬解析241次， 耗费1488s的DB TIME



hard parse elapsed time        1,488.85       




cursor_space_for_time        TRUE 这个参数非推荐



PGA Target Est (MB)
Size Factr
W/A MB Processed
Estd Extra W/A MB Read/ Written to Disk
Estd PGA Cache Hit %
Estd PGA Overalloc Count
786
0.13
12,322,504.25
1,735,245.76
88.00
33,754
1,572
0.25
12,322,504.25
1,712,451.58
88.00
32,530
3,144
0.50
12,322,504.25
1,377,710.46
90.00
24,355
4,715
0.75
12,322,504.25
1,018,126.48
92.00
18,104
6,287
1.00
12,322,504.25
211,253.94
98.00
6,819
7,544
1.20
12,322,504.25
148,607.50
99.00
0
8,802
1.40
12,322,504.25
139,732.13
99.00
0
10,059
1.60
12,322,504.25
139,732.13
99.00
0
11,317
1.80
12,322,504.25
139,732.13
99.00
0
12,574
2.00
12,322,504.25
139,732.13
99.00
0
18,861
3.00
12,322,504.25
139,732.13
99.00
0
25,148
4.00
12,322,504.25
139,732.13
99.00
0
37,722
6.00
12,322,504.25
139,732.13
99.00
0
50,296
8.00
12,322,504.25
139,732.13
99.00
0



从PGA Memory Advisory看 目前pga_aggregate_target=6g时 仍存在PGA overalloc，考虑增大pga_aggregate_target 到8g左右


1. 考虑优化解析减少hard parse，可以有效减少 PGA的使用
2、 考虑上面提到的参数
3、 考虑增大pga_aggregate_target 减少PGA overalloc

回复 只看该作者 道具 举报

7#
发表于 2012-12-26 15:27:47
感谢刘大回复！学习了

回复 只看该作者 道具 举报

8#
发表于 2012-12-26 16:07:46
确实。对比了上线前的awr，发现6g时候的pga 的overalloc 只有7，现在增加到了6,819。表明增加pga的大小能够解决这个pga （uga）分配的问题.再次感谢。

回复 只看该作者 道具 举报

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

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

GMT+8, 2024-12-26 01:56 , Processed in 0.053296 second(s), 23 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

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