RE: AUTOEXTEND

Never observed it myself either - until now. Notice below, the insert usually takes an average .56 seconds, but when the datafile has to extend by 128MB, the same insert takes 2.84 seconds. Sure, it's only a 2 second difference, but the real life impact of this will depend on 1) How patient are your users, 2) How fast is your OS/disk, 3) How often are you autoextending, 4) How large are your autoextent sizes.

Regards,
Brandon

CREATE TABLESPACE TEST (created in OEM)

    DATAFILE '/u07/oradat/vrtxtst/TEST.dbf' SIZE 1M REUSE     AUTOEXTEND ON NEXT 128M MAXSIZE 1024M     EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO SQL>create table t tablespace test as select * from all_objects where 1=2;

Table created.

Elapsed: 00:00:00.10

SQL>select bytes, initial_extent, extents, next_extent from user_segments where segment_name = 'T';

     BYTES INITIAL_EXTENT EXTENTS NEXT_EXTENT
---------- -------------- ---------- -----------

     65536 65536 1

Elapsed: 00:00:00.06

SQL>insert into t select * from all_objects where rownum < 5001;

2953 rows created.

Elapsed: 00:00:00.61

SQL>select bytes, initial_extent, extents, next_extent from user_segments where segment_name = 'T';

     BYTES INITIAL_EXTENT EXTENTS NEXT_EXTENT
---------- -------------- ---------- -----------

    393216 65536 6

Elapsed: 00:00:00.05

SQL>insert into t select * from all_objects where rownum < 5001;

2953 rows created.

Elapsed: 00:00:00.57

SQL>select bytes, initial_extent, extents, next_extent from user_segments where segment_name = 'T';

     BYTES INITIAL_EXTENT EXTENTS NEXT_EXTENT
---------- -------------- ---------- -----------

    655360 65536 10

Elapsed: 00:00:00.05

SQL>select file_name, bytes from dba_data_files where file_id=5;

FILE_NAME                                               BYTES


-------------------------------------------------- ----------

/u07/oradat/vrtxtst/TEST.dbf                          1048576

Elapsed: 00:00:00.03

SQL>insert into t select * from all_objects where rownum < 5001;

2953 rows created.

Elapsed: 00:00:00.50

SQL>select bytes, initial_extent, extents, next_extent from user_segments where segment_name = 'T';

     BYTES INITIAL_EXTENT EXTENTS NEXT_EXTENT
---------- -------------- ---------- -----------

    983040 65536 15

Elapsed: 00:00:00.05

SQL>select file_name, bytes from dba_data_files where file_id=5;

FILE_NAME                                               BYTES


-------------------------------------------------- ----------

/u07/oradat/vrtxtst/TEST.dbf                          1048576

Elapsed: 00:00:00.01

SQL>insert into t select * from all_objects where rownum < 5001;

2953 rows created.

Elapsed: 00:00:02.84

SQL>select bytes, initial_extent, extents, next_extent from user_segments where segment_name = 'T';

     BYTES INITIAL_EXTENT EXTENTS NEXT_EXTENT
---------- -------------- ---------- -----------

   2097152 65536 17

Elapsed: 00:00:00.04

SQL>select file_name, bytes from dba_data_files where file_id=5;

FILE_NAME                                               BYTES


-------------------------------------------------- ----------

/u07/oradat/vrtxtst/TEST.dbf                        135266304

Elapsed: 00:00:00.00

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Dennis Williams Sent: Tuesday, October 04, 2005 2:40 PM
To: DGoulet_at_vicr.com
Cc: mark.powell_at_eds.com; oracle-l_at_freelists.org Subject: Re: AUTOEXTEND

I have to agree with Dick.

. . .

Brandon - I've heard of the big time delay to allocate another extent, but I have yet to observe it, or have a user complain.

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

--
http://www.freelists.org/webpage/oracle-l