Fragmentation? Too many extents?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Fragmentation? Too many extents?

  1. #1
    Join Date
    Sep 2001
    Posts
    27
    hello,

    After searching the old postings, I am still confused. Can someone clarify?

    Is a table fragmented if it has double digit extents?

    If I run a query like:

    select segment_name, extents
    from dba_segments
    where extents > 10;

    Should I reorg these tables based on this information?

    (I understand the difference between row chaining and migration and I understand that this is a separate issue from multiple extents.)

    Thanks for the insight!!!



  2. #2
    Join Date
    Aug 2001
    Posts
    111
    The table is not fragmented when it has double digit extents.

    There are some issues with very high numbers of extents, but that is more to do with the dictionary tables and how the default cluster key size is not set up to handle very high no. of extents in many tables.

    A good followup on this issue would be to look at the asktom.oracle.com site and search on the topic or
    http://www.ixora.com.au where Steve Adams also talks about the issue.

    Both basically debunk the idea that tables should be in one extent.

    Have Fun
    Performance... Push the envelope!

  3. #3
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Fragmentation can occur if there are too many deletes in the table/index or if there are different types of objects, all with different storage requirements stored in the same tablespace.

    Extents will keep on adding to the initial segment even if there is only one, growing table in a tablespace. So, high number of extents, per se does not mean high fragmentation.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  4. #4
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Fragmentation is not based upon the amount of extents per segment, it is based on the amount of freespace pieces in a tablespace.

    here is a quick way of finding how many free pieces in each tablespace :

    select tablespace_name, count(*) from dba_free_space
    group by tablespace_name
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  5. #5
    Join Date
    Oct 2000
    Posts
    467
    One has to look at the OS fragmentation as well. Even though the oracle blocks are not fragmented, the actual underlying Os blocks could be fragmented .
    Vinit

  6. #6
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    1. How can one find the degree of OS fragmentation?

    2. How can one repair OS fragmentation?

    TIA
    David Knight
    OCP DBA 8i, 9i, 10g

  7. #7
    Join Date
    Jul 2001
    Posts
    181
    This answer is specific to NT platforms!!!

    Defrag may be an option

    On unix you use veritas to defrag also!!

  8. #8
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Thanks for the response.

    Which Veritas tool?
    David Knight
    OCP DBA 8i, 9i, 10g

  9. #9
    Join Date
    Jul 2001
    Posts
    181
    Volume manager!

  10. #10
    Join Date
    Sep 2001
    Posts
    27
    Sureshy,

    What should I be looking for with the query:

    select tablespace_name, count(*) from dba_free_space
    group by tablespace_name

    What results would indicate fragmentation? More than 10, More than 20?

    And what steps should I take to correct this fragmentation?

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