DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Fragmentation again.

  1. #1
    Join Date
    Sep 2001
    Posts
    112
    I re-created the database recently and create Locally managed tablespaces, one using the following statement

    create tablespace NAME
    datafile '/export/home/oradata/NAME/datafile1.dbf' SIZE 500M EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

    After importting the objects a query I used to run which is the following

    SELECT SUBSTR(de.owner,1,8) "Owner",
    SUBSTR(de.segment_type,1,8) "Seg Type",
    SUBSTR(de.segment_name,1,35) "Table Name (Segment)",
    SUBSTR(de.tablespace_name,1,20) "Tablespace Name",
    COUNT(*) "Frag NEED"
    FROM SYS.DBA_EXTENTS de
    WHERE de.owner <> 'SYS'
    GROUP BY de.owner, de.segment_name, de.segment_type, de.tablespace_name
    HAVING COUNT(*) > 3
    ORDER BY COUNT(*) DESC


    Show alot more objects than it used to as fragmented.

    Can someone explain where i'm going wrong, I have even exported and re-imported one table and it remained just as fragmented. I thought the LMT would eliminate fragmentation, it may be i'm understanding the query incorrectly as well.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    your fragmentation concept is wrong, you are counting the number of extents which is incorrect, fragmentation is not based on that. When we talk fragmentation we refer to freespace fragmentation and block level fragmentation (chained rows, migration)

    number of extents does not matter

  3. #3
    Join Date
    Dec 2001
    Location
    UK
    Posts
    1,684

    I Agree

    Pando's correct. Don't be afraid of extents

    I fell into the same trap as you when I first used locally managed tablespaces. From my version 7 days I was always taught having multiple extents for a segment was a sin. Once you switch to locally managed tablespaces multiple extents cause less of an impact because extent management is handled within the tablespace, rather than at the dictionary level.

    When using AUTOALLOCATE you might see something like a table with a initial extent of 128K being assigned 2 64K extents. As the number of extents grows Oracle may decide that the next extent should be larger than 64K. You will therefore see a range of extent sizes over time.

    If you don't like this you can group your tables into tablespaces of similar extent sizes and use the uniform size option. This way, all extents within a tablespace will have identical extent sizes. This is better for preventing tablespace fragmentation as any gaps will always be of the correct size.

    It becomes even more fun in 9i where you can use Automatic Segment Free Space Management rather than freelists in locally managed tablespaces.

    Cheers

    Tim...

  4. #4
    Join Date
    Sep 2001
    Posts
    112
    Thanks guys, so the number of extents IS NOT good for managing fragmentation. Ok Cool.

    Now I have zero chained rows across my database .

    How can I check for migration, I understand this impacts performance.

    and what should I be looking for when I am hunting for fragmentation.

  5. #5
    Join Date
    Aug 2000
    Posts
    236
    Tim/Pando,

    In that case what is better with LMT's - Autoallocate or uniform size? The size of tables in my database vary heavily.

    And how come after importing into an LMT, analyzing and querying DBA_TABLES show an initial extent size of 100MB (same as my original dictionary managed TS/table)
    while DBA_EXTENTS show mutiple extents allocated of uniform size? What does the initial_extent value really mean?

    Thanks,
    Nizar

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    in LMT with extent size of 4MB for example ifyou specify initial of 20MB you wont get a big extent of 20MB, instead you get 5 4MB extents, this is how it works not a bug or anything

  7. #7
    Join Date
    Aug 2000
    Posts
    236
    Pando,

    In LMT creation, I can specify uniform size of 4 MB as you said. But how can I specify initial? LMT's do not allow initial/next and all the rest of the parameters.

    My question is:

    After an import into an LMT of uniform extent sizes of 512K, DBA_EXTENTS show exactly that allocation.

    But my dba_table query shows the INITIAL_EXTENT value to be 100M . What does this mean?

    Thanks,
    Nizar

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by nabaig
    Pando,

    In LMT creation, I can specify uniform size of 4 MB as you said. But how can I specify initial? LMT's do not allow initial/next and all the rest of the parameters.

    Don't worry about it. Put your object in a tablespace that is compatible with their general table size and forget about it. Forget all you learned about INITIAL, NEXT, and PCTINCREASE!


    After an import into an LMT of uniform extent sizes of 512K, DBA_EXTENTS show exactly that allocation.

    But my dba_table query shows the INITIAL_EXTENT value to be 100M . What does this mean?
    This just means that you specified INITIAL 100M but your segment was really chopped up into 512K extents.
    Jeff Hunter

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cannot specify initial in tablespace creation but you can when you create an object

    regarding the size, if I have extent sizes of 100K and I specify initial 256K when create a table in dba_extents i would see 3 extents for this table, adding up to 300K. Why this happens? This is because in LMT all extents have same sizes to reduce fragmentation, no matter the size you specify it will be always multiples of LMT extent size. In dictionary managed tablespace you can achieve this functionality as well with minimum extent clause the difference is in DMT you can have different size of extents but in LMT no (assuming using uniform size)

    in your 100MB initial you should see 200 extent of 512K but that is how it works to reduce fragmentation (have same extent sizes or multiple, uniform size would give us same sizes and autoallocate multiple)

  10. #10
    Join Date
    Aug 2000
    Posts
    236
    Pando/Jeff/Tim/et al....

    Thanks for your patience & support here. One final question if I may.

    How do I go about calculating what the best uniform extent size would be for a LMT?

    Thanks,
    Nizar

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