ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > UNDO表空间100%诊断

UNDO表空间100%诊断

原创 Oracle 作者：myy10146 时间：2014-03-17 23:17:45 0 删除 编辑

一、检查日志

1、查看数据库警告日志

无报错

2、查看undo相关参数设置


点击(此处)折叠或打开

  1. SQL> show parameter undo
  2. NAME TYPE VALUE
  3. ------------------------------------ ----------- ------------------------------
  4. undo_management string AUTO
  5. undo_retention integer 900
  6. undo_tablespace string UNDOTBS3


3、分析undo空间的使用情况


点击(此处)折叠或打开

  1. SQL> SELECT TABLESPACE_NAME,STATUS,TRUNC(SUM(BLOCKS) * 8 / 1024) AS \"Size M\",
  2.   2 COUNT(*) Undo_Extent_Num
  3.   3 FROM DBA_UNDO_EXTENTS
  4.   4 GROUP BY TABLESPACE_NAME, STATUS;
  5.  
  6. TABLESPACE_NAME STATUS Size M UNDO_EXTENT_NUM
  7. ------------------------------ --------- ---------- ---------------
  8. UNDOTBS3 UNEXPIRED 20406 4318
  9. UNDOTBS1 EXPIRED 525 477
  10. UNDOTBS3 ACTIVE 16 2
  11. UNDOTBS3 EXPIRED 56 138

发现存在大量UNEXPIRED的undo信息，有大量undo信息未过期，占用着undo表空间。


4.分析undo信息

点击(此处)折叠或打开

  1. SQL> alter session set nls_date_format = \'yyyy-mm-dd hh24:mi:ss\';
  2. SQL> select BEGIN_TIME,END_TIME,MAXQUERYLEN,MAXQUERYSQLID,TUNED_UNDORETENTION from DBA_HIST_UNDOSTAT;
  3.  
  4. BEGIN_TIME END_TIME MAXQUERYLEN MAXQUERYSQLID TUNED_UNDORETENTION
  5. ------------------- ------------------- ----------- ------------- -------------------
  6. 2012-09-07 15:50:51 2012-09-07 16:00:51 0 900
  7. 2012-09-07 16:00:51 2012-09-07 16:10:51 0 900
  8. 2012-09-07 16:10:51 2012-09-07 16:20:51 0 900
  9. 2012-09-07 16:20:51 2012-09-07 16:30:51 0 900
  10. 2012-09-07 16:30:51 2012-09-07 16:40:51 0 900
  11. 2012-09-07 16:40:51 2012-09-07 16:50:51 0 900
  12. 2012-09-10 04:00:51 2012-09-10 04:10:51 0 900
  13. 2012-09-10 04:10:51 2012-09-10 04:20:51 0 900
  14. 2012-09-10 04:20:51 2012-09-10 04:30:51 0 900
  15. 2012-09-10 04:30:51 2012-09-10 04:40:51 0 900
  16. 2012-09-10 04:40:51 2012-09-10 04:50:51 0 900
  17. 2012-09-10 23:55:07 2012-09-11 00:05:07 0 900
  18. 2012-09-11 00:05:07 2012-09-11 00:15:07 0 900
  19. 2012-09-11 00:15:07 2012-09-11 00:25:07 0 900
  20. 2012-09-11 00:25:07 2012-09-11 00:35:07 0 900
  21. 2012-09-11 00:35:07 2012-09-11 00:45:07 113 agw37z64272ga 900
  22. 2012-09-11 00:45:07 2012-09-11 00:55:07 0 900
  23. 2012-09-12 15:55:07 2012-09-12 16:05:07 24618 gc4xxp228mra3 25519
  24. 2012-09-12 16:05:07 2012-09-12 16:15:07 25220 gc4xxp228mra3 26120
  25. 2012-09-12 16:15:07 2012-09-12 16:25:07 25820 gc4xxp228mra3 26720
  26. 2012-09-12 16:25:07 2012-09-12 16:35:07 26422 gc4xxp228mra3 27320
  27. 2012-09-12 16:35:07 2012-09-12 16:45:07 27020 gc4xxp228mra3 27923
  28. 2012-09-12 16:45:07 2012-09-12 16:55:07 27624 gc4xxp228mra3 28523
  29. 2012-09-13 12:55:07 2012-09-13 13:05:07 100353 gc4xxp228mra3 101073
  30. 2012-09-13 13:05:07 2012-09-13 13:15:07 100954 gc4xxp228mra3 101674
  31. 2012-09-13 13:15:07 2012-09-13 13:25:07 101554 gc4xxp228mra3 102274
  32. 2012-09-13 13:25:07 2012-09-13 13:35:07 102156 gc4xxp228mra3 102877
  33. 2012-09-13 13:35:07 2012-09-13 13:45:07 102757 gc4xxp228mra3 103478
  34. 2012-09-13 13:45:07 2012-09-13 13:55:07 103358 gc4xxp228mra3 104077
  35. 2012-09-13 17:55:07 2012-09-13 18:05:07 118385 gc4xxp228mra3 119106
  36. 2012-09-13 18:05:07 2012-09-13 18:15:07 118986 gc4xxp228mra3 119704
  37. 2012-09-13 18:15:07 2012-09-13 18:25:07 119587 gc4xxp228mra3 120307
  38. 2012-09-13 18:25:07 2012-09-13 18:35:07 120189 gc4xxp228mra3 120908
  39. 2012-09-13 18:35:07 2012-09-13 18:45:07 120789 gc4xxp228mra3 121509
  40. 2012-09-13 18:45:07 2012-09-13 18:55:07 121390 gc4xxp228mra3 122110
  41.  
  42.  
  43. SQL> select BEGIN_TIME,END_TIME,MAXQUERYID,MAXQUERYLEN,SSOLDERRCNT,TUNED_UNDORETENTION from v$undostat;
  44.  
  45. BEGIN_TIME END_TIME MAXQUERYID MAXQUERYLEN SSOLDERRCNT TUNED_UNDORETENTION
  46. ------------------- ------------------- ------------- ----------- ----------- -------------------
  47. 2012-09-14 04:55:07 2012-09-14 05:02:07 gc4xxp228mra3 157756 0 158477
  48. 2012-09-14 04:45:07 2012-09-14 04:55:07 gc4xxp228mra3 157456 0 158115
  49. 2012-09-14 04:35:07 2012-09-14 04:45:07 gc4xxp228mra3 156855 0 157515
  50. 2012-09-14 04:25:07 2012-09-14 04:35:07 gc4xxp228mra3 156254 0 156914
  51. 2012-09-14 04:15:07 2012-09-14 04:25:07 gc4xxp228mra3 155653 0 156312
  52. 2012-09-14 04:05:07 2012-09-14 04:15:07 gc4xxp228mra3 155051 0 155711
  53. 2012-09-14 03:55:07 2012-09-14 04:05:07 gc4xxp228mra3 154451 0 155111
  54. 2012-09-14 03:45:07 2012-09-14 03:55:07 gc4xxp228mra3 153848 0 154508
  55. 2012-09-14 03:35:07 2012-09-14 03:45:07 gc4xxp228mra3 153248 0 153908
  56. 2012-09-14 03:25:07 2012-09-14 03:35:07 gc4xxp228mra3 152647 0 153307
  57. 2012-09-14 03:15:07 2012-09-14 03:25:07 gc4xxp228mra3 152046 0 152706
  58. 2012-09-14 03:05:07 2012-09-14 03:15:07 gc4xxp228mra3 151445 0 152105
  59. 2012-09-14 02:55:07 2012-09-14 03:05:07 gc4xxp228mra3 150844 0 151502
  60. 2012-09-14 02:45:07 2012-09-14 02:55:07 gc4xxp228mra3 150242 0 150902
  61. 2012-09-14 02:35:07 2012-09-14 02:45:07 gc4xxp228mra3 149642 0 150302
  62. 2012-09-14 02:25:07 2012-09-14 02:35:07 gc4xxp228mra3 149041 0 149701
  63. 2012-09-14 02:15:07 2012-09-14 02:25:07 gc4xxp228mra3 148438 0 149098
  64. 2012-09-14 02:05:07 2012-09-14 02:15:07 gc4xxp228mra3 147839 0 148499
  65. 2012-09-14 01:55:07 2012-09-14 02:05:07 gc4xxp228mra3 147237 0 147898
  66. 2012-09-14 01:45:07 2012-09-14 01:55:07 gc4xxp228mra3 146635 0 147294
  67. 2012-09-14 01:35:07 2012-09-14 01:45:07 gc4xxp228mra3 146035 0 146694
  68. 2012-09-14 01:25:07 2012-09-14 01:35:07 gc4xxp228mra3 145434 0 146094
  69. 2012-09-14 01:15:07 2012-09-14 01:25:07 gc4xxp228mra3 144833 0 145493
  70. 2012-09-14 01:05:07 2012-09-14 01:15:07 gc4xxp228mra3 144231 0 144891
  71. 2012-09-14 00:55:07 2012-09-14 01:05:07 gc4xxp228mra3 143631 0 144291
  72. 2012-09-14 00:45:07 2012-09-14 00:55:07 gc4xxp228mra3 143030 0 143688
  73. 2012-09-14 00:35:07 2012-09-14 00:45:07 gc4xxp228mra3 142428 0 143088
  74. 2012-09-14 00:25:07 2012-09-14 00:35:07 gc4xxp228mra3 141828 0 142487
  75. 2012-09-14 00:15:07 2012-09-14 00:25:07 gc4xxp228mra3 141227 0 141884
  76. 2012-09-14 00:05:07 2012-09-14 00:15:07 gc4xxp228mra3 140624 0 141284
  77. 2012-09-13 23:55:07 2012-09-14 00:05:07 gc4xxp228mra3 140025 0 140685
  78. 2012-09-13 23:45:07 2012-09-13 23:55:07 gc4xxp228mra3 139424 0 140081
  79. 2012-09-13 23:35:07 2012-09-13 23:45:07 gc4xxp228mra3 138823 0 139481
  80. 2012-09-13 23:25:07 2012-09-13 23:35:07 gc4xxp228mra3 138221 0 138882
  81. 2012-09-13 23:15:07 2012-09-13 23:25:07 gc4xxp228mra3 137619 0 138280
  82. 2012-09-13 23:05:07 2012-09-13 23:15:07 gc4xxp228mra3 137017 0 137679
  83. 2012-09-13 22:55:07 2012-09-13 23:05:07 gc4xxp228mra3 136418 0 137078
  84. 2012-09-13 22:45:07 2012-09-13 22:55:07 gc4xxp228mra3 135814 0 136474
  85. 2012-09-13 22:35:07 2012-09-13 22:45:07 gc4xxp228mra3 135216 0 135874
  86. 2012-09-13 22:25:07 2012-09-13 22:35:07 gc4xxp228mra3 134615 0 135275
  87. 2012-09-13 22:15:07 2012-09-13 22:25:07 gc4xxp228mra3 134014 0 134674
  88. 2012-09-13 22:05:07 2012-09-13 22:15:07 gc4xxp228mra3 133413 0 134071
  89. 2012-09-13 21:55:07 2012-09-13 22:05:07 gc4xxp228mra3 132812 0 133472
  90. 2012-09-13 21:45:07 2012-09-13 21:55:07 gc4xxp228mra3 132210 0 132871
  91. 2012-09-13 21:35:07 2012-09-13 21:45:07 gc4xxp228mra3 131607 0 132267
  92. 2012-09-13 21:25:07 2012-09-13 21:35:07 gc4xxp228mra3 131008 0 131668
  93. 2012-09-13 21:15:07 2012-09-13 21:25:07 gc4xxp228mra3 130407 0 131067
  94. 2012-09-13 21:05:07 2012-09-13 21:15:07 gc4xxp228mra3 129804 0 130524
  95. 2012-09-13 20:55:07 2012-09-13 21:05:07 gc4xxp228mra3 129205 0 129924
  96. 2012-09-13 20:45:07 2012-09-13 20:55:07 gc4xxp228mra3 128602 0 129324
  97. 2012-09-13 20:35:07 2012-09-13 20:45:07 gc4xxp228mra3 128003 0 128721
  98. 2012-09-13 20:25:07 2012-09-13 20:35:07 gc4xxp228mra3 127402 0 128121
  99. 2012-09-13 20:15:07 2012-09-13 20:25:07 gc4xxp228mra3 126800 0 127521
  100. 2012-09-13 20:05:07 2012-09-13 20:15:07 gc4xxp228mra3 126199 0 126920
  101. 2012-09-13 19:55:07 2012-09-13 20:05:07 gc4xxp228mra3 125598 0 126317
  102. 2012-09-13 19:45:07 2012-09-13 19:55:07 gc4xxp228mra3 124997 0 125717
  103. 2012-09-13 19:35:07 2012-09-13 19:45:07 gc4xxp228mra3 124396 0 125114
  104. 2012-09-13 19:25:07 2012-09-13 19:35:07 gc4xxp228mra3 123795 0 124515
  105. 2012-09-13 19:15:07 2012-09-13 19:25:07 gc4xxp228mra3 123194 0 123914
  106. 2012-09-13 19:05:07 2012-09-13 19:15:07 gc4xxp228mra3 122593 0 123311
  107. 2012-09-13 18:55:07 2012-09-13 19:05:07 gc4xxp228mra3 121992 0 122710
  108. 2012-09-13 18:45:07 2012-09-13 18:55:07 gc4xxp228mra3 121390 0 122110
  109. 2012-09-13 18:35:07 2012-09-13 18:45:07 gc4xxp228mra3 120789 0 121509
  110. 2012-09-13 18:25:07 2012-09-13 18:35:07 gc4xxp228mra3 120189 0 120908
  111. 2012-09-13 18:15:07 2012-09-13 18:25:07 gc4xxp228mra3 119587 0 120307
  112. 2012-09-13 18:05:07 2012-09-13 18:15:07 gc4xxp228mra3 118986 0 119704
  113. 2012-09-13 17:55:07 2012-09-13 18:05:07 gc4xxp228mra3 118385 0 119106
  114. 2012-09-13 17:45:07 2012-09-13 17:55:07 gc4xxp228mra3 117784 0 118504
  115. 2012-09-13 17:35:07 2012-09-13 17:45:07 gc4xxp228mra3 117180 0 117901
  116. 2012-09-13 17:25:07 2012-09-13 17:35:07 gc4xxp228mra3 116582 0 117300
  117. 2012-09-13 17:15:07 2012-09-13 17:25:07 gc4xxp228mra3 115980 0 116700
  118. 2012-09-13 17:05:07 2012-09-13 17:15:07 gc4xxp228mra3 115377 0 116100
  119. 2012-09-13 16:55:07 2012-09-13 17:05:07 gc4xxp228mra3 114779 0 115499
  120. 2012-09-13 16:45:07 2012-09-13 16:55:07 gc4xxp228mra3 114178 0 114898
  121. 2012-09-13 16:35:07 2012-09-13 16:45:07 gc4xxp228mra3 113576 0 114297
  122. 2012-09-13 16:25:07 2012-09-13 16:35:07 gc4xxp228mra3 112976 0 113696
  123. 2012-09-13 16:15:07 2012-09-13 16:25:07 gc4xxp228mra3 112375 0 113094
  124. 2012-09-13 16:05:07 2012-09-13 16:15:07 gc4xxp228mra3 111773 0 112493
  125. 2012-09-13 15:55:07 2012-09-13 16:05:07 gc4xxp228mra3 111172 0 111893
  126. 2012-09-13 15:45:07 2012-09-13 15:55:07 gc4xxp228mra3 110570 0 111291
  127. 2012-09-13 15:35:07 2012-09-13 15:45:07 gc4xxp228mra3 109970 0 110690
  128. 2012-09-13 15:25:07 2012-09-13 15:35:07 gc4xxp228mra3 109369 0 110089
  129. 2012-09-13 15:15:07 2012-09-13 15:25:07 gc4xxp228mra3 108767 0 109487
  130. 2012-09-13 15:05:07 2012-09-13 15:15:07 gc4xxp228mra3 108167 0 108887
  131. 2012-09-13 14:55:07 2012-09-13 15:05:07 gc4xxp228mra3 107566 0 108286
  132. 2012-09-13 14:45:07 2012-09-13 14:55:07 gc4xxp228mra3 106965 0 107685
  133. 2012-09-13 14:35:07 2012-09-13 14:45:07 gc4xxp228mra3 106364 0 107084
  134. 2012-09-13 14:25:07 2012-09-13 14:35:07 gc4xxp228mra3 105763 0 106483
  135. 2012-09-13 14:15:07 2012-09-13 14:25:07 gc4xxp228mra3 105162 0 105882
  136. 2012-09-13 14:05:07 2012-09-13 14:15:07 gc4xxp228mra3 104560 0 105280
  137. 2012-09-13 13:55:07 2012-09-13 14:05:07 gc4xxp228mra3 103960 0 104680
  138. 2012-09-13 13:45:07 2012-09-13 13:55:07 gc4xxp228mra3 103358 0 104077
  139. 2012-09-13 13:35:07 2012-09-13 13:45:07 gc4xxp228mra3 102757 0 103478
  140. 2012-09-13 13:25:07 2012-09-13 13:35:07 gc4xxp228mra3 102156 0 102877
  141. 2012-09-13 13:15:07 2012-09-13 13:25:07 gc4xxp228mra3 101554 0 102274
  142. 2012-09-13 13:05:07 2012-09-13 13:15:07 gc4xxp228mra3 100954 0 101674
  143. 2012-09-13 12:55:07 2012-09-13 13:05:07 gc4xxp228mra3 100353 0 101073
  144. 2012-09-13 12:45:07 2012-09-13 12:55:07 gc4xxp228mra3 99752 0 100471
  145. 2012-09-13 12:35:07 2012-09-13 12:45:07 gc4xxp228mra3 99151 0 99871
  146. 2012-09-13 12:25:07 2012-09-13 12:35:07 gc4xxp228mra3 98550 0 99270
  147. 2012-09-13 12:15:07 2012-09-13 12:25:07 gc4xxp228mra3 97949 0 98669
  148. 2012-09-13 12:05:07 2012-09-13 12:15:07 gc4xxp228mra3 97348 0 98067
  149. 2012-09-13 11:55:07 2012-09-13 12:05:07 gc4xxp228mra3 96744 0 97467
  150. 2012-09-13 11:45:07 2012-09-13 11:55:07 gc4xxp228mra3 96146 0 96924
  151. 2012-09-13 11:35:07 2012-09-13 11:45:07 gc4xxp228mra3 95545 0 96324
  152. 2012-09-13 11:25:07 2012-09-13 11:35:07 gc4xxp228mra3 94944 0 95721
  153. 2012-09-13 11:15:07 2012-09-13 11:25:07 gc4xxp228mra3 94342 0 95121
  154. 2012-09-13 11:05:07 2012-09-13 11:15:07 gc4xxp228mra3 93741 0 94521
  155. 2012-09-13 10:55:07 2012-09-13 11:05:07 gc4xxp228mra3 93138 0 93921
  156. 2012-09-13 10:45:07 2012-09-13 10:55:07 gc4xxp228mra3 92539 0 93318
  157. 2012-09-13 10:35:07 2012-09-13 10:45:07 gc4xxp228mra3 91938 0 92718
  158. 2012-09-13 10:25:07 2012-09-13 10:35:07 gc4xxp228mra3 91336 0 92118
  159. 2012-09-13 10:15:07 2012-09-13 10:25:07 gc4xxp228mra3 90735 0 91515
  160. 2012-09-13 10:05:07 2012-09-13 10:15:07 gc4xxp228mra3 90135 0 90914
  161. 2012-09-13 09:55:07 2012-09-13 10:05:07 gc4xxp228mra3 89534 0 90314
  162. 2012-09-13 09:45:07 2012-09-13 09:55:07 gc4xxp228mra3 88933 0 89711
  163. 2012-09-13 09:35:07 2012-09-13 09:45:07 gc4xxp228mra3 88332 0 89112
  164. 2012-09-13 09:25:07 2012-09-13 09:35:07 gc4xxp228mra3 87731 0 88511
  165. 2012-09-13 09:15:07 2012-09-13 09:25:07 gc4xxp228mra3 87130 0 87910
  166. 2012-09-13 09:05:07 2012-09-13 09:15:07 gc4xxp228mra3 86528 0 87308
  167. 2012-09-13 08:55:07 2012-09-13 09:05:07 gc4xxp228mra3 85928 0 86708
  168. 2012-09-13 08:45:07 2012-09-13 08:55:07 gc4xxp228mra3 85325 0 86105
  169. 2012-09-13 08:35:07 2012-09-13 08:45:07 gc4xxp228mra3 84726 0 85504
  170. 2012-09-13 08:25:07 2012-09-13 08:35:07 gc4xxp228mra3 84125 0 84905
  171. 2012-09-13 08:15:07 2012-09-13 08:25:07 gc4xxp228mra3 83524 0 84301
  172. 2012-09-13 08:05:07 2012-09-13 08:15:07 gc4xxp228mra3 82921 0 83701
  173. 2012-09-13 07:55:07 2012-09-13 08:05:07 gc4xxp228mra3 82321 0 83101
  174. 2012-09-13 07:45:07 2012-09-13 07:55:07 gc4xxp228mra3 81720 0 82501
  175. 2012-09-13 07:35:07 2012-09-13 07:45:07 gc4xxp228mra3 81119 0 81898
  176. 2012-09-13 07:25:07 2012-09-13 07:35:07 gc4xxp228mra3 80517 0 81298
  177. 2012-09-13 07:15:07 2012-09-13 07:25:07 gc4xxp228mra3 79916 0 80697
  178. 2012-09-13 07:05:07 2012-09-13 07:15:07 gc4xxp228mra3 79316 0 80094
  179. 2012-09-13 06:55:07 2012-09-13 07:05:07 gc4xxp228mra3 78714 0 79495
  180. 2012-09-13 06:45:07 2012-09-13 06:55:07 gc4xxp228mra3 78114 0 78894
  181. 2012-09-13 06:35:07 2012-09-13 06:45:07 gc4xxp228mra3 77513 0 78293
  182. 2012-09-13 06:25:07 2012-09-13 06:35:07 gc4xxp228mra3 76911 0 77692
  183. 2012-09-13 06:15:07 2012-09-13 06:25:07 gc4xxp228mra3 76311 0 77091
  184. 2012-09-13 06:05:07 2012-09-13 06:15:07 gc4xxp228mra3 75710 0 76488
  185. 2012-09-13 05:55:07 2012-09-13 06:05:07 gc4xxp228mra3 75109 0 75889
  186. 2012-09-13 05:45:07 2012-09-13 05:55:07 gc4xxp228mra3 74508 0 75288
  187. 2012-09-13 05:35:07 2012-09-13 05:45:07 gc4xxp228mra3 73907 0 74687
  188. 2012-09-13 05:25:07 2012-09-13 05:35:07 gc4xxp228mra3 73304 0 74084
  189. 2012-09-13 05:15:07 2012-09-13 05:25:07 gc4xxp228mra3 72704 0 73484
  190. 2012-09-13 05:05:07 2012-09-13 05:15:07 gc4xxp228mra3 72103 0 72884
  191. 2012-09-13 04:55:07 2012-09-13 05:05:07 gc4xxp228mra3 71500 0 72283
  192. 2012-09-13 04:45:07 2012-09-13 04:55:07 gc4xxp228mra3 70901 0 71680
  193. 2012-09-13 04:35:07 2012-09-13 04:45:07 gc4xxp228mra3 70300 0 71080
  194. 2012-09-13 04:25:07 2012-09-13 04:35:07 gc4xxp228mra3 69699 0 70480
  195. 2012-09-13 04:15:07 2012-09-13 04:25:07 gc4xxp228mra3 69098 0 69879
  196. 2012-09-13 04:05:07 2012-09-13 04:15:07 gc4xxp228mra3 68496 0 69278
  197. 2012-09-13 03:55:07 2012-09-13 04:05:07 gc4xxp228mra3 67896 0 68677
  198. 2012-09-13 03:45:07 2012-09-13 03:55:07 gc4xxp228mra3 67295 0 68075
  199. 2012-09-13 03:35:07 2012-09-13 03:45:07 gc4xxp228mra3 66694 0 67475
  200. 2012-09-13 03:25:07 2012-09-13 03:35:07 gc4xxp228mra3 66093 0 66873
  201. 2012-09-13 03:15:07 2012-09-13 03:25:07 gc4xxp228mra3 65492 0 66270
  202. 2012-09-13 03:05:07 2012-09-13 03:15:07 gc4xxp228mra3 64891 0 65671
  203. 2012-09-13 02:55:07 2012-09-13 03:05:07 gc4xxp228mra3 64289 0 65070
  204. 2012-09-13 02:45:07 2012-09-13 02:55:07 gc4xxp228mra3 63689 0 64469
  205. 2012-09-13 02:35:07 2012-09-13 02:45:07 gc4xxp228mra3 63088 0 63926
  206. 2012-09-13 02:25:07 2012-09-13 02:35:07 gc4xxp228mra3 62487 0 63267
  207. 2012-09-13 02:15:07 2012-09-13 02:25:07 gc4xxp228mra3 61885 0 62726
  208. 2012-09-13 02:05:07 2012-09-13 02:15:07 gc4xxp228mra3 61282 0 62125
  209. 2012-09-13 01:55:07 2012-09-13 02:05:07 gc4xxp228mra3 60682 0 61523
  210. 2012-09-13 01:45:07 2012-09-13 01:55:07 gc4xxp228mra3 60082 0 60922
  211. 2012-09-13 01:35:07 2012-09-13 01:45:07 gc4xxp228mra3 59480 0 60319
  212. 2012-09-13 01:25:07 2012-09-13 01:35:07 gc4xxp228mra3 58879 0 59719
  213. 2012-09-13 01:15:07 2012-09-13 01:25:07 gc4xxp228mra3 58279 0 59119
  214. 2012-09-13 01:05:07 2012-09-13 01:15:07 gc4xxp228mra3 57678 0 58516
  215. 2012-09-13 00:55:07 2012-09-13 01:05:07 gc4xxp228mra3 57075 0 57917
  216. 2012-09-13 00:45:07 2012-09-13 00:55:07 gc4xxp228mra3 56476 0 57315
  217. 2012-09-13 00:35:07 2012-09-13 00:45:07 gc4xxp228mra3 55874 0 56714
  218. 2012-09-13 00:25:07 2012-09-13 00:35:07 gc4xxp228mra3 55272 0 56112
  219. 2012-09-13 00:15:07 2012-09-13 00:25:07 gc4xxp228mra3 54672 0 55512
  220. 2012-09-13 00:05:07 2012-09-13 00:15:07 gc4xxp228mra3 54069 0 54912
  221. 2012-09-12 23:55:07 2012-09-13 00:05:07 gc4xxp228mra3 53469 0 54309
  222. 2012-09-12 23:45:07 2012-09-12 23:55:07 gc4xxp228mra3 52870 0 53709
  223. 2012-09-12 23:35:07 2012-09-12 23:45:07 gc4xxp228mra3 52268 0 53108
  224. 2012-09-12 23:25:07 2012-09-12 23:35:07 gc4xxp228mra3 51665 0 52505
  225. 2012-09-12 23:15:07 2012-09-12 23:25:07 gc4xxp228mra3 51065 0 51905
  226. 2012-09-12 23:05:07 2012-09-12 23:15:07 gc4xxp228mra3 50465 0 51305
  227. 2012-09-12 22:55:07 2012-09-12 23:05:07 gc4xxp228mra3 49863 0 50704
  228. 2012-09-12 22:45:07 2012-09-12 22:55:07 gc4xxp228mra3 49263 0 50102
  229. 2012-09-12 22:35:07 2012-09-12 22:45:07 gc4xxp228mra3 48662 0 49501
  230. 2012-09-12 22:25:07 2012-09-12 22:35:07 gc4xxp228mra3 48059 0 48901
  231. 2012-09-12 22:15:07 2012-09-12 22:25:07 gc4xxp228mra3 47460 0 48298
  232. 2012-09-12 22:05:07 2012-09-12 22:15:07 gc4xxp228mra3 46858 0 47698
  233. 2012-09-12 21:55:07 2012-09-12 22:05:07 gc4xxp228mra3 46255 0 47098
  234. 2012-09-12 21:45:07 2012-09-12 21:55:07 gc4xxp228mra3 45656 0 46497
  235. 2012-09-12 21:35:07 2012-09-12 21:45:07 gc4xxp228mra3 45055 0 45896
  236. 2012-09-12 21:25:07 2012-09-12 21:35:07 gc4xxp228mra3 44453 0 45295
  237. 2012-09-12 21:15:07 2012-09-12 21:25:07 gc4xxp228mra3 43853 0 44692
  238. 2012-09-12 21:05:07 2012-09-12 21:15:07 gc4xxp228mra3 43252 0 44092
  239. 2012-09-12 20:55:07 2012-09-12 21:05:07 gc4xxp228mra3 42648 0 43492
  240. 2012-09-12 20:45:07 2012-09-12 20:55:07 gc4xxp228mra3 42049 0 42890
  241. 2012-09-12 20:35:07 2012-09-12 20:45:07 gc4xxp228mra3 41448 0 42288
  242. 2012-09-12 20:25:07 2012-09-12 20:35:07 gc4xxp228mra3 40848 0 41688
  243. 2012-09-12 20:15:07 2012-09-12 20:25:07 gc4xxp228mra3 40246 0 41087
  244. 2012-09-12 20:05:07 2012-09-12 20:15:07 gc4xxp228mra3 39646 0 40485
  245. 2012-09-12 19:55:07 2012-09-12 20:05:07 gc4xxp228mra3 39042 0 39885
  246. 2012-09-12 19:45:07 2012-09-12 19:55:07 gc4xxp228mra3 38442 0 39282
  247. 2012-09-12 19:35:07 2012-09-12 19:45:07 gc4xxp228mra3 37842 0 38682
  248. 2012-09-12 19:25:07 2012-09-12 19:35:07 gc4xxp228mra3 37241 0 38082
  249. 2012-09-12 19:15:07 2012-09-12 19:25:07 gc4xxp228mra3 36639 0 37478
  250. 2012-09-12 19:05:07 2012-09-12 19:15:07 gc4xxp228mra3 36038 0 36878
  251. 2012-09-12 18:55:07 2012-09-12 19:05:07 gc4xxp228mra3 35438 0 36279
  252. 2012-09-12 18:45:07 2012-09-12 18:55:07 gc4xxp228mra3 34836 0 35677
  253. 2012-09-12 18:35:07 2012-09-12 18:45:07 gc4xxp228mra3 34234 0 35074
  254. 2012-09-12 18:25:07 2012-09-12 18:35:07 gc4xxp228mra3 33634 0 34475
  255. 2012-09-12 18:15:07 2012-09-12 18:25:07 gc4xxp228mra3 33034 0 33871
  256. 2012-09-12 18:05:07 2012-09-12 18:15:07 gc4xxp228mra3 32432 0 33273
  257. 2012-09-12 17:55:07 2012-09-12 18:05:07 gc4xxp228mra3 31832 0 32672
  258. 2012-09-12 17:45:07 2012-09-12 17:55:07 gc4xxp228mra3 31229 0 32071
  259. 2012-09-12 17:35:07 2012-09-12 17:45:07 gc4xxp228mra3 30629 0 31470
  260. 2012-09-12 17:25:07 2012-09-12 17:35:07 gc4xxp228mra3 30028 0 30869
  261. 2012-09-12 17:15:07 2012-09-12 17:25:07 gc4xxp228mra3 29424 0 30268
  262. 2012-09-12 17:05:07 2012-09-12 17:15:07 gc4xxp228mra3 28824 0 29724
  263. 2012-09-12 16:55:07 2012-09-12 17:05:07 gc4xxp228mra3 28225 0 29125
  264. 2012-09-12 16:45:07 2012-09-12 16:55:07 gc4xxp228mra3 27624 0 28523
  265. 2012-09-12 16:35:07 2012-09-12 16:45:07 gc4xxp228mra3 27020 0 27923
  266. 2012-09-12 16:25:07 2012-09-12 16:35:07 gc4xxp228mra3 26422 0 27320
  267. 2012-09-12 16:15:07 2012-09-12 16:25:07 gc4xxp228mra3 25820 0 26720
  268. 2012-09-12 16:05:07 2012-09-12 16:15:07 gc4xxp228mra3 25220 0 26120
  269. 2012-09-12 15:55:07 2012-09-12 16:05:07 gc4xxp228mra3 24618 0 25519
  270. 2012-09-12 15:45:07 2012-09-12 15:55:07 gc4xxp228mra3 24017 0 24918
  271. 2012-09-12 15:35:07 2012-09-12 15:45:07 gc4xxp228mra3 23416 0 24316
  272. 2012-09-12 15:25:07 2012-09-12 15:35:07 gc4xxp228mra3 22813 0 23713
  273. 2012-09-12 15:15:07 2012-09-12 15:25:07 gc4xxp228mra3 22214 0 23113
  274. 2012-09-12 15:05:07 2012-09-12 15:15:07 gc4xxp228mra3 21613 0 22513
  275. 2012-09-12 14:55:07 2012-09-12 15:05:07 gc4xxp228mra3 21012 0 21911
  276. 2012-09-12 14:45:07 2012-09-12 14:55:07 gc4xxp228mra3 20410 0 21310
  277. 2012-09-12 14:35:07 2012-09-12 14:45:07 gc4xxp228mra3 19810 0 20710
  278. 2012-09-12 14:25:07 2012-09-12 14:35:07 gc4xxp228mra3 19207 0 20107
  279. 2012-09-12 14:15:07 2012-09-12 14:25:07 gc4xxp228mra3 18607 0 19508
  280. 2012-09-12 14:05:07 2012-09-12 14:15:07 gc4xxp228mra3 18007 0 18907
  281. 2012-09-12 13:55:07 2012-09-12 14:05:07 gc4xxp228mra3 17406 0 18304
  282. 2012-09-12 13:45:07 2012-09-12 13:55:07 gc4xxp228mra3 16803 0 17703
  283. 2012-09-12 13:35:07 2012-09-12 13:45:07 gc4xxp228mra3 16203 0 17104
  284. 2012-09-12 13:25:07 2012-09-12 13:35:07 gc4xxp228mra3 15600 0 16503
  285. 2012-09-12 13:15:07 2012-09-12 13:25:07 gc4xxp228mra3 15001 0 15900
  286. 2012-09-12 13:05:07 2012-09-12 13:15:07 gc4xxp228mra3 14400 0 15300
  287. 2012-09-12 12:55:07 2012-09-12 13:05:07 gc4xxp228mra3 13798 0 14700
  288. 2012-09-12 12:45:07 2012-09-12 12:55:07 gc4xxp228mra3 13199 0 14099
  289. 2012-09-12 12:35:07 2012-09-12 12:45:07 gc4xxp228mra3 12597 0 13497
  290. 2012-09-12 12:25:07 2012-09-12 12:35:07 gc4xxp228mra3 11996 0 12897
  291. 2012-09-12 12:15:07 2012-09-12 12:25:07 gc4xxp228mra3 11395 0 12293
  292. 2012-09-12 12:05:07 2012-09-12 12:15:07 gc4xxp228mra3 10793 0 11695
  293. 2012-09-12 11:55:07 2012-09-12 12:05:07 gc4xxp228mra3 10190 0 11094
  294. 2012-09-12 11:45:07 2012-09-12 11:55:07 gc4xxp228mra3 9592 0 10490
  295. 2012-09-12 11:35:07 2012-09-12 11:45:07 gc4xxp228mra3 8991 0 9892
  296. 2012-09-12 11:25:07 2012-09-12 11:35:07 gc4xxp228mra3 8390 0 9290
  297. 2012-09-12 11:15:07 2012-09-12 11:25:07 gc4xxp228mra3 7789 0 8689
  298. 2012-09-12 11:05:07 2012-09-12 11:15:07 gc4xxp228mra3 7187 0 8087
  299. 2012-09-12 10:55:07 2012-09-12 11:05:07 gc4xxp228mra3 6586 0 7487
  300. 2012-09-12 10:45:07 2012-09-12 10:55:07 gc4xxp228mra3 5985 0 6886
  301. 2012-09-12 10:35:07 2012-09-12 10:45:07 gc4xxp228mra3 5385 0 6285
  302. 2012-09-12 10:25:07 2012-09-12 10:35:07 gc4xxp228mra3 4784 0 5684
  303. 2012-09-12 10:15:07 2012-09-12 10:25:07 gc4xxp228mra3 4183 0 5080
  304. 2012-09-12 10:05:07 2012-09-12 10:15:07 gc4xxp228mra3 3582 0 4480
  305. 2012-09-12 09:55:07 2012-09-12 10:05:07 gc4xxp228mra3 2981 0 3881
  306. 2012-09-12 09:45:07 2012-09-12 09:55:07 gc4xxp228mra3 2380 0 3280
  307. 2012-09-12 09:35:07 2012-09-12 09:45:07 gc4xxp228mra3 1777 0 2679
  308. 2012-09-12 09:25:07 2012-09-12 09:35:07 1r8ac931vfz9h 1660 0 2560
  309. 2012-09-12 09:15:07 2012-09-12 09:25:07 1r8ac931vfz9h 1059 0 1960
  310. 2012-09-12 09:05:07 2012-09-12 09:15:07 1r8ac931vfz9h 458 0 1359
  311. 2012-09-12 08:55:07 2012-09-12 09:05:07 0 0 900
  312. 2012-09-12 08:45:07 2012-09-12 08:55:07 0 0 900

发现从2012-09-12 09:05:07就有长时间的sql语句出现，每十分钟执行一次，一直持续到2012-09-14 05:02:07，该sql语句失败。SQLID为：gc4xxp228mra3

 

     

5.引发问题的SQL内容


点击(此处)折叠或打开

  1. select count(*) from (select T16616.F16614 as F16618, T16616.F16615 as F16619, T16616.F12065 as F16620 from (select T42.IIIII as F16614, T42.V09001 as F16615, T43.V11542 as F12065 from GRMCDB.GRMC_OCEN_ELE T42, GRMCDB.GRMC_AWST_ELE T43) T16616) T16623


6. Undo tablespace使用率100%原因

从oracle10g开始，oracle提供了Automatic UNDO Retention特性，即oracle会根据实际情况自动调整undo_retention值，从2012-09-12 09:05:07至2012-09-14 05:02:07 期间产生了多次超长时间查询，这段期间MAXQUERYLEN值保持在10万秒以上，为了尽可能避免ORA-01555的报错，尽可能保留更多的UNEXPIRED EXTENT，ORACLE会将这些查询的undo信息保留时间变长，选择在undo tablespace中扩充rollback segment，所以undo tablespace使用率会不断上升，最终导致了undo tablespace空间使用率达到100%。

注：

MAXQUERYLEN 最长查询的耗时(单位：秒)

UNDO_RETENTION undo信息在undo表空间保存的时间，默认900秒

TUNED_UNDORETENTION oracle自动调整后的undo_retention值

7. 解决思路

1）undo表空间会自动回收，循环使用，可忽略100%问题

2）对于oracle10g版本，使用如下命令强制undo保存时间

alter tablespace UNDOTBS3 retention guarantee;

3）从引发问题的SQL语句下手，应用开发人员分析该SQL语句重复执行的原因。

8. Undo tablespace空间监控方法（网络文章，参考）

undo tablespace空间使用率达到100%，只是意味着所有的空间都已经分配给rollback segment，但并不一定会影响到业务的select和insert、delete、update操作，因为rollback segment中的空间是可以重用的。在oracle10g，不能通过监控dba_free_space数据库字典的方法去获得undo tablespace的使用情况，可以考虑通过以下方法进行监控：

1. 监控v$undostat视图，主要包括MAXQUERYLEN、SSOLDERRCNT、NOSPACEERRCNT列值，如：MAXQUERYLEN大于36000秒则报警、SSOLDERRCNT大于0次（或一定次数）则报警、NOSPACEERRCNT大于0次则严重警告等；

2. 监控平均每一秒钟所需的最少回滚表空间容量，如果超过一定范围则报警

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"

FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),

(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)*86400))) AS UPS FROM v$undostat),

(select block_size as DBS from dba_tablespaces where tablespace_name=

(select value from v$parameter where name = 'undo_tablespace'));

     Bytes

----------

 413215736

 

那么一个undo表空间的理论大小为：413215736*900

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

1
0
上一篇： Checkpoint CNT和SCN
下一篇： 没有了~
请登录后发表评论 登录
全部评论
myy10146

注册时间：2011-04-12

  • 博文量
    11
  • 访问量
    58909

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593