关于系统查询、提取速度慢的问题

这样的环境：
Win2k AD+oracle8
两台x2.4g+2g ram做集群
运行一个c/s的系统配合两个磁盘阵列（raid5，320g）
磁盘由os管理数据大约是1kW条，数据量是150g左右（其中图片数据100G左右）从6月开始出现这样的问题：
进行数据操作（查询、提取数据上报、业务操作），非常慢，到现在为止，进行一次业务操作，需要差不多5分钟（原来是几十秒）而数据提取上报（上级服务器直连，提取），根本无法完成
现在系统已经到接近崩溃的境地了
经过检查，os的cpu和ram占用分别是30％，45％
还比较正常，而数据库和应用系统都没有什么问题
网络连接很正常，是40m的带宽，利用率不到10m目前的表现主要是两个：
一个是授权，因为连接数到489个后，就无法连接了
一个是磁盘阵列，磁盘阵列的读写非常频繁（比以前大很多）我们分析是因为磁盘阵列有问题造成系统的缓慢
但是数据备份所需要的时间和以前比，只增加少量（大约几分钟）而检查授权，和原来没有什么差别，就出现这个问题了
所以，百思不得其解
这个到底是什么原因？

解决方案 »

  1.   

    数据库慢.原因很多的,楼上说的就是一种,我的建议先用第三方工具,比如说toad
    测一下系统的健康状况和运行情况,看看是什么原因导致的数据库缓慢,查到原因后再去做进一步处理,省的无的放矢,费了时间还没有效果
      

  2.   

    我觉得，磁盘空间占用的比较大，而且可能是磁盘碎片比较多。我有一次在执行sql的时候，看到放oracle的盘的空间，直接被占用了2G。经过整理，就恢复了
      

  3.   

    下面是v$session_wait的内容
    请求帮忙看看    SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3 P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
    1 1 13564 pmon timer duration 300 0000012C 0 00 0 00 0 33 WAITING
    2 2 27093 rdbms ipc message timeout 300 0000012C 0 00 0 00 0 5 WAITING
    3 3 20653 rdbms ipc message timeout 272 00000110 0 00 0 00 0 0 WAITING
    4 6 28 rdbms ipc message timeout 180000 0002BF20 0 00 0 00 0 936 WAITING
    5 17 35564 rdbms ipc message timeout 30000 00007530 0 00 0 00 0 239 WAITING
    6 46 19672 rdbms ipc message timeout 30000 00007530 0 00 0 00 0 1446 WAITING
    7 7 32711 rdbms ipc message timeout 30000 00007530 0 00 0 00 0 360 WAITING
    8 4 14113 rdbms ipc message timeout 300 0000012C 0 00 0 00 0 0 WAITING
    9 5 2347 smon timer sleep time 300 0000012C failed 0 00 0 00 0 236 WAITING
    10 8 148 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 3202 WAITING
    11 27 502 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 798 WAITING
    12 26 1141 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 722 WAITING
    13 25 397 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 728 WAITING
    14 24 5292 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1980 WAITING
    15 23 16367 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1372 WAITING
    16 22 2475 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2272 WAITING
    17 21 690 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2275 WAITING
    18 20 106 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2573 WAITING
    19 18 1769 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 96 WAITING
    20 44 502 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2277 WAITING
    21 43 1954 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 779 WAITING
    22 42 422 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 3243 WAITING
    23 41 49627 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5200 WAITING
    24 40 470 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2322 WAITING
    25 39 581 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1305 WAITING
      

  4.   

    26 38 499 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 96 WAITING
    27 37 36 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 -2 0 WAITED UNKNOWN TIME
    28 36 2777 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1354 WAITING
    29 68 157 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2272 WAITING
    30 67 177 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2280 WAITING
    31 66 635 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1308 WAITING
    32 65 1154 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1390 WAITING
    33 64 7707 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 3 WAITING
    34 63 4551 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2272 WAITING
    35 60 186 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1967 WAITING
    36 59 433 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 10 WAITING
    37 58 135 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1980 WAITING
    38 93 584 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5058 WAITING
    39 91 191 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2270 WAITING
    40 90 781 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 473 WAITING
    41 89 13999 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 0 7124 WAITING
    42 88 49523 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 27 WAITING
    43 84 1118 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 735 WAITING
    44 82 157 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 80 WAITING
    45 81 34999 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 0 15 WAITING
    46 79 102 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2013 WAITING
    47 120 1824 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 721 WAITING
    48 119 529 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5072 WAITING
    49 118 16525 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 8 WAITING
    50 117 24176 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 3279 WAITING
    51 112 269 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 27 WAITING
    52 111 1251 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5138 WAITING
    53 110 1157 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1374 WAITING
    54 108 1070 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5135 WAITING
    55 107 10957 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 93 WAITING
    56 145 214 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5199 WAITING
    57 144 1003 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5200 WAITING
    58 143 1224 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5072 WAITING
    59 142 307 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5200 WAITING
    60 141 179 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5058 WAITING
    61 140 205 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5123 WAITING
    62 139 651 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1995 WAITING
    63 138 281 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5125 WAITING
    64 137 940 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 3230 WAITING
    65 162 866 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2272 WAITING
    66 161 225 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 88 WAITING
    67 160 214 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1306 WAITING
    68 159 151 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4623 WAITING
    69 158 38 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5067 WAITING
    70 157 113 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5064 WAITING
    71 156 86 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5144 WAITING
    72 155 142 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5125 WAITING
    73 154 734 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5117 WAITING
    74 196 150 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 24 WAITING
    75 194 130 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 27 WAITING
    76 193 430 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 0 WAITING
    77 192 1702 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 0 WAITING
    78 191 95 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 728 WAITING
    79 190 324 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 739 WAITING
    80 189 217 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1073 WAITING
    81 188 774 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 49 WAITING
    82 187 1704 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 0 WAITING
    83 186 1136 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 718 WAITING
    84 185 9479 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 717 WAITING
    85 184 209 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1352 WAITING
    86 183 555 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 713 WAITING
    87 182 123 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 102 WAITING
    88 181 920 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2302 WAITING
    89 180 4475 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2347 WAITING
    90 179 879 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 30 WAITING
    91 178 993 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2277 WAITING
    92 177 2557 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 59 WAITING
    93 176 53 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4620 WAITING
    94 175 438 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4592 WAITING
    95 174 799 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4592 WAITING
    96 173 464 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4626 WAITING
    97 172 2053 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1354 WAITING
    98 171 3753 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2272 WAITING
    99 170 311 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1309 WAITING
      

  5.   

    100 169 272 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4515 WAITING
    101 168 535 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4597 WAITING
    102 167 262 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 80 WAITING
    103 166 8401 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1967 WAITING
    104 165 6863 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2312 WAITING
    105 164 1119 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4593 WAITING
    106 163 365 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2352 WAITING
    107 153 149 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4620 WAITING
    108 152 671 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5149 WAITING
    109 151 1408 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 0 WAITING
    110 150 2288 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5117 WAITING
    111 149 489 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5125 WAITING
    112 148 136 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5058 WAITING
    113 147 176 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5171 WAITING
    114 146 1103 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5117 WAITING
    115 136 303 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5156 WAITING
    116 135 933 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5132 WAITING
    117 134 192 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5136 WAITING
    118 131 1486 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 99 WAITING
    119 130 364 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5137 WAITING
    120 127 332 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5125 WAITING
    121 122 327 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 0 0 WAITING
    122 121 1551 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5125 WAITING
    123 106 286 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 10 WAITING
    124 105 7280 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 70 WAITING
    125 102 842 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 15 WAITING
    126 101 1164 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 0 WAITING
    127 99 1764 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 795 WAITING
    128 98 961 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 74 WAITING
    129 96 850 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 10 WAITING
    130 95 41085 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5114 WAITING
    131 78 1116 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 5138 WAITING
    132 75 147 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1313 WAITING
    133 74 339 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 737 WAITING
    134 73 88 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1972 WAITING
    135 72 147 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1958 WAITING
    136 71 17378 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 45 WAITING
    137 70 155 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 24 WAITING
    138 69 715 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 27 WAITING
    139 57 275 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 713 WAITING
    140 56 1530 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1963 WAITING
    141 55 256 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2339 WAITING
    142 53 264 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1327 WAITING
    143 52 791 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1305 WAITING
    144 51 1068 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2302 WAITING
    145 49 189 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2275 WAITING
    146 47 206 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 2577 WAITING
    147 35 135 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1079 WAITING
    148 34 22551 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 8 WAITING
    149 33 193 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1331 WAITING
    150 32 46 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 12 WAITING
    151 31 426 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 0 WAITING
    152 30 469 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 10 WAITING
    153 29 4150 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1364 WAITING
    154 28 177 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1984 WAITING
    155 9 235 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4503 WAITING
    156 11 780 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1309 WAITING
    157 12 2010 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 96 WAITING
    158 13 713 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1986 WAITING
    159 14 42067 SQL*Net message from client driver id 1413697536 54435000 #bytes 1 00000001 0 00 0 8123 WAITING
    160 15 40 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 1963 WAITING
    161 16 85 SQL*Net message from client driver id 675562835 28444553 #bytes 1 00000001 0 00 0 4653 WAITING