表空间对应文件的AUTOEXTEND ON NEXT指定的值对性能的影响
创建表空间是指定的数据文件可以设为自动扩展,以及每次扩展多少容量,如果发现在大数据量插入的时候非常慢,可能的原因是NEXT指定的值太小.
下面来模拟一下这个过程:
1,创建一个表空间:
CREATE TABLESPACE "T" DATAFILE 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\T.DBF' SIZE 5M
默认的话是不自动扩展的:
SQL> select file_id, tablespace_name,autoextensible, increment_by
  2  from dba_data_files where tablespace_name='T';
   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.
SQL> select file_id, tablespace_name,autoextensible, increment_by
  2  from dba_data_files where tablespace_name='T';

   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 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\T2.DBF'
SIZE 50M  autoextend on next 20M
SQL> select file_id, tablespace_name,autoextensible, increment_by
  2  from dba_data_files where tablespace_name='T2';
   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.
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