Autoallocate or uniform extents?

[BV1963]

Any reason not to use EXTENT MANAGEMENT LOCAL AUTOALLOCATE setting while creating locally managed tablespaces? How does Oracle come up with the size of the next extent in case autoallocate is specified?
Is it better try to create multiple tablespaces with uniform extent and try to populate them with the objects with appropriate sizes?

Thanks.

[bhallar]

I din't find any Oracle-published alogorthim for AUTOALLOCATE. Looks like to me first 16 extents are of 8K and then next 128K (9.2.0.3)

Try allocating extents using

alter table TEST allocate extents;

SQL> select blocks, count(*), segment_name from dba_extents where tablespace_name = 'TEST_TBS' group by blocks, segment_name;

BLOCKS COUNT(*) SEGMENT_NAME
---------- ---------- ------------------------------
8 16 TEST
128 38 TEST


For Pro and Cons, Pls read the following

http://asktom.oracle.com/pls/ask/f?p...1162235053090,

[jmodic]

Originally posted by bhallar
I din't find any Oracle-published alogorthim for AUTOALLOCATE. Looks like to me first 16 extents are of 8K and then next 128K (9.2.0.3)
[/url]

It starts with 64K extents, not 8K. Your block size is obviously 8K and your query reported initial extents to be 8 blocks, not 8 Kb.

For a brief description how autoallocate algorythm (might) works, check this discussion (my reply on page 3):

http://www.dbasupport.com/forums/sho...0&pagenumber=3

[nagarjuna]

Uniform extent allocation is a good idea to use if most of your segments are of same size. But, it is not good idea if size of your segments range over a value from few mbs to gbs. In this case, either you have to allocate more space for smaller segments by giving bigger uniform size at tablespace level or have more number of extents for bigger segments by giving smaller uniform value at tablespace level.

Auto allocate is good idea if you dont want to control the size of your extents.

My idea to combine advantages of DMT as well LMT is something like this..

Create your tablespaces as DMT and use dbms_space_admin package to conver them to LMTs. This makes the tablespaces LMTs and at the same time extent management remains as user. User extent management allows us to create segments with our own extent size. This gives us more control over extent size as well as reduces contention to data dictionary by using bitmap segments to manage space allocation issues.

[dbbyleo]

This makes the tablespaces LMTs and at the same time extent management remains as user.

Ummm...I may wrong on this, but once a segment is in a LMT, you can't control any of it's extent sizes. When you specify the EXTENT MANAGEMENT LOCAL clause, the default is AUTOALLOCATE (the other option is UNIFORM). But once AUTOALLOCATE or UNIFORM is specified, user has no more control over extents sizing.

This is how I've understood this to be. So I don't understand what you're talking about...

[gandolf989]

Ummm...I may wrong on this, but once a segment is in a LMT, you can't control any of it's extent sizes. When you specify the EXTENT MANAGEMENT LOCAL clause, the default is AUTOALLOCATE (the other option is UNIFORM). But once AUTOALLOCATE or UNIFORM is specified, user has no more control over extents sizing.

This is how I've understood this to be. So I don't understand what you're talking about...

But databases normally have more than one tablespace. So you can create different tablespaces with different extent sizes, so if you want to change an extent size, than move it to a different tablespace. I don't understand your confusion.

[slimdave]

But databases normally have more than one tablespace. So you can create different tablespaces with different extent sizes, so if you want to change an extent size, than move it to a different tablespace. I don't understand your confusion.

Yes, I've got into the habit now of putting DWh fact tables into uniform extent sizes and dimension tables (which tend to vary in size greatly) into auto allocation tablespaces.

[dbbyleo]

But databases normally have more than one tablespace. So you can create different tablespaces with different extent sizes, so if you want to change an extent size, than move it to a different tablespace. I don't understand your confusion.

Nagajuna's comment made it sound like after you make a tablespace LMT, you can still change the extent sizing of the segments in it.

(I know you know move segments from tablespace to another...duh...) But nagajuna didn't mention anything about moving segments from tablespace to tablespace.

[BV1963]

Thanks to everyone for the info.