Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2003
    Posts
    20

    Talking Unanswered: Database fragmentation problem

    Hi All,
    I am inserting millions of records and updating/deleting that many daily in a real time system which is giving serious fragmentation problems.
    Can any one suggest any tools or method to avoid or recover from database fragmentation ?
    Txs n rgds,
    Sandy..

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    if version 9i then use LMTs

    if not version 9i, then I would configure pct_free and pct_used on the corresponding tables to reflect how your table operates.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Oct 2003
    Posts
    706

    Post

    Also note that quite a lot of fragmentation is normal. Usually a database will fragment quite extensively, then start to stabilize as more and more opportunities arise to re-use the fragment blocks. A database might operate quite stably at a physical size 2x or 3x larger than it would be if fully compressed, and yet deliver perfectly satisfactory performance. If it grows well beyond that point and does not show signs of stabilization then obviously there's some problem .. but it might be structural, having to do with the database/index design in terms of its long-term solution.

    A real-time database with millions of inserts/deletes is a very tough situation because you really can't predict and really can't control either the volume nor the key-value spread of the incoming data. A database like that is going to be, and to remain, quite fragmented.
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I suggest you take a serious look at partitioning the tables (& indexes)

  5. #5
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    If it's a real time system - definitely use LMT as the_duck suggests.

  6. #6
    Join Date
    Sep 2003
    Posts
    20

    I am Using Oracle 8i

    Hi All,
    Thanks all for sending me your most welcome and valuable suggetion.The thing is that I am using Oracle 8i and I donno whether it supports LMT.
    As DUCK suggested the other method of observing tables,
    what should I do after detecting that a particular table space is fragmenting ? I have 4 major tables where records are inserted / updated / deleted repeatedly and all contribute equally to fragmentation.
    So what next should I do ?
    Any suggetions most welcome.
    Thanks a lot and regards,
    Sandy..

  7. #7
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: I am Using Oracle 8i

    Originally posted by skdas
    The thing is that I am using Oracle 8i and I donno whether it supports LMT.
    Yes 8i supports it.

    LMT= Locally Managed Tablespace

    i would use a uniform extent for a real time system personally.

    HTH
    Al

Posting Permissions

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