1 2 3 4 5 Previous Next 72 Replies Latest reply on Oct 10, 2012 2:27 PM by imran khan Go to original post
      • 45. Re: Fragmentation/Defragmentation of space in Database
        Girish Sharma
        Dude,

        I am remembering, in previous years (2-3 year back) there was a very popular discussion regarding default block sizes, people used to ask/argue about default and non-default block sizes and those threads had been spawn in more than 4-5 pages, but no final conclusion/agreement. Now, its fragmentation.

        Why all these happens, because neither docs nor MOS articles defines it perfectly, technically. We all used to explain in our terms, understanding; which further might rub into disputes. So, what I feels that whenever there is a popular discussion on OTN forum, Oracle should publish a white paper or an article in Oracle magazine to stop further discussion as well as to remove the darkness/mystery.

        My opinion... may/may not be fruitful though.

        Regards
        Girish Sharma
        • 46. Re: Fragmentation/Defragmentation of space in Database
          sb92075
          Some folks claim "defragmenting" provides benefits, yet NEVER produce any reproducible test case to substantiate their claim.

          I have yet to see any SQL that reliably measure or quantify this phenomenom call "fragmenation".
          At what point does a table or tablespace go from being "OK" to being fragmented (which metric at what value)??

          Simply asked, what is unit of measure for "fragmentation"?
          • 47. Re: Fragmentation/Defragmentation of space in Database
            Girish Sharma
            100% agree Sb. Because if I says 1+1=3 then I have to prove it; not like Mr X has said it is correct, Mr Y too has approved and Mr Z too has confirmed; its computer science and science work on facts not on fiction, it requires weapon not words..!!

            Regards
            Girish Sharma

            Edited by: Girish Sharma on Aug 8, 2012 9:20 AM
            • 48. Re: Fragmentation/Defragmentation of space in Database
              imran khan
              After so much of disscussion there must be a conclusion for this. You are the best person to conclude this suspense story.

              I dont think I can get a proper opinion for Fragmentation . If fragmentation doesn't exist then why oracle has provided the shrink option, row movement for a table,etc. I need a straight forwards answer so that I can close my thread.


              Regards,
              Imran Khan
              • 49. Re: Fragmentation/Defragmentation of space in Database
                You said: Locally managed tablespaces cannot get fragmented, ever.
                
                First, I'd like to question your definition of fragmentation and where it is defined. Do you think that fragmentation refers to space that is both unallocated and unusable? If yes, can you provide the reference?
                
                Do you say that fragmentation cannot exist in LMT because all free space will be of the same size that can always be reused?
                My definition, obviously, comes from Tom Kyte, eg http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1295801859138

                As this thread addresses many of your concerns, and Tom Kyte denies all of them, you may want to read it in full.

                As you will see the thread, as always, drifts away from the original question, because of the definition of 'fragmentation' no one agrees upon.
                If you say there is a consensus on the definition of 'fragmentation', I don't agree. I also don't agree on the usefullness of 'defragmentation'.
                As that usually doesn't solve anything, it is a waste of resources.

                -----------
                Sybrand Bakker
                Senior Oracle DBA
                • 50. Re: Fragmentation/Defragmentation of space in Database
                  Jonathan Lewis
                  imran khan wrote:
                  If fragmentation doesn't exist then why oracle has provided the shrink option, row movement for a table,etc.
                  I understand that one of the London hospitals offers a complete heart and lung transplant operation - but I hope I won't ever have to use it.

                  As you have probably read in the 4 articles I wrote on fragmentaiton, the term is used very carelessly and rarely defined when discussed. There are data distributions patterns that can have an impact on performance and free space distribution patterns that can be an irritant (though probably never a performance issue). In rare cases it is worth making an effort to do something about the distribution patterns - in most cases it is a waste of time for various reasons.

                  Reading the various posts you have made to this thread, my first thoughts are:

                  a) It is possible that the analytic side of PROD O&D is doing something fairly bizarre and therefore hitting a boundary condition that ought to be addressed by the supplier - but in the short term this could justify your DBA taking extreme action every weekend

                  b) It is more likely that your DBA is doing a load of work that is a waste of his time and the company's money at the weekend.

                  In your place, I'd ask the DBA for some objective evidence that his week-end activity is having a worthwhile impact on performance. If there is some real evidence, it might tell you how to get the same benefit with a lot less effort.

                  Regards
                  Jonathan Lewis
                  • 51. Re: Fragmentation/Defragmentation of space in Database
                    Jonathan Lewis
                    sybrand_b wrote:

                    My definition, obviously, comes from Tom Kyte, eg http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1295801859138
                    In what way was it "obvious" that your definition came from Tom Kyte.
                    As this thread addresses many of your concerns, and Tom Kyte denies all of them, you may want to read it in full.
                    You might also note that in the first reply that Tom gives he says: <i>"... it is pretty much impossible to get into a situation where you have unusable free space."</i>, so your dogmatic insistence that fragmentation is impossible in LMTs is impossible isn't even in agreement with your own chosen source.
                    If you say there is a consensus on the definition of 'fragmentation', I don't agree. I also don't agree on the usefullness of 'defragmentation'.
                    So if you don't think there is a consensus on the definition of "fragmentaiton" how can you insist that it doesn't exist or can't happen, or never needs fixing ?
                    As that usually doesn't solve anything, it is a waste of resources.
                    If it USUALLY doesn't solve anything, then you're implying that there are times when it does solve something.

                    For future reference, you might consider quoting the definition that Tom uses when you decide to tell people that it's a waste of time to worry about the only interpretation of the word fragmentation that you can think of.

                    Regards
                    Jonathan Lewis
                    • 52. Re: Fragmentation/Defragmentation of space in Database
                      Dude!
                      sybrand_b wrote:
                      My definition, obviously, comes from Tom Kyte, eg http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1295801859138

                      As this thread addresses many of your concerns, and Tom Kyte denies all of them, you may want to read it in full.
                      I did. Well, statements in the link you provided were made nearly 12 years ago. But assuming the author of these statements still stand behind them, with all due to respect, Tom Kyte's definition of fragmentation sounds very strange, and in my opinion is wrong.

                      He defined fragmentation based on a symptom of fragmentation. In other words, as long as there is no negative outcome of fragmentation, fragmentation does not exist. To me that is not objective.

                      He further outlined: Extents are never ever contigous - even if you put them right next to eachother "logically" (we are on a file system, file systems are not contigous, you have raid striping and other stuff in addition to the fact that file systems are not contigous. Even if they were, you achieve nothing by having the data contigous from a performance perspective)

                      Such a statement is over-generalized and also incorrect. Beside the OS, every modern filesystem will try to allocate contiguous blocks to avoid fragmentation where possible. Perhaps MS-Windows FAT32 wasn't at the time, but the statement still isn't true. There are many aspects of hardware that makes a huge difference in performance regarding at which location data is being stored, ever since, for example, outer versus inner tracks of a disk.

                      Tom Kyte shows only little knowledge about OS and hardware, at least at the time. Perhaps his statement should emphasize that fragmentation cannot be avoided, hence fragmentation is irrelevant. And since it is irrelevant, perhaps this has lead to the strange definition of the term fragmentation as it applies to an Oracle tablespace. But then again, it is a definition by symptom.

                      Many of this has already been explained and outlined in previous and other similar threads, but it seems to me you keep disregarding or don't understand such information, hence we are going in circles with this type of discussion.
                      • 53. Re: Fragmentation/Defragmentation of space in Database
                         but it seems to me you keep disregarding or don't understand such information
                        Why am I not surprised by the ongoing insults?
                        You really always need to flame me, don't you?
                        Why is everyone who doesn't agree with you 'wrong'?

                        ----------
                        Sybrand Bakker
                        Senior Oracle DBA
                        • 54. Re: Fragmentation/Defragmentation of space in Database
                          imran khan
                          In your place, I'd ask the DBA for some objective evidence that his week-end activity is having a worthwhile impact on performance.
                          Initially I talked about the performance but the real cause of concern is that he removes all the fragmentation from the tablespace/datafiles. he has shown me the fragmented space in the datafiles containing objects of multiple schemas. Unfortunately we dont have DBconsole to monitor the graph of objects stored on the datafiles. He confidently says that it is definitely the fragmentation and we need to remove the fragementation.

                          I am getting irritated now, To whom I have to believe/trust either the DBA who does the process of defragmentation (who shows me the proof) or the experts who has spent years on Database administration side( Jonathan Lewis) .

                          I have told him number of times that the free space will be re-used but he disagrees and tells me that we have to remove fragmentation by moving the objects from the original tablespace to a sample tablespace (create temporarily) then resize the datafiles of the original tablespace then again move those objects back to the original tablespace.

                          Please Jonathan one last favour. Please advice me how to approach it whether to go for defragmentation process or leave it . As I am another resource in my DBA team I have to take care of exports/imports and other things belonging to that database because my colleague(DBA) is going on vacation. Is there anything I have to be worried about regarding the DB?


                          Regards,
                          Imran Khan
                          • 55. Re: Fragmentation/Defragmentation of space in Database
                            Dude!
                            sybrand_b wrote:
                            but it seems to me you keep disregarding or don't understand such information
                            Why am I not surprised by the ongoing insults?
                            You really always need to flame me, don't you?
                            Why is everyone who doesn't agree with you 'wrong'?
                            Here we go again. Well, I said how it appears to me. You are very sensitive, but on the other hand you are very quick in accusing and insulting others, including me of spreading myth's and falsehood information. It does not matter if you take my last observation about your behavior and turn it around. I don't necessarily think this is personal though since you do it all the time.

                            I have come to the conclusion that there are various ways to deal with the situation, either to ignore your inappropriate comments and not to react on your posts ever again - hopefully the rest of the audience will realize. Or to leave this forum for good and forget about it. Something tells me that this is what you want.

                            I really did not mean to insult you and I'm sorry if what I wrote gave you the impression, but unfortunately, I have responded to your rude behavior. You may have marked my posts as "abuse", for what it's worth, I did not.

                            Personally, I'd prefer if you could come up with a technical answer or explanation to my previous response, rather than picking on personal bullshit again. Thanks.
                            • 56. Re: Fragmentation/Defragmentation of space in Database
                              Jonathan Lewis
                              imran khan wrote:
                              Initially I talked about the performance but the real cause of concern is that he removes all the fragmentation from the tablespace/datafiles. he has shown me the fragmented space in the datafiles containing objects of multiple schemas. Unfortunately we dont have DBconsole to monitor the graph of objects stored on the datafiles. He confidently says that it is definitely the fragmentation and we need to remove the fragementation.
                              In the phrase "it is definitely the fragmentation" - what is "it". Is the DBA simply proving that by doing this two-stage rebuild he is temporariliy eliminating any empty space between used extents ? If so, why bother ?
                              Is it the two-step process that worries you, or the fact that he is doing any rebuilding at all ?
                              How large is the tablespace before and after the rebuild ?
                              What is the point of shrinking a tablespace down from (say) 10GB to 2GB if it grows back to 10GB by the end of the week ... and would never grow any bigger ?

                              >
                              I have told him number of times that the free space will be re-used but he disagrees and tells me that we have to remove fragmentation by moving the objects from the original tablespace to a sample tablespace (create temporarily) then resize the datafiles of the original tablespace then again move those objects back to the original tablespace.
                              Is the original tablespace locally managed or dictionary managed. If locally managed does it use uniform extents or system-allocated extent sizing ? If dictionary managed has it had a "minimum extent length" set, and the default pctfree set to zero, and have all the objects been declared with storage clauses that are consistent with this minimum length and pctfree, and are there any objects subject to paralllel inserts ?
                              Please Jonathan one last favour. Please advice me how to approach it whether to go for defragmentation process or leave it . As I am another resource in my DBA team I have to take care of exports/imports and other things belonging to that database because my colleague(DBA) is going on vacation. Is there anything I have to be worried about regarding the DB?
                              It's not possible to give advice that's guaranteed to be 100% safe because there is always the very slight chance that there is a valid reason for at least SOME of the rebuilding; however, you need to weigh the risks of two operations: (a) if you do the rebuild, will you make a terrible mistake, (b) if you don't do the rebuild could something appallling happen. If you don't do the rebuild for two weeks and nothing untoward happens you will be able to demonstrate to the DBA when he comes back from his holdiay, that he has been wasting his time.

                              Regards
                              Jonathan Lewis
                              • 57. Re: Fragmentation/Defragmentation of space in Database
                                jgarry
                                >

                                >
                                It's not possible to give advice that's guaranteed to be 100% safe because there is always the very slight chance that there is a valid reason for at least SOME of the rebuilding; however, you need to weigh the risks of two operations: (a) if you do the rebuild, will you make a terrible mistake, (b) if you don't do the rebuild could something appallling happen. If you don't do the rebuild for two weeks and nothing untoward happens you will be able to demonstrate to the DBA when he comes back from his holdiay, that he has been wasting his time.
                                And if something coincidentally ruins performance after 1.5 weeks, you will have the worst: People saying "See!" doing an emergency defragmenting and not letting you solve the actual problem.

                                I think observation is the better part of valor here. I know I've come back from vacation to find really, really strange things have happened, as people jump to unwarranted conclusions and fix things in a negative manner. Trephination comes to mind.

                                You have to assume that procedures are correct, then prove they are wrong, especially if you are low man on the totem pole and know they are likely wrong.
                                • 58. Re: Fragmentation/Defragmentation of space in Database
                                  sb92075
                                  imran khan wrote:
                                  After so much of disscussion there must be a conclusion for this. You are the best person to conclude this suspense story.

                                  I dont think I can get a proper opinion for Fragmentation .
                                  If you can not measure "fragmentation", why do you believe it even exists?

                                  You have a few challenges.
                                  1) Proving it exists by post SQL whose result set shows "fragmentation" exists.
                                  2) Proving that by eliminating "fragmentation" that performance improves; again by posting SQL & results.
                                  3) Showing which metric at what value does a table go from being OK to being "fragmented"?
                                  4) what is the unit of measure for "fragmentation"?
                                  • 59. Re: Fragmentation/Defragmentation of space in Database
                                    imran khan
                                    Here I go. I got the job to maintain the database now . I will not be performing the defragmentation process and wait if I can find any weired behaviour of the database if I dont perform the defrag process. I hope everything goes well. Just performed export/import activity of size 600GB.


                                    Jonathan , I will update once there is any issue.


                                    Thanks,

                                    Imran Khan