Reducing database fragmentation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Reducing database fragmentation

  1. #1
    Join Date
    May 2007
    Posts
    24

    Reducing database fragmentation

    I read this on another site. Its a sql command to find out if the DB is fragmented.

    select * from dba_segments where extents > 10;

    After I run this almost all of my tables are above 10, which the author of this article says "In general, if a table or index has more than 10 extents then rebuild it to fit into one extent". This is a pretty new DB and hasn't been used that much. Is this good advice?

    Thanks Paul

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Don't trust everything you read out there.
    Here in my shop we have several tables holding in excess of one billion rows apiece, how could them fit in a single extent? BS!
    Everything depends on volume.
    For your five more critical tables please post...
    1- Quantity of rows
    2- Average row lenght
    3- Initial/Next extent size
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    I disagree, it is a sql query that will tell you what has more than 10 extents. A table or index having more than 10 extents is not a sign of fragmentation. If you are using locally managed tablespaces than you have have tables with extents in the thousands and not see an issue.

    If you want to avoid fragmentation use locally managed tablespaces with either automatic storage of reasonably sized uniform extents.
    this space intentionally left blank

  4. #4
    Join Date
    May 2007
    Posts
    24

    Thanks

    Thanks for the replies.. We are using a RAC setup with ASM.. Does ASM account for the fragmentation?

  5. #5
    Join Date
    Jun 2006
    Posts
    259
    Quote Originally Posted by PAVB
    For your five more critical tables please post...
    1- Quantity of rows
    2- Average row lenght
    3- Initial/Next extent size
    What does that have to do with fragmentation?

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by ixion
    What does that have to do with fragmentation?
    have you seen what OP thinks fragmentation is? Just trying to use the same language of the user does, otherwise no conversation is possible.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by pam61
    Does ASM account for the fragmentation?
    No, it doesn't.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    There used to be a concern with dictionary managed tablespaces over segments with extremely ghigh numbers of extents, and in fact the number of extents used be be severely limityed in early versions of Oracle.

    With locally managed tablespaces there should be a very much lower level of concern, certainly not in the range the author suggests.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,026
    This seems to be the definitive word on the subject of fragmentation, http://technet.oracle.com/deploy/ava...pdf/defrag.pdf
    this space intentionally left blank

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