预先分配extent减少插入时间
======================
--在aa表空间中创建空表
an@DB10> create table a_ob1 tablespace aa as select * from an_objects where 1=2;
表已创建。
--清除内存,减少干扰因素
系统已更改。
已用时间: 00: 00: 04.50
an@DB10> alter system flush shared_pool;
an@DB10> alter system flush shared_pool;
系统已更改。
已用时间: 00: 00: 02.43
--插入数据,用时46.71秒
an@DB10> insert into a_ob1 select * from an_objects;
an@DB10> insert into a_ob1 select * from an_objects;
已创建800736行。
已用时间: 00: 00: 46.71
an@DB10> commit;
an@DB10> commit;
提交完成。
已用时间: 00: 00: 00.01
--确认表空间使用情况
an@DB10> SET WRAP OFF
an@DB10> SET LINESIZE 158
an@DB10> COL ts_name FOR A30
an@DB10> select * from(
2 SELECT
3 dtp.tablespace_name ts_name,
4 NVL(ts.bytes,0)/1024/1024 size_m,
5 NVL(ts.bytes - NVL(f.bytes,0),0)/1024/1024 used_m,
6 ROUND((NVL(ts.bytes - NVL(f.bytes,0),0) /NVL(ts.bytes,0)*100),2) used
7 FROM
8 sys.DBA_TABLESPACES dtp,
9 (SELECT tablespace_name,SUM(bytes) bytes FROM DBA_DATA_FILES GROUP BY tablespace_name) ts,
10 (SELECT tablespace_name,SUM(bytes) bytes FROM DBA_FREE_SPACE GROUP BY tablespace_name) f
11 WHERE dtp.tablespace_name = ts.tablespace_name(+) AND
12 dtp.tablespace_name = f.tablespace_name(+)
13 AND NOT (dtp.CONTENTS LIKE 'TEMPORARY')
14 UNION ALL
15 SELECT
16 dtp.tablespace_name ts_name,
17 NVL(a.bytes,0)/1024/1024 size_m,
18 NVL(t.bytes,0)/1024/1024 used_m,
19 ROUND(NVL(t.bytes,0)/NVL(a.bytes,0)*100,2) used
20 FROM
21 sys.DBA_TABLESPACES dtp,
22 (SELECT tablespace_name,SUM(bytes) bytes FROM DBA_TEMP_FILES GROUP BY tablespace_name) a,
23 (SELECT ss.tablespace_name ,SUM((ss.used_blocks*ts.blocksize)) bytes FROM gv$sort_segment ss,sys.ts$ ts WHERE
blespace_name) t
24 WHERE dtp.tablespace_name = a.tablespace_name (+) AND
25 dtp.tablespace_name = t.tablespace_name (+) AND
26 dtp.CONTENTS LIKE 'TEMPORARY') pp
27 order by pp.used desc
28 ;
an@DB10> SET WRAP OFF
an@DB10> SET LINESIZE 158
an@DB10> COL ts_name FOR A30
an@DB10> select * from(
2 SELECT
3 dtp.tablespace_name ts_name,
4 NVL(ts.bytes,0)/1024/1024 size_m,
5 NVL(ts.bytes - NVL(f.bytes,0),0)/1024/1024 used_m,
6 ROUND((NVL(ts.bytes - NVL(f.bytes,0),0) /NVL(ts.bytes,0)*100),2) used
7 FROM
8 sys.DBA_TABLESPACES dtp,
9 (SELECT tablespace_name,SUM(bytes) bytes FROM DBA_DATA_FILES GROUP BY tablespace_name) ts,
10 (SELECT tablespace_name,SUM(bytes) bytes FROM DBA_FREE_SPACE GROUP BY tablespace_name) f
11 WHERE dtp.tablespace_name = ts.tablespace_name(+) AND
12 dtp.tablespace_name = f.tablespace_name(+)
13 AND NOT (dtp.CONTENTS LIKE 'TEMPORARY')
14 UNION ALL
15 SELECT
16 dtp.tablespace_name ts_name,
17 NVL(a.bytes,0)/1024/1024 size_m,
18 NVL(t.bytes,0)/1024/1024 used_m,
19 ROUND(NVL(t.bytes,0)/NVL(a.bytes,0)*100,2) used
20 FROM
21 sys.DBA_TABLESPACES dtp,
22 (SELECT tablespace_name,SUM(bytes) bytes FROM DBA_TEMP_FILES GROUP BY tablespace_name) a,
23 (SELECT ss.tablespace_name ,SUM((ss.used_blocks*ts.blocksize)) bytes FROM gv$sort_segment ss,sys.ts$ ts WHERE
blespace_name) t
24 WHERE dtp.tablespace_name = a.tablespace_name (+) AND
25 dtp.tablespace_name = t.tablespace_name (+) AND
26 dtp.CONTENTS LIKE 'TEMPORARY') pp
27 order by pp.used desc
28 ;
TS_NAME SIZE_M USED_M USED
------------------------------ ---------- ---------- ----------
SYSAUX 250 250 100
AA 176.0625 176.0625 100
SYSTEM 480 476.0625 99.18
USERS 437.5 414.5 94.74
UNDOTBS1 200 174 87
A_5 10 .0625 .63
TEMP 20 0 0
------------------------------ ---------- ---------- ----------
SYSAUX 250 250 100
AA 176.0625 176.0625 100
SYSTEM 480 476.0625 99.18
USERS 437.5 414.5 94.74
UNDOTBS1 200 174 87
A_5 10 .0625 .63
TEMP 20 0 0
已选择7行。
--创建第二个测试表
已用时间: 00: 00: 00.21
an@DB10> create table a_ob2 tablespace aa as select * from an_objects where 1=2;
已用时间: 00: 00: 00.21
an@DB10> create table a_ob2 tablespace aa as select * from an_objects where 1=2;
表已创建。
--清除内存中数据,减少干扰因素
已用时间: 00: 00: 00.82
an@DB10> alter system flush buffer_cache;
an@DB10> alter system flush buffer_cache;
系统已更改。
已用时间: 00: 00: 06.67
an@DB10> alter system flush shared_pool;
an@DB10> alter system flush shared_pool;
系统已更改。
表已更改。
已创建800736行。
已用时间: 00: 00: 15.71
通过预分配空间减少了数据插入的时间消耗
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13177610/viewspace-705716/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13177610/viewspace-705716/