Is Fragmentation Really Bad ???
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Is Fragmentation Really Bad ???

  1. #1
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Hi,

    I came across this on the web

    http://www.evdbt.com/MythsExtPerf.doc

    This totally debunks the theory of fragmentation and the effects it has on the database.

    My whole life till today I was under the impression that fragmentation is Bad and used to constantly monitor it.

    Can others please give their opinions on fragmentation and its effects.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  2. #2
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I read another article about a year ago saying basically the same thing. It had a graph showing that performance was only adversely effected when there were hundreds or thousands of extents.

    The key, however, was uniform sized extents. We've since completely revamped our object sizing in tablespaces with uniform extents (tablespace1_64k, tablespace2_512k, tablespace3_1m, etc.)
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #3
    Join Date
    Sep 2001
    Location
    Ohio
    Posts
    334
    I agree completely with Ken! We use Uniform Extents and don't reorg unless we get ~1000 extents. We use size 128K, 4M and 128M extent_size tablespaces. We have seen some improvement in performance, but mostly we save because we don't have "unusable" space in our tablespaces. Everything is reused.

    For a good article on this and how to implement, please see:
    http://otn.oracle.com/deploy/performance/pdf/defrag.pdf or article "How to Stop Defragmenting and Start Living" at http://www.hotsos.com/catalog/

    Good luck!




  4. #4
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    I'd be more concerned about SPACE Utilisation and Administration TIME rather than PERFORMANCE. The more fragmented your tablesapce, the more space you'll need to store the segments it contains, and the higher the change of processes falling over therfore increase time to fix the issue.

    I have a case in which I have > 2000 tables truncated and reloaded each week and their associated indexes (of course are rebuilt). Not all together mind, about 400 each week day. Therefore, truncating and recreating indexes that are different sizes can can cause the process to fail because the associated index with different sized extents live in the same tablespace. Even coalescing doesn't soemtime return enough free space to recreate the object. Therefore leaving the only optioon to expand the tablespace again.

    Obviously, maintaining uniform extents is the answer, but I'm just pointing out that the issue with fragmentation is more than just a PERFOMANCE issue. It is also a SPACE and TIME factor in which additional administration is required for fragmentated tablespaces... and thereby elevating my hate of teh COMPRESS=Y optiin in Exports.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

  5. #5
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    I have a few doubts....

    1. If uniform extents is/was the solution why was this never told in oracle documentation or anywhere till recently. I am sure they all knew it all along.

    2. There is no smoke without fire .. why did this myth ever start in the first place if it has no effects. It only make learning/managing oracle a nightmare.
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I believe that changes to more recent versions have made the # of extents relatively a non-issue.

    I'm with you .. I remember having a rule that we configure our tables and indexes to have no more than 5 extents over 2 years of use. I was deathly afraid of having a bunch of extents. It only makes sense to lessen the number of hops from extent to extent, right? That's got to have some additional overhead, right?

    I've got a better technical explanation in a white paper somewhere around here. I'll dig through my stuff and post the details when I find them.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Feb 2002
    Posts
    13
    The performance impact of multiple extents is historic, not a myth. The amount of time to move from extent a to extent b is nothing today. Fifteen, twenty, twenty-five years ago that was not the case; the amount of time required to move the head and await the rotational delay could be the difference between acceptable and unacceptable.

    I can tell you from experience, if you've gotten your burnt by something it's real difficult to 'put it in the past'.

    FYI. Try uniform extents, it's a lot of work up front, but saves a huge amount of work later.

  8. #8
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295
    THere is another aspect to fragmentation.

    15 years ago (when I started with Oracle) the emphasis was not on transactions but on reports. Yes, we could do transactions, but the 'paradigm' was report generation.

    Large numbers of small extents (the modern tendency) gives better transaction performance, but 'worse' report performance. Small numbers of large extents give better report performance but waste a lot of time and resources for small transactions.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  9. #9
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    heres another link supporting this

    http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_B:730289259844,Y
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

  10. #10
    Join Date
    Mar 2001
    Location
    New York , New York
    Posts
    577
    Does all this mean that one day pctincrease and dictioanry managed tablespaces will be obsolete.

    Thanks
    Ronnie
    ronnie_yours@yahoo.com

    You can if you think you can.

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