4 Replies Latest reply: Jun 17, 2010 2:27 AM by 416690 RSS

    database fragmentation

    416690
      I have a question about database fragmentation.

      I know that fragmentation can reduce performance in query times. The blocks are distributes
      in many extents and scans process takes a long time. Oracle engine have to locate the address
      of the next extent..

      I want to know if there is any system view in which you can check if your table or index
      has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.

      Any useful script or query to do this, any interesting oracle system view??

      Any advice will bre greatly apreciatted.

      Thanks in advance
        • 1. Re: database fragmentation
          sybrand_b
          Provided you are using Locally Managed Tablespaces:
          Why are you still subscribing to Ye Olde Fragmentation Myth?

          Using LMT, objects won't get fragmented, EVER.

          Ye Olde Fragmentation Myth has been invented by consultants to keep their income high: they come over to de-fragment your database every few months, and this doesn't resolve anything.

          ----------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: database fragmentation
            rajeysh
            this will helps you to understand fragmentation.
            http://www.orafaq.com/node/1936
            • 3. Re: database fragmentation
              Hoek
              You aren't on a pre-8i database version, are you?

              http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1295801859138
              • 4. Re: database fragmentation
                416690
                Thank you, for your help.
                You're right I have LMT objects in Oracle 9i, but I have others databases in olders version, like....Oracle 7.
                In Oracle 7 I think there is fragmentation.

                Do you know any script or query to check the level of fragmentation???

                Thanks in advance