1 2 Previous Next 22 Replies Latest reply: Apr 2, 2013 6:04 PM by jgarry Go to original post RSS
      • 15. Re: database fragmentation
        moslee
        Hi EdStevens

        'In sync' over here means whenever an application query from those 3 identical DBs (the data they get are from a same source), the 3 results must be the same and the time taken for the DBs to return the results must be the same. If not, this will trigger the alert. As mentioned previously and please believe that over here, upon receiving the alert, past DBAs always relate it to fragmentation in either one of the DB. Every month these 3 DBs handle high transactions which past DBAs reckoned to be the cause of fragmentation. They reckon that fragmentation cause slowness in any one of the DB which they do not want.

        So what do they do when they receive this alert? They did a physcial copy and paste of those system and datafiles. Thereafter the application will query from those 3 identical DBs again. Issue is resolve when the 3 results are the same and the time taken for the DBs to return the results are the same.

        So as for me, whether is this a myth or fact, I need to try my best to improve the situation or to reduce fragmentation in these 3 DBs. And please don't harp on the fact that 8.1.6 are no longer under Oracle Support, like what that guy is doing. I know that they are no longer under support and also please accept the fact that not all IT management in the world wants to spend extra $ to upgrade.

        What I can do now is to find out if LMT can really reduce fragmentation in these 3 DBs. They were previously set at DMT. Oracle documentation states that LMT can reduce fragmentation but I need to prove and test to ensure that it really works. To be clear, I am against myth and that is the reason why I am asking for factual evidence to support the claim that LMT really does reduce fragmentation. But seems like no one is able to assist me in this..
        • 16. Re: database fragmentation
          sb92075
          if you can not measure "fragmentation", then how are you ever going to prove anything about it?


          The ONLY way table "fragmentation" can occur is from DELETE operations.

          in rough order of magnitude, how many rows get INSERTed, UPDATEd & DELETEd monthly?
          • 17. Re: database fragmentation
            rp0428
            HIJACKED THREAD!

            Please do NOT hijack or revive three year old threads.

            If you have a question or issue create a new thread and post the required information about it.

            Do not continue using this thread. It doesn't belong to you. If you think it contains relevant information then put a link to it in your new thread.
            • 18. Re: database fragmentation
              moslee
              Hi rp0428

              Actually I first continue this thread in response to Sybrand's statement...
              • 19. Re: database fragmentation
                rp0428
                >
                Actually I first continue this thread in response to Sybrand's statement...
                >
                Your reasons for hijacking a thread don't matter. Create your own thread.

                Even from your first post here you have repeatedly tried to get help with your own issue
                >
                How can I test the Before and After effects? I have DMT tablespaces in Oracle 8i SE(8.1.6.0) and I will be using sys.dbms_space_admin .tablespace_migrate_to_local('USERS');
                . . .
                Here's abit of my current situation which I have just taken over:
                . . .
                If you choose not to believe the problem that I have now, likewise I seriously don't think what you said can be of any valuable help at all.
                . . .
                So what do they do when they receive this alert? They did a physcial copy and paste of those system and datafiles. Thereafter the application will query from those 3 identical DBs again. Issue is resolve when the 3 results are the same and the time taken for the DBs to return the results are the same.

                So as for me, whether is this a myth or fact, I need to try my best to improve the situation or to reduce fragmentation in these 3 DBs. And please don't harp on the fact that 8.1.6 are no longer under Oracle Support, like what that guy is doing. I know that they are no longer under support and also please accept the fact that not all IT management in the world wants to spend extra $ to upgrade.

                What I can do now is to find out if LMT can really reduce fragmentation in these 3 DBs. They were previously set at DMT. Oracle documentation states that LMT can reduce fragmentation but I need to prove and test to ensure that it really works. To be clear, I am against myth and that is the reason why I am asking for factual evidence to support the claim that LMT really does reduce fragmentation. But seems like no one is able to assist me in this..
                >
                Those posts are ALL yours asking for help with YOUR problem.

                This thread is NOT yours and is three years old.

                If you have a question or issue create a new thread and post it.

                DO NOT POST IN THIS THREAD! It is a violation of forum policy to hijack another user's thread and subjects your account to possible termination.
                • 20. Re: database fragmentation
                  moslee
                  Hi sb92075

                  Thanks for your response.

                  Exactly, I cant measure fragmentation. This is the issue I am facing. Only if I have the 3 identical DBs (8.1.6) for development (instead of only 1 currently) with almost the same amount of transaction monthly, I can do my own testing by changing from DMT to LMT and if no alert is generated for the next 50-60 days or even longer. Then I can safely claim that LMT really reduce fragmentations in the context of 8.1.6 and in my current situtation. Though this is not a good way to measure fragmentation.

                  Purging of data (deletion) is done monthly. I cant tell you an exact number for how many rows now, but I can safely said that the number of such operations are in the millions per DB. I'm in the semiconductor industry.
                  • 21. Re: database fragmentation
                    moslee
                    Hi rp0428

                    Alright then. Will create a new thread. =)
                    • 22. Re: database fragmentation
                      jgarry
                      Well, since I don't see a new thread, I will answer in this one.

                      First, Sybrand is correct about the problem with upgrading DMT to LMT, it doesn't fix the problem you are purporting to fix. To fix an actual fragmentation problem, you would have to move the data around, changing the data pattern. This might make your performance worse, for various reasons.

                      Second, fragmentation is a loaded term, there are multiple definitions of it, and what you are describing doesn't sound like any of them. What you are describing sounds more like differences in data patterns between the databases. This doesn't have to be in the actual data, it could be in the statistics gathered. If you are using the Cost Based Optimizer, there are many things that could cause a difference in performance. The mode case for performance problems is the SQL, so you will often be asked to follow the "how to make a performance request" rules here, which includes getting execution plans for slow and fast times. Even if you are still using RBO, there are still other things that can change the performance of a given query between databases, even as simple as network problems or different transactional loads. Are you using CBO?

                      Third, as you've been told, you haven't made a causal connection between your syncronization and the performance. You haven't ruled out any confounding variables, such as shared pool problems, or even other things loading up the OS for some dumb reason or another.

                      Fourth, given that your former seniors were working under myths or worse, who knows what the real problem is?

                      Even though you are working with an old version, root problem analysis hasn't changed. What's changed is the people who did it correctly at that time have moved on, as have the greater number of people who did it incorrectly, with some unknown number of people converting from the latter to the former, thanks to the work of a few kind souls like Tom Kyte and Jonathan Lewis (read their books! Including Practical Oracle 8i).

                      In summary, your predecessors were doing it wrong, you are doing it wrong, and we're all here to help. But we have heard all your arguments before, over and over, and really, the horse is dead, buried, and turned to dust. There are expenses to not upgrading. There are expenses to upgrading. The thing about the modern version is, it has tools and feature to solve the problems that were never properly solved with the old magic wand waving.
                      1 2 Previous Next