Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › segment分配extent时，怎么在多个datafile里选择的？ ...

bibosea

18 积分	0 好友	0 主题

发消息

segment分配extent时，怎么在多个datafile里选择的？

1^#

发表于 2012-2-9 09:04:44 | 查看: 6768| 回复: 4

一个表空间里有多个datafile，分配extent时，策略是什么？随机还是？

分享0

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-2-9 18:18:49

ODM test:

test1:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> create tablespace MACLEAN datafile size 100M;
Tablespace created.
SQL> alter tablespace maclean add datafile size 100M ;
Tablespace altered.
SQL> alter tablespace maclean add datafile size 100M ;
Tablespace altered.
SQL> alter tablespace maclean add datafile size 100M ;
Tablespace altered.
SQL> alter tablespace maclean add datafile size 100M ;
Tablespace altered.
SQL> alter tablespace maclean add datafile size 100M ;
Tablespace altered.
SQL> alter tablespace maclean add datafile size 100M ;
Tablespace altered.
SQL> select file_id from dba_data_Files where tablespace_name='MACLEAN';
FILE_ID
----------
20
21
22
23
24
25
26
7 rows selected.
SQL> create table maclean (t1 int) tablespace maclean;
Table created.
SQL> alter table maclean allocate extent (size 20M);
Table altered.
SQL> select extent_id ,file_id,blocks,bytes/1024/1024 from dba_extents where segment_name='MACLEAN';
EXTENT_ID FILE_ID BLOCKS BYTES/1024/1024
---------- ---------- ---------- ---------------
0 20 8 .0625
1 21 128 1
2 22 128 1
3 23 128 1
4 24 128 1
5 25 128 1
6 26 128 1
7 20 128 1
8 21 128 1
9 22 128 1
10 23 128 1
11 24 128 1
12 25 128 1
13 26 128 1
14 20 128 1
15 21 128 1
16 22 128 1
17 23 128 1
18 24 128 1
19 25 128 1
20 26 128 1
21 rows selected.

复制代码

结论1：alter table allocate extent 是均匀分配在多个数据文件上，且无视extent size

回复只看该作者道具举报

Maclean Liu(刘相兵

3^#

发表于 2012-2-9 18:26:50

ODM TEST2:

SQL> drop table maclean;
Table dropped.
SQL> purge recyclebin;
Recyclebin purged.
SQL>
SQL> create table maclean tablespace maclean as select * from dba_objects where 0=1;
Table created.
SQL> alter table maclean nologging;
Table altered.
SQL> insert /*+ append */ into maclean select * from dba_objects;
50736 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
50736 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
101472 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
202944 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
405888 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
811776 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
1623552 rows created.
SQL> commit;
Commit complete.
SQL> select extent_id ,file_id,blocks,bytes/1024/1024 from dba_extents where segment_name='MACLEAN';
EXTENT_ID FILE_ID BLOCKS BYTES/1024/1024
---------- ---------- ---------- ---------------
0 21 8 .0625
1 21 8 .0625
2 21 8 .0625
3 21 8 .0625
4 21 8 .0625
5 21 8 .0625
6 21 8 .0625
7 21 8 .0625
8 21 8 .0625
9 21 8 .0625
10 21 8 .0625
11 21 8 .0625
12 21 8 .0625
13 21 8 .0625
14 21 8 .0625
15 21 8 .0625
16 22 128 1
17 23 128 1
18 24 128 1
19 25 128 1
20 26 128 1
21 20 128 1
22 21 128 1
23 22 128 1
24 23 128 1
25 24 128 1
26 25 128 1
27 26 128 1
28 20 128 1
29 21 128 1
30 22 128 1
31 23 128 1
32 24 128 1
33 25 128 1
34 26 128 1
35 20 128 1
36 21 128 1
37 22 128 1
38 23 128 1
39 24 128 1
40 25 128 1
41 26 128 1
42 20 128 1
43 21 128 1
44 22 128 1
45 23 128 1
46 24 128 1
47 25 128 1
48 26 128 1
49 20 128 1
50 21 128 1
51 22 128 1
52 23 128 1
53 24 128 1
54 25 128 1
55 26 128 1
56 20 128 1
57 21 128 1
58 22 128 1
59 23 128 1
60 24 128 1
61 25 128 1
62 26 128 1
63 20 128 1
64 21 128 1
65 22 128 1
66 23 128 1
67 24 128 1
68 25 128 1
69 26 128 1
70 20 128 1
71 21 128 1
72 22 128 1
73 23 128 1
74 24 128 1
75 25 128 1
76 26 128 1
77 20 128 1
78 21 128 1
79 22 1024 8
80 23 1024 8
81 24 1024 8
82 25 1024 8
83 26 1024 8
84 20 1024 8
85 21 1024 8
86 22 1024 8
87 23 1024 8
88 24 1024 8
89 25 1024 8
90 26 1024 8
91 20 1024 8
92 21 1024 8
93 22 1024 8
94 23 1024 8
95 24 1024 8
96 25 1024 8
97 26 1024 8
98 20 1024 8
99 21 1024 8
100 22 1024 8
101 23 1024 8
102 24 1024 8
103 25 1024 8
104 26 1024 8
105 20 1024 8
106 21 1024 8
107 22 1024 8
108 23 1024 8
109 24 1024 8
110 25 1024 8
111 26 1024 8
112 20 1024 8
113 21 1024 8
114 22 1024 8
115 rows selected.
SQL> select file_id,block_id,blocks from dba_extents where segment_name='MACLEAN' and blocks=8;
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
21 9 8
21 17 8
21 25 8
21 33 8
21 41 8
21 49 8
21 57 8
21 65 8
21 73 8
21 81 8
21 89 8
21 97 8
21 105 8
21 113 8
21 121 8
21 129 8
16 rows selected.

复制代码

结论2 针对INSERT操作，在local management extent size autoallocate下当segment较小(8k标准块下小于1MB)时可能会在起始数据文件中连续分配16个 8个数据块的extent ，之后会在多个数据文件上均匀分配大小为128=> 1024=>..个 blocks的 extent。

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-2-9 18:36:28

ODM test3:

SQL> drop tablespace maclean including contents and datafiles;
Tablespace dropped.
SQL> create tablespace maclean datafile size 100M extent management local uniform size 1M;
Tablespace created.
SQL> alter tablespace maclean add datafile size 100M;
Tablespace altered.
SQL> /
Tablespace altered.
SQL> /
Tablespace altered.
SQL> /
Tablespace altered.
SQL> /
Tablespace altered.
SQL> /
Tablespace altered.
SQL> /
Tablespace altered.
SQL> create table maclean tablespace maclean as select * from dba_objects where 0=1;
Table created.
SQL> alter table maclean nologging;
Table altered.
SQL> insert /*+ append */ into maclean select * from dba_objects;
50736 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
50736 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
101472 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
202944 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
405888 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
811776 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ append */ into maclean select * from maclean;
1623552 rows created.
SQL> commit;
Commit complete.
SQL> select extent_id ,file_id,blocks,bytes/1024/1024 from dba_extents where segment_name='MACLEAN';
EXTENT_ID FILE_ID BLOCKS BYTES/1024/1024
---------- ---------- ---------- ---------------
0 27 128 1
1 20 128 1
2 21 128 1
3 22 128 1
4 23 128 1
5 24 128 1
6 25 128 1
7 26 128 1
8 27 128 1
9 20 128 1
10 21 128 1
11 22 128 1
12 23 128 1
13 24 128 1
14 25 128 1
15 26 128 1
16 27 128 1
17 20 128 1
18 21 128 1
19 22 128 1
20 23 128 1
21 24 128 1
22 25 128 1
23 26 128 1
24 27 128 1
25 20 128 1
26 21 128 1
27 22 128 1
28 23 128 1
29 24 128 1
30 25 128 1
31 26 128 1
32 27 128 1
33 20 128 1
34 21 128 1
35 22 128 1
36 23 128 1
37 24 128 1
38 25 128 1
39 26 128 1
40 27 128 1
41 20 128 1
42 21 128 1
43 22 128 1
44 23 128 1
45 24 128 1
46 25 128 1
47 26 128 1
48 27 128 1
49 20 128 1
50 21 128 1
51 22 128 1
52 23 128 1
53 24 128 1
54 25 128 1
55 26 128 1
56 27 128 1
57 20 128 1
58 21 128 1
59 22 128 1
60 23 128 1
61 24 128 1
62 25 128 1
63 26 128 1
64 27 128 1
65 20 128 1
66 21 128 1
67 22 128 1
68 23 128 1
69 24 128 1
70 25 128 1
71 26 128 1
72 27 128 1
73 20 128 1
74 21 128 1
75 22 128 1
76 23 128 1
77 24 128 1
78 25 128 1
79 26 128 1
80 27 128 1
81 20 128 1
82 21 128 1
83 22 128 1
84 23 128 1
85 24 128 1
86 25 128 1
87 26 128 1
88 27 128 1
89 20 128 1
90 21 128 1
91 22 128 1
92 23 128 1
93 24 128 1
94 25 128 1
95 26 128 1
96 27 128 1
97 20 128 1
98 21 128 1
99 22 128 1
100 23 128 1
101 24 128 1
102 25 128 1
103 26 128 1
104 27 128 1
105 20 128 1
106 21 128 1
107 22 128 1
108 23 128 1
109 24 128 1
110 25 128 1
111 26 128 1
112 27 128 1
113 20 128 1
114 21 128 1
115 22 128 1
116 23 128 1
117 24 128 1
118 25 128 1
119 26 128 1
120 27 128 1
121 20 128 1
122 21 128 1
123 22 128 1
124 23 128 1
125 24 128 1
126 25 128 1
127 26 128 1
128 27 128 1
129 20 128 1
130 21 128 1
131 22 128 1
132 23 128 1
133 24 128 1
134 25 128 1
135 26 128 1
136 27 128 1
137 20 128 1
138 21 128 1
139 22 128 1
140 23 128 1
141 24 128 1
142 25 128 1
143 26 128 1
144 27 128 1
145 20 128 1
146 21 128 1
147 22 128 1
148 23 128 1
149 24 128 1
150 25 128 1
151 26 128 1
152 27 128 1
153 20 128 1
154 21 128 1
155 22 128 1
156 23 128 1
157 24 128 1
158 25 128 1
159 26 128 1
160 27 128 1
161 20 128 1
162 21 128 1
163 22 128 1
164 23 128 1
165 24 128 1
166 25 128 1
167 26 128 1
168 27 128 1
169 20 128 1
170 21 128 1
171 22 128 1
172 23 128 1
173 24 128 1
174 25 128 1
175 26 128 1
176 27 128 1
177 20 128 1
178 21 128 1
179 22 128 1
180 23 128 1
181 24 128 1
182 25 128 1
183 26 128 1
184 27 128 1
185 20 128 1
186 21 128 1
187 22 128 1
188 23 128 1
189 24 128 1
190 25 128 1
191 26 128 1
192 27 128 1
193 20 128 1
194 21 128 1
195 22 128 1
196 23 128 1
197 24 128 1
198 25 128 1
199 26 128 1
200 27 128 1
201 20 128 1
202 21 128 1
203 22 128 1
204 23 128 1
205 24 128 1
206 25 128 1
207 26 128 1
208 27 128 1
209 20 128 1
210 21 128 1
211 22 128 1
212 23 128 1
213 24 128 1
214 25 128 1
215 26 128 1
216 27 128 1
217 20 128 1
218 21 128 1
219 22 128 1
220 23 128 1
221 24 128 1
222 25 128 1
223 26 128 1
224 27 128 1
225 20 128 1
226 21 128 1
227 22 128 1
228 23 128 1
229 24 128 1
230 25 128 1
231 26 128 1
232 27 128 1
233 20 128 1
234 21 128 1
235 22 128 1
236 23 128 1
237 24 128 1
238 25 128 1
239 26 128 1
240 27 128 1
241 20 128 1
242 21 128 1
243 22 128 1
244 23 128 1
245 24 128 1
246 25 128 1
247 26 128 1
248 27 128 1
249 20 128 1
250 21 128 1
251 22 128 1
252 23 128 1
253 24 128 1
254 25 128 1
255 26 128 1
256 27 128 1
257 20 128 1
258 21 128 1
259 22 128 1
260 23 128 1
261 24 128 1
262 25 128 1
263 26 128 1
264 27 128 1
265 20 128 1
266 21 128 1
267 22 128 1
268 23 128 1
269 24 128 1
270 25 128 1
271 26 128 1
272 27 128 1
273 20 128 1
274 21 128 1
275 22 128 1
276 23 128 1
277 24 128 1
278 25 128 1
279 26 128 1
280 27 128 1
281 20 128 1
282 21 128 1
283 22 128 1
284 23 128 1
285 24 128 1
286 25 128 1
287 26 128 1
288 27 128 1
289 20 128 1
290 21 128 1
291 22 128 1
292 23 128 1
293 24 128 1
294 25 128 1
295 26 128 1
296 27 128 1
297 20 128 1
298 21 128 1
299 22 128 1
300 23 128 1
301 24 128 1
302 25 128 1
303 26 128 1
304 27 128 1
305 20 128 1
306 21 128 1
307 22 128 1
308 23 128 1
309 24 128 1
310 25 128 1
311 26 128 1
312 27 128 1
313 20 128 1
314 21 128 1
315 22 128 1
316 23 128 1
317 24 128 1
318 25 128 1
319 26 128 1
320 27 128 1
321 20 128 1
322 21 128 1
323 22 128 1
324 23 128 1
325 24 128 1
326 25 128 1
327 26 128 1
328 27 128 1
329 20 128 1
330 21 128 1
331 22 128 1
332 23 128 1
333 24 128 1
334 25 128 1
335 26 128 1
336 27 128 1
337 20 128 1
338 21 128 1
339 22 128 1
340 23 128 1
341 24 128 1
342 25 128 1
343 26 128 1
344 27 128 1
345 20 128 1
346 21 128 1
347 22 128 1
348 23 128 1
349 24 128 1
350 25 128 1
351 rows selected.

复制代码

结论3 针对INSERT操作，在local management extent uniform size 1M 情况下 EXTENT大小统一为1M时，会在多个数据文件之间均匀分配

回复只看该作者道具举报

bibosea

5^#

发表于 2012-2-10 14:23:14

辛苦楼主了！
谢谢！

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			注册