Optimal Extent size when setting up LMT's
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Optimal Extent size when setting up LMT's

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Posts
    236
    Hats off to the senior guys here who clarified a lot of things.

    Well - One last question before I go ahead and implement LMT's in my production database.

    1. Since my schema contains tables of diferent sizes ranging from 0.5M to 100M (Database size is roughly 10G), shall I use the autoallocate option or the uniform size.

    2 - When I tested autoallocate, the extent sizes were so different for each segment (first 8M, second 1M etc).
    Will this not cause fragmentation again?

    3. I am planning an export, creation of LMt's and import of data into that LMT? Is that the right way to go about it?

    Thanks,
    Nizar

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by nabaig
    Hats off to the senior guys here who clarified a lot of things.

    Well - One last question before I go ahead and implement LMT's in my production database.

    1. Since my schema contains tables of diferent sizes ranging from 0.5M to 100M (Database size is roughly 10G), shall I use the autoallocate option or the uniform size.

    2 - When I tested autoallocate, the extent sizes were so different for each segment (first 8M, second 1M etc).
    Will this not cause fragmentation again?

    3. I am planning an export, creation of LMt's and import of data into that LMT? Is that the right way to go about it?

    Thanks,
    Nizar
    1. I would suggest the UNIFORM size. If you choose system-managed extents, you can specify the size of the initial extent and Oracle determines the optimal size of additional extents, with a minimum extent size of 64 KB. This is the default for permanent tablespaces.

    2. If you choose to use AUTOALLOCATE, you have specified to allow the system (Oracle) to manage the extent size so it really should not matter what extent sizes are used or how they are chosen. The important thing is to ensure that you have enough free space to accomodate the extent request.

    If you want predictable extent sizes then you should use UNIFORM extent sizing instead of AUTOALLOCATE.

    And, do not worry about fragmentation.

    3. Yes.




  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    I like to setup different sized tablespaces to handle different tables. In your case, I might setup a LARGE tablespace with uniform size of 32M, a MEDIUM tablespace with 1M uniform extents, and a SMALL tablespace with 32K extents.

    You can create your LMT and then MOVE/REBUILD your tables/indexes to the new TS.
    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."

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    Jeff's advise is very good, I suggest you follow it.


  5. #5
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    I downloaded a white paper by Bhaskar Himatingka, Oracle Corp and Juan Loaiza, Oracle Corp that recommends

    Segments smaller than 128m should be placed in 128k extent tablespace.
    Segments between 128M and 4G should be place in 4M extent tablespaces.
    Segments Larger thean 4G should be placed in 128M extent tablespaces.

    When a segment reaches 1024 extents it is a good candidate to be moved to the next larger extent size tablespace....


    The article was called
    How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation.
    Doug

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by molonede
    I downloaded a white paper by Bhaskar Himatingka, Oracle Corp and Juan Loaiza, Oracle Corp that recommends

    Segments smaller than 128m should be placed in 128k extent tablespace.
    Segments between 128M and 4G should be place in 4M extent tablespaces.
    Segments Larger thean 4G should be placed in 128M extent tablespaces.

    When a segment reaches 1024 extents it is a good candidate to be moved to the next larger extent size tablespace....


    The article was called
    How to Stop Defragmenting and Start Living: The Definitive Word on Fragmentation.
    Thanks a lot for the tip. was there any explanation why. Meaning why those 3 groups and why those limits?

    I personally find the jump from 128K to 4M rather big. I use 64K (mostly for look-ups), 1M and 10M.


  7. #7
    Join Date
    Aug 2000
    Posts
    236
    I read that article also and it had more relevance to dictionary managed TS in V 7.0 and 8.0 . That article did not address LMT's.


  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by nabaig
    I read that article also and it had more relevance to dictionary managed TS in V 7.0 and 8.0 . That article did not address LMT's.

    That makes sense. I was wondering why we should care about the amount of extents in LMTs...


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