You Asked

Hello,

I was recently wondering exactly what the limits of the "next" clause are in a create table space statement. If I create a tablespace with the "Next" clause value of 50mb and maxsize unlimited (with autoextend on), does that mean that the maximum chunk that the database will grab to grow the tablespace is 50mb?

I was mainly curious on the potential performance impact this small "next" extent could have on a bigfile datafile that is growing quickly, and if Oracle would in anyway see how the datafile is growing and grab bigger extents.

Thanks!

and Connor said...

In the world of locally managed tablespace, initial/next are somewhat of a moot point, because we allocate extents in sizes defined by the internal bitmaps that manage the space.

For example

SQL> create tablespace blah datafile 'C:\ORACLE\ORADATA\NP12\BLAH.DBF' size 500m
  2  default storage ( initial 1m next 100m );

Tablespace created.

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x char(100)) tablespace blah;

Table created.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 500;

500 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0      65536
         1      65536

SQL> insert into t
  2  select rownum from dual connect by level <= 5000;

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6      65536
         7      65536
         8      65536
         9      65536
        10      65536
        11      65536

12 rows selected.

SQL> insert into t
  2  select rownum from dual connect by level <= 50000;

50000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0      65536
         1      65536
         2      65536
         3      65536
         4      65536
         5      65536
         6      65536
         7      65536
         8      65536
         9      65536
        10      65536
        11      65536
        12      65536
        13      65536
        14      65536
        15      65536
        16    1048576
        17    1048576
        18    1048576
        19    1048576
        20    1048576
        21    1048576

22 rows selected.

If I make those storage allocations at the table level, there is some basic honouring of the intention, but it it still aligned to fixed extent sizes, eg

SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table t ( x char(100)) storage ( initial 10m next 100m ) tablespace blah;

Table created.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 500;

500 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    1048576
         2    1048576

3 rows selected.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 5000;

5000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    1048576
         2    1048576

3 rows selected.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 50000;

50000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    1048576
         2    1048576

3 rows selected.

SQL>
SQL> insert into t
  2  select rownum from dual connect by level <= 500000;

500000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select extent_id, bytes from dba_extents
  2  where segment_name = 'T'
  3  and owner = user;

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    1048576
         2    1048576
         3    8388608
         4    8388608
         5    8388608
         6    8388608
         7    8388608
         8    8388608
         9    8388608

10 rows selected.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Travis Hunt, February 16, 2017 - 3:32 pm UTC

Travis Hunt, February 16, 2017 - 3:35 pm UTC

So if the tablespace is growing quickly, let's say to 500GB from initial 100m next extent 100m, but MAXSIZE UNLIMITED. Would there be a performance impact when growing the tablespace because the next extent is so low? The tablespace would also be for LOBs.

February 18, 2017 - 4:22 am UTC

There is a background process SMCO (space manager) that will proactively try grow a datafile so that foreground sessions dont get slugged with that overhead.

But if you fill a file *rapidly* then you could see a delay for your session whilst the file is grown.

Misunderstood

Jeff, February 16, 2017 - 7:52 pm UTC

I think he was asking about next extent when autoextending a the datafile, not of object extents stored inside a tablespace.

Live SQL

Share and learn SQL and PL/SQL; free access to the latest version of Oracle Database!

Dev Gym

Classes, workouts and quizzes on Oracle Database technologies. Expertise through exercise!

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Misunderstood

More to Explore

Live SQL

Dev Gym

"Next" Clause when creating a tablespace

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Misunderstood

More to Explore

Live SQL

Dev Gym