1 2 3 Previous Next 30 Replies Latest reply: Aug 17, 2010 5:55 AM by Charles Hooper Go to original post RSS
      • 15. Re: About Segment Tuning
        Charles Hooper
        gjilevski1 wrote:
        Hi,

        Oracle MOSC note 122008.1 has the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:

        - deleted entries represent 20% or more of the current entries.
        - the index depth is more then 4 levels.
        It appears that criteria for an index rebuild, which has been demonstrated several times in the OTN forums as faulty reasoning for an index rebuild, has been removed from Metalink (My Oracle Support) Doc ID 122008.1. Metalink (My Oracle Support) Doc ID 555284.1 has also been purged of an index rebuilding script based on similar criteria.

        Charles Hooper
        Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
        http://hoopercharles.wordpress.com/
        IT Manager/Oracle DBA
        K&M Machine-Fabricating, Inc.
        • 16. Re: About Segment Tuning
          gjilevski1
          Hi,

          Agree..

          http://oracle-online-help.blogspot.com/2006/11/clustering-factor_28.html

          The post will give you an idea of the index clustering factor apart from those reasons below.

          - deleted entries represent 20% or more of the current entries.
          - the index depth is more then 4 levels.


          Indexes on the above segments due to the inserts/updates can gain on performance if rebuild.

          Regards,

          Edited by: gjilevski1 on Aug 16, 2010 1:07 PM
          • 17. Re: About Segment Tuning
            618702
            Uwe Hesse,

            The point that you have mentioned is the base of the problem, "what is a table fragmentation?". the examples that you have given was perfectly OK and i do think that most of the people think that its the "fragmentation".

            The way that Oracle uses allocated extents are flawless.

            Ogan
            • 18. Re: About Segment Tuning
              Uwehesse-Oracle
              >
              The post will give you an idea of the index clustering factor apart from those reasons below.
              >

              Thank you, but I frankly do not think that I need any help in order to understand the clustering factor :-)

              Are you recommending to rebuild indexes in order to improve the clustering factor?

              Kind regards
              Uwe Hesse

              http://uhesse.wordpress.com
              • 19. Re: About Segment Tuning
                user12100209
                Thanks everybody. I am not an expert. So, finally Do I have to rebuild or not indexes???

                If I do, how can I know what indexes are??

                Greetings
                • 20. Re: About Segment Tuning
                  gjilevski1
                  Hi,

                  You have shrunk some segments. This involved deletes and inserts.

                  Check if there are indexes on those segments.

                  Indexes on the above segments due to the inserts/updates can gain on performance if rebuild.


                  Regards,
                  • 21. Re: About Segment Tuning
                    gjilevski1
                    Hi,

                    General answer is depends. Clustering Factor [ID 39836.1]

                    In this case index rebuild can gain performance.

                    Regards,
                    • 22. Re: About Segment Tuning
                      sybrand_b
                      Please comment on Charles Hooper's contribution.
                      Please come up with an actual example of an index of more than 4 levels, and with a working example demonstrating index rebuilds always do help, and/or anything demonstrating Richard Foote's research is faulty.

                      ------------
                      Sybrand Bakker
                      Senior Oracle DBA
                      • 23. Re: About Segment Tuning
                        gjilevski1
                        This discussion is going into

                        Index rebuild

                        Regards,
                        • 24. Re: About Segment Tuning
                          Jonathan Lewis
                          gjilevski1 wrote:
                          Hi,

                          Oracle MOSC note 122008.1 has the officially authorized script to detect indexes that benefit from rebuilding. This script detects indexes for rebuilding using these rules: Rebuild the index when these conditions are true:

                          - deleted entries represent 20% or more of the current entries.
                          - the index depth is more then 4 levels.
                          Oracle withdrew that note about 18 months ago following the feedback I gave them about it (See Re: Leaf nodes and Blevel in an index )

                          The note reappeared in June 2010 with the following comment relating to the two suggestions you have made:
                          <blockquote>
                          While this may have been valid criteria in earlier releases or could act as a starting point, this script is no longer valid. Please see the following articles: ... MOS 989093.1
                          </blockquote>

                          My guideline on "prior versions of Oracle" would be not later than 7.2.3.

                          Regards
                          Jonathan Lewis
                          • 25. Re: About Segment Tuning
                            Jonathan Lewis
                            user12100209 wrote:
                            Yes as Ogan says, I know how I can perform desfragmentation.

                            My question wanted to say, why those segments appears as fragmented whole time.
                            Uwe Hesse has given you some ideas about "fragmentation".

                            I've written a few general notes about the topic that might give you further ideas about the concept, and these may give you some idea why ADDM is reporting segment fragmentation, and allow you to work out whether any of it matters and, if so, how to avoid the issue.

                            https://jonathanlewis.wordpress.com/?s=fragmentation


                            Regards
                            Jonathan Lewis
                            • 26. Re: About Segment Tuning
                              amardeep.sidhu
                              Thank you, but I frankly do not think that I need any help in order to understand the clustering factor :-)
                              hehe ;)
                              • 27. Re: About Segment Tuning
                                Aman....
                                The post will give you an idea of the index clustering factor apart from those reasons below.
                                So correct me if I am wrong that are you proposing that if clustering factor is approaching near to the number of the rows of a table, the index needs a rebuilt?
                                - deleted entries represent 20% or more of the current entries.
                                - the index depth is more then 4 levels.
                                I won't comment on this but would surely suggest that you read couple of threads over here and also blog of Richard Foote to understand that the above two(or three) assumptions doesn't necessary mean that one has to go for index rebuilding.

                                HTH
                                Aman....
                                • 28. Re: About Segment Tuning
                                  311441
                                  gjilevski1 wrote:
                                  Hi,

                                  General answer is depends. Clustering Factor [ID 39836.1]

                                  In this case index rebuild can gain performance.
                                  And where precisely in that MOS note does it mention anything relating to Clustering Factor might mean an "index rebuild can gain performance".

                                  Answer: no where ...

                                  Cheers

                                  Richard Foote
                                  http://richardfoote.wordpress.com/
                                  • 29. Re: About Segment Tuning
                                    635471
                                    Just a note to remind everyone that "shrink space compact" does not move the high water mark on the segment so it does not speed full table scans -- "shrink space" does that.