The impact of the autoextend on next value ON the performance of the tablespace File

The impact of the value specified by autoextend on next on the performance of the tablespace corresponding to the file. Creating a tablespace is a data file that can be set to automatic expansion and how much capacity can be expanded each time, if it is found that insertion of large data volumes is very slow, the possible cause is that the value specified by NEXT is too small.
1. CREATE a TABLESPACE: www.2cto.com create tablespace "T" DATAFILE 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ T. DBF 'size 5 M is not automatically extended by default: 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. If the inserted data exceeds 5 MB, an error occurs in 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 T3, change tablespace to auto Scaling: SQL> alter database datafile 8 autoextend on; Database altered. explain 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 ---------- detail --- ------------ 8 T YES 1
By default, each extension is 1 byte, which is slow for insertion of large data volumes. 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>
In another case, you can find that the performance is improved by setting the capacity of each expansion to a large value. create tablespace "T2" DATAFILE 'd: \ ORACLE \ PRODUCT \ 10.2.0 \ ORADATA \ ORCL \ T2.DBF 'size 50 M autoextend on next 20 MSQL> 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 256014: 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> (this time there will also be multiple 20 m Extension applications)