Results 1 to 11 of 11

Thread: Fragmentation

  1. #1
    Join Date
    Jan 2004
    Posts
    84

    Unanswered: Fragmentation

    Hi,
    If I have to find level of fragmentation of Database Objects , what is the baseline that should be chosen? Is it if number of extents have become more than a specific number ? If yes, then what is that number to be chosen?
    Thanks!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    # of extents does not mean fragmentation.
    That is a misnomer.

    first, you need to determine a few things like if you are using:
    LMTs - then your tablespaces will not be fragmented
    Auto Segment Space Management
    (anything else you might be incorporating)

    analyze all tables and indexes to determine any other fragmentation.

    only high-volume tables will most likely have the fragmentation you are looking for.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    84
    Yes, we are using LMTs with auto space management.
    I am not clear on what is meant by
    "Analyze Tables and Indexes to find any other Fragmentation "

  4. #4
    Join Date
    Jul 2003
    Posts
    2,296
    you won't have much fragmentation (if at all) using LMTs and ASSM.
    Oracle does almost everything you need for you.

    You should 'analyze index validate structure' for all your indexes to
    determine fragmentation.

    if you search this forum I posted a script to do just this thing.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  5. #5
    Join Date
    Feb 2004
    Posts
    45
    Now don't start throwing things......

    Fragmentation is not always the bane of a DBA's existence so it might be a good idea to work on other causes of performance problems. Disk transfer rates are high enough that you should not consider fragmentation as the first cause of throughput problems.

    When it's extreme (like when other things such as index depth or number of partitions are outrageous) sure, but fragmentation can be your friend, sometimes - like when it spreads used data across more volumes and helps balance I/O and when it keeps data space used up high giving you a quick way to reclaim space when you run out and cant talk the powers-that-be into more disks (this is a from-the-trenches trick, not a recommended practice - but it will let you carry on).
    It was working just 5 minutes ago - I promise !

  6. #6
    Join Date
    Jan 2004
    Posts
    84
    Yes , THE_DUCK ! I went through one of the posts wherein you have given a package to find indexes that need rebuilding and spools the output. It worked great and is indeed of great help to any DBA. Thanks for the same! Would like to know how does COMPRESS option in the rebuild statement work.
    Thanks,
    Preeti

  7. #7
    Join Date
    Jan 2004
    Posts
    84
    Also, Would like to know how to decide the following parameters that you have set in the package and can be customised.

    vMaxHeight := 3;
    vMaxDel := 20;

  8. #8
    Join Date
    Jan 2004
    Posts
    84
    Earlier , I used to find Indexes which have becoem bigger in size than the associated table itself and rebuild those indexes).
    Size was found from dba_indexes.
    Do you think it still is not a bad idea to continue doing so!

  9. #9
    Join Date
    Jan 2004
    Posts
    84
    THE_DUCK!
    I tried the package to give me indexes that need rebuilding .
    I ran the rebuild script and tested the package again. It gave me no indexes which need rebuilding. But next day I get good number of indexes again that need for rebuilding. What does this exactly imply?
    Pls help!

  10. #10
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    See what Oracle guru Tom Kyte has to say about index rebuilds here.

  11. #11
    Join Date
    Jul 2003
    Posts
    2,296
    agreed.
    you should hardly ever need to rebuild indexes and it shouldn't be part of your every day activities.

    only times when you are altering tables will you normally need to rebuild indexes (like partitioning or possibly direct loading and those
    types of things).

    you asked about fragmentation so I provided some info to help you.
    however, rebuilding indexes frequently is not needed in a general sense.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •