Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Lindsay.

Asked: July 10, 2000 - 11:24 am UTC

Last updated: July 31, 2006 - 8:27 am UTC

Version: 8.1.6.1

Viewed 10K+ times! This question is

You Asked

I have tables with various sizes in the same tablespace.

I have heard that the Next Extent needs to be the same for all tables in a given tablespace to cut down on fragmentation. Is this correct?

If so, does the Initial Extent need to be the same also?

Thanks

and Tom said...

Its true.

I love a new feature in Oracle8i release 8.1 and that is LOCALLY managed tablespace with UNIFORM sizing. This feature is such that storage options such as initial, next and pctincrease are totally ignored on object creation statements and the tablespace controls the space always. There is also a "best size" algorithm you can use as well where the tablespace manages the extents given to an object -- the object doesn't dictate what it wants beyond its initial extent.


Now, if you are using dictionary managed tablespaces -- then you must do this yourself and it can be hard. Yes, the initial extent counts -- if you set up an initial extent of 5meg and you use 2meg next extents with pctincrease=0 (i never used anything other then pctincrease=0 myself, although I only use locally managed tablespaces now...) and you use datafiles that are 6meg -- then you have just created a situation with every file that contains an initial extent will have a 1meg free extent that never gets used (bad example -- would never have a 6meg datafile really but it paints the picture).

So, if possible, use locally managed tablespaces (just add 64k to the size of EVERY file you add to it!! 64k is needed for the bitmap -- if you put a 5meg file in there and use uniform sizing of 1m -- then you will only get 4 1meg extents, the 5'th one will be too small -- you need to add a 5meg+64k datafile and then you'll get 5 1meg extents)....

Rating

  (5 ratings)

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

Comments

Same problem repeated.

A reader, March 21, 2003 - 11:27 am UTC

Does the same logic works for the partitioned tables where every partition table have different initial_extent and next_extent for every partition ?

Regards

Tom Kyte
March 21, 2003 - 1:46 pm UTC 

each partition is individually sized:



ops$tkyte@ORA920> CREATE TABLE t
  2  (
  3    x int primary key,
  4    collection_year int,
  5    y int
  6  )
  7  PARTITION BY RANGE (COLLECTION_YEAR) (
  8    PARTITION PART_95 VALUES LESS THAN (1996) storage (initial 1m),
  9    PARTITION PART_96 VALUES LESS THAN (1997) storage (initial 5m),
 10    PARTITION PART_01 VALUES LESS THAN (MAXVALUE) storage (initial 1k)
 11  )
 12  ;

Table created.



ops$tkyte@ORA920> select segment_name, partition_name, max(extent_id), sum(bytes)
  2  from user_extents
  3  where segment_name = 'T' group by segment_name, partition_name;

SEGMENT_NAME                   PARTITION_NAME                 MAX(EXTENT_ID) SUM(BYTES)
--------------------- ------------------------------ -------------- ----------
T                     PART_01                                     0      65536
T                     PART_95                                    15    1048576
T                     PART_96                                     4    5242880

Its Good

A reader, March 24, 2003 - 4:21 am UTC 

I ran the same DDL and DML to create the table 'T'. But did not understood where has it picked the values for next_extent, min_extent, max_extent. Can you please explain me.

SQL> select tablespace_name, initial_extent, next_extent, min_extent, max_extent
  2  from dba_tab_partitions where table_name = 'T' ;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
------------------------------ -------------- ----------- ---------- ----------
USERS                                 1048576      163840          1       2041
USERS                                 5242880      163840          1       2041
USERS                                   65536      163840          1       2041

Tom Kyte
March 24, 2003 - 7:57 am UTC 

well,first -- don't know how you got 2041 for max_extent, or even what release of Oracle you are on.  In 9iR2 I get:

ops$tkyte@ORA920> CREATE TABLE t
  2  (
  3    x int primary key,
  4    collection_year int,
  5    y int
  6  )
  7  PARTITION BY RANGE (COLLECTION_YEAR) (
  8    PARTITION PART_95 VALUES LESS THAN (1996) storage (initial 1m),
  9    PARTITION PART_96 VALUES LESS THAN (1997) storage (initial 5m),
 10    PARTITION PART_01 VALUES LESS THAN (MAXVALUE) storage (initial 1k)
 11  )
 12  ;

Table created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> column tablespace_name format a5
ops$tkyte@ORA920> select tablespace_name, initial_extent, next_extent, min_extent, max_extent
  2    from dba_tab_partitions where table_name = 'T' and table_owner = USER;

TABLE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
----- -------------- ----------- ---------- ----------
USERS        1048576                      1 2147483645
USERS        5242880                      1 2147483645
USERS          16384                      1 2147483645



The initial = amount of space requested 1m, 5m, and smallest extent possible (16k on this system)

In this case NEXT is null since I used SYSTEM allocated extents.  min extent is of course 1 and max extents is (as it true for LMTS) is effectively unlimited (RBS segments diverge from this rule, they have 32k max extents)

In 9iR1 I get:

TABLE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENT MAX_EXTENT
----- -------------- ----------- ---------- ----------
USERS        1048576      524288          1 2147483645
USERS        5242880      524288          1 2147483645
USERS          16384      524288          1 2147483645


and 524,288 just happens to be my UNIFORM size.  But otherwise the same.


same results in 8i.

Initial Extent

Irfan, April 06, 2003 - 6:15 am UTC

Hi Tom,

I have a table with the following

SVRMGR> select table_name,initial_extent,next_extent,min_extents,max_extents from dba_tables;

TABLE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT
------------------------------ ---------- ---------- ---------- ----------
HCAN 262215680 125849600 1 121

I would like to know from you, how this value is calculated for Initial Extent, hence the table was created with oracle default storage clause. And from dba_tablespaces I get the following information for the HCAN table in tablespace HIST.

SVRMGR> select * from dba_tablespaces where tablespace_name='HIST';

TABLESPACE_NAME INITIAL_EX NEXT_EXTEN MIN_EXTENT MAX_EXTENT PCT_INCREA STATUS CONTENTS
------------------------------ ---------- ---------- ---------- ---------- ---------- --------- ---------
HIST 10240 10240 1 121 50 ONLINE PERMANENT

The table inherits the properties of tablespace - In this case why the Initial Extent Differs?


Many Thanks


Tom Kyte
April 06, 2003 - 10:35 am UTC

well, not having been there to watch all of the steps -- I can only say that one of your assumptions is false.

One of these statements may be true:

o the default storage clause in fact was not used, the table in question had "initial ...."

o the default storage for the tablespace in question was DIFFERENT at the point in time the segment was created.

o HCAN is in fact not in the HIST tablespace and the tablespace it is in has totally different storage clauses

o HCAN was IN a different tablespace and moved to this one and kept its old storage clause.

In other words -- some other sequence of events other then what you believe happened -- happened.

Initial and Next extent calculation

Bala, July 31, 2006 - 7:04 am UTC

Hi Tom,

What would be the ideal size for Initial and Next extent? Will the size of Initial or Next extent affect the DML performance?


Tom Kyte
July 31, 2006 - 8:27 am UTC

use locally managed tablespaces with system allocated extents.


And we don't even have to consider all of the things you would have considered in the ancient past to try to figure out what might possibly be your "best" initial extent.

ASSM and bulk insert slowness

Brian, August 06, 2014 - 11:02 am UTC 

Hi Tom,

Does ASSM use INITIAL_EXTENT and NEXT_EXTENT, as we see in dba_tablespaces, at all?

We have seen some slow bulk inserts into an ASSM table on a 11gR2 RAC that runs on top of Sun T4 servers. A typical bulk insert contains about 100k rows issued from a Java application using JDBC batch API, while the table itself contains about 40 million rows. It takes about 9 minutes to complete. My initial impression is that the ASSM is grabbing the extents in too many small steps. Is this a possibility?

SQL> select block_size, initial_extent, next_extent from dba_tablespaces where tablespace_name = 'USERS';

BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT
---------- -------------- -----------
      8192          65536

Best,
Brian

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.