Autoallocate or uniform extents?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Autoallocate or uniform extents?

  1. #1
    Join Date
    Nov 2001
    Posts
    335

    Autoallocate or uniform extents?

    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.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  2. #2
    Join Date
    Jun 2001
    Location
    California
    Posts
    124
    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,

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Aug 2002
    Location
    Bangalore, India
    Posts
    405
    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.
    -nagarjuna

  5. #5
    Join Date
    Nov 2001
    Posts
    335
    Thanks to everyone for the info.
    One, who thinks that the other one who thinks that know and does not know, does not know either!

  6. #6
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Quote Originally Posted by nagarjuna
    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...

  7. #7
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,002
    Quote Originally Posted by dbbyleo
    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.
    this space intentionally left blank

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Quote Originally Posted by gandolf989
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174
    Quote Originally Posted by gandolf989
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width