LMT. Pros/Cons of Uniform Extent Vs. Auto Extents
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: LMT. Pros/Cons of Uniform Extent Vs. Auto Extents

  1. #1
    Join Date
    Nov 2000
    Posts
    224

    LMT. Pros/Cons of Uniform Extent Vs. Auto Extents

    Can someone please explain me and/or redirect me where I can find information on, Which Extent allocation to be used, UNIFORM or AUTO ALLOCATE.
    Is it drived by nature of database, like Data warehouse/OLTP/Hybrid? Your valuable suggestions are highly appreciated.

    Thanks!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    If you are going to use mixed sized objects in the same tablespace, I would use automatic mangement.

    If you will place similar sized objects in the same talbespace, I would use uniform extent sizes following the 160/5/160 rule (tables <5M in uniform 160K, tables under 160M in 5M uniform size, tables < 160M in tablespace with uniform size 160M)
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Regarding the smallest extent size, in this case 160K -- this ought to be equal to, or an exact multiple of, the block size multiplied by db_file_multiblock_read_count, no? 160 seems a peculiar number in that respect. I would expect to see 128, or 256 maybe.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Smallest extent size is DB_BLOCK_SIZE * 5, for UNIFORM.
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by grjohnson
    Smallest extent size is DB_BLOCK_SIZE * 5, for UNIFORM.
    yes, the smallest possible is 5*db_block_size, but i was meaning the smallest desirable extent size.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Ah ha, I thought I remembered the 160K number from somewhere, however the authors are referring to Oracle 7 when they recommend this size.

    Here's a quote ...

    These extent sizes are chosen to be a multiple of five blocks since Oracle7 will round all extent sizes to a multiple of five blocks. In Oracle8 extents won’t be rounded to a multiple of five blocks if they are a multiple of the minimum extent size for the tablespace. For Oracle8 databases, choosing the following extent sizes is a little simpler.
    1) Segments smaller than 128M should be placed in 128K extent tablespaces.
    2) Segments between 128M and 4G should be placed in 4M extent tablespaces.
    3) Segments larger than 4G should be placed in 128M extent tablespaces
    When the authors note later that ...

    By using large extents, scan operations can issue large contiguous disk reads. Extent sizes of 160K already capture most of this speedup. 5120K extent sizes capture all of this speedup. Most segments are only ever accessed through indexes, so the table scan time is totally irrelevant anyway.
    ... it seems to be an indirect reference to db_file_multiblock_read_count, although they don't mention the parameter anywhere.

    As a side issue, let us just note that the minimum extent size of (DB_BLOCK_SIZE*5) only applies to uniform allocation with automatic segment space management -- manual ssm has a minimum of DB_BLOCK_SIZE. It seems odd that "5" should crop up in both 9i LMT's with auto SSM, and in Oracle 7 -- maybe it's just coincidence.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Jeff, good link. Thanks.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  9. #9
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796

    Re: LMT. Pros/Cons of Uniform Extent Vs. Auto Extents

    Originally posted by Sharma
    Can someone please explain me and/or redirect me where I can find information on, Which Extent allocation to be used, UNIFORM or AUTO ALLOCATE.
    Is it drived by nature of database, like Data warehouse/OLTP/Hybrid? Your valuable suggestions are highly appreciated.

    Thanks!
    http://www.dbazine.com/burleson11.html

    http://www.dbazine.com/jlewis8.html

    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

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