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

shawls 2007-10-23 07:45:58
这样的环境:
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个后,就无法连接了
一个是磁盘阵列,磁盘阵列的读写非常频繁(比以前大很多)

我们分析是因为磁盘阵列有问题造成系统的缓慢
但是数据备份所需要的时间和以前比,只增加少量(大约几分钟)

而检查授权,和原来没有什么差别,就出现这个问题了
所以,百思不得其解




这个到底是什么原因?
...全文
175 点赞 收藏 12
写回复
12 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
shawls 2007-10-27

问题解决了
是有个和c/s运行有关,但是无需备份的中间表,因为磁盘碎片比较多,造成读写慢,从而造成办理业务慢,而备份无需备份此数据,故没有问题
回复
shawls 2007-10-26

没有人关注这个问题了?
回复
shawls 2007-10-25



查询是不包括图片的
图片数据是存放再另外一个表中

如果是图片的问题
我估计可能不大
应为原来也有这么多图片

数据每个月增加的不多,也就是几十~几百m的样子


磁盘已经进行了一次整理,似乎没有什么改观
回复
hongqi162 2007-10-25
如果查询包括100G图片的那个表的话,速度慢也是有可能的
回复
shawls 2007-10-25
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
回复
shawls 2007-10-25
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
回复
shawls 2007-10-25
下面是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
回复
bjt_ 2007-10-24
查看v$session_wait表
回复
haiye 2007-10-24
我觉得,磁盘空间占用的比较大,而且可能是磁盘碎片比较多。我有一次在执行sql的时候,看到放oracle的盘的空间,直接被占用了2G。经过整理,就恢复了
回复
kelsoncong 2007-10-24
数据库慢.原因很多的,楼上说的就是一种,我的建议先用第三方工具,比如说toad
测一下系统的健康状况和运行情况,看看是什么原因导致的数据库缓慢,查到原因后再去做进一步处理,省的无的放矢,费了时间还没有效果
回复
shawls 2007-10-24

由于我们的网络与互联网没有连接,so,需要回家上网才行



今天我查看了一下数据库磁盘,发现没有什么碎片
但是系统(操作系统)盘上面碎片非常多,
是否就是造成这个的原因?


回复
蒋晟 2007-10-23
整理一下数据库碎片看看
回复
相关推荐
发帖
Oracle 高级技术
创建于2007-09-28

3429

社区成员

Oracle 高级技术相关讨论专区
申请成为版主
帖子事件
创建了帖子
2007-10-23 07:45
社区公告
暂无公告