作者：jixiaojia | 来源：互联网 | 2014-07-11 17:33
表空间对应文件的AUTOEXTENDONNEXT指定的值对性能的影响创建表空间是指定的数据文件可以设为自动扩展,以及每次扩展多少容量,如果发现在大数据量插入的时候非常慢,可能的原因是NEXT指定的值太小.下面来模拟一...SyntaxHighlighter.all();
表空间对应文件的AUTOEXTEND ON NEXT指定的值对性能的影响
创建表空间是指定的数据文件可以设为自动扩展,以及每次扩展多少容量,如果发现在大数据量插入的时候非常慢,可能的原因是NEXT指定的值太小.
下面来模拟一下这个过程:
1,创建一个表空间: www.2cto.com
CREATE TABLESPACE "T" DATAFILE &＃39;D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\T.DBF&＃39; SIZE 5M
默认的话是不自动扩展的:
SQL> select file_id, tablespace_name,autoextensible, increment_by
2 from dba_data_files where tablespace_name=&＃39;T&＃39;;
FILE_ID TABLESPACE_NAME AUT INCREMENT_BY
---------- ------------------------------ --- ------------
8 T NO 0
2,如果插入的数据超过5M,会出错
SQL> create table largeinsert tablespace t as select * from dba_objects;
create table largeinsert tablespace t as select * from dba_objects *
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace T
3,将表空间修改为自动扩展:
SQL> alter database datafile 8 autoextend on;
Database altered. www.2cto.com
SQL> select file_id, tablespace_name,autoextensible, increment_by
2 from dba_data_files where tablespace_name=&＃39;T&＃39;;
FILE_ID TABLESPACE_NAME AUT INCREMENT_BY
---------- ------------------------------ --- ------------
8 T YES 1
默认每次扩展是1个byte,这对于大数据量插入是很慢的.
14:41:00 SQL> create table largeinsert tablespace t as select * from dba_objects;
Table created.
14:41:04 SQL>
14:41:18 SQL> insert into largeinsert select * from largeinsert;
51017 rows created.
14:41:26 SQL> insert into largeinsert select * from largeinsert;
102034 rows created.
14:41:34 SQL> insert into largeinsert select * from largeinsert;
204068 rows created.
14:41:59 SQL>
14:42:13 SQL> insert into largeinsert select * from largeinsert;
408136 rows created.
14:42:44 SQL>
在另一种情况下,可以发现将每次扩展的容量设置为较大的值,性能会有提升.
CREATE TABLESPACE "T2" DATAFILE &＃39;D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\T2.DBF&＃39;
SIZE 50M autoextend on next 20M
SQL> select file_id, tablespace_name,autoextensible, increment_by
2 from dba_data_files where tablespace_name=&＃39;T2&＃39;;
FILE_ID TABLESPACE_NAME AUT INCREMENT_BY
---------- ------------------------------ --- ------------
9 T2 YES 2560
14:45:07 SQL> create table largeinsert2 tablespace t2 as select * from dba_objects;
Table created. www.2cto.com
14:45:11 SQL>
14:45:14 SQL>
14:45:26 SQL> insert into largeinsert2 select * from largeinsert2;
51018 rows created.
14:45:28 SQL> insert into largeinsert2 select * from largeinsert2;
102036 rows created.
14:45:35 SQL> insert into largeinsert2 select * from largeinsert2;
204072 rows created.
14:45:40 SQL> insert into largeinsert2 select * from largeinsert2;
408144 rows created.
14:46:08 SQL>(这一次也会有多次的20M的扩展申请)
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有 
