1 2 Previous Next 23 Replies Latest reply: Jan 24, 2014 8:04 AM by pudge Go to original post RSS
      • 15. Re: Fragmented tables
        Jonathan Lewis

        If you're dealing with partitions then you need to write code to address the partitions, not the tables.

         

        Since you've mentioned compression it's not surprising that you have a negative "fragmented space" - the avg_row_len doesn't cater for the savings from compression.so (for example) 1,000 identical rows of 400 bytes length each would total about 400K, but could be stored in about 450 bytes giving you "minus 399.5KB" of "fragmented space".

         

        If you're trying to save space by moving and compressing then remember that parallel operations implicitly make some space in the tablespace hard to use - at parallel 100 for a large object you are likely to make 100 "holes" in the tablespace at an average of 500KB each - which may not matter too much if the object is very large, but might if it's one of many partitions of only a few hundred MB.

         

        Think about your overall strategy first, and then you will be able to ask the right questions about how to apply it efficiently and safely.

         

        Regards

        Jonathan Lewis

        • 16. Re: Fragmented tables
          user3240461
          Jonathan Lewis,

           

          I want to clarify one CONCEPT about fragmentation in Dataware house Database as I have been asked by my management to rebuild ALL tables regardless of the function they fulfill.

           

          My theory is following

           

          i) We have Monthly Transaction tables , monthly tables older than CURRENT Month are compressed and NO DML occurs in it. My point of view is that there is no use of rebuilding such tables as they are already COMPRESSED and no DML happens on them to slow down performance. Am i right ?

           

          ii) Monthly Transaction Tables/Partitions go through BULK INSERT , some update and deletes but only happen for 1 month after which a NEW Monthly Partition is created. What benefit in performance will be get in rebuilding the partition which will be COMPRESSED after that month.

           

          iii) What kind of tables should be considered ideal for REBUILDING due to FRAGMENTATION which can effect performance of database in a Datawarehouse .

          • 17. Re: Fragmented tables
            Jonathan Lewis

            user3240461 wrote:

             

            My theory is following

             

            i) We have Monthly Transaction tables , monthly tables older than CURRENT Month are compressed and NO DML occurs in it. My point of view is that there is no use of rebuilding such tables as they are already COMPRESSED and no DML happens on them to slow down performance. Am i right ?

             

            ii) Monthly Transaction Tables/Partitions go through BULK INSERT , some update and deletes but only happen for 1 month after which a NEW Monthly Partition is created. What benefit in performance will be get in rebuilding the partition which will be COMPRESSED after that month.

             

            iii) What kind of tables should be considered ideal for REBUILDING due to FRAGMENTATION which can effect performance of database in a Datawarehouse .

             

            i) You are right. If you have a table that is used for a month, and then suffers no further DML you can rebuild it just once with compression after which there is no need to do any further maintenance on it - nothing is happening to it that will affect performance.  For such tables I would adopt a strategy of having them in working" tablespaces while there were active and then move them to "history" tablespaces - then the history tablespaces could be changed to read-only at some point. If I were keen to keep space "wastage" in the tablespace to a minimum I would consider a serial rebuild rather than parallel because of the semi-random gaps left by the "extent trimming" effects.

             

            ii) This is really making the same point as ( i ), if there is no DML on the partition after you've rebuilt it once with compression there is no need to rebuild it ever again.  You might want to look at the idea of copying the partition to create a table, then doing exchange partition - since you guarantee that no DML will take place while you're copying.

             

            iii) The general principles are well-known, but applying them in practice is very much up to the client.  Starting from "cold" - i.e. looking at the state of the database now without prejudice:  what objects seem to be taking significantly more space than you might expect (say 30% OTT); why is this happening; will the wastage (as a percentage) keep increasing with time; what could I change to stop the wastage from growing; how much of a performance impact will you gain by reclaiming that space; how much extra work will it take to reclaim the space as a one-off; how much work will it take to implement changes that stop the wastage.   One thought - if you're doing APPENDs with a lot of deletes then you could be leaving a lot of empty space behind, so that might be a case where you review your mechanisms for getting data into the data, and whether you want to do a one-off or regular rebuild.

             

            Regards

            Jonathan Lewis

            • 18. Re: Fragmented tables
              user3240461

              Jonathan Lewis,

               

              Thank you for sharing valuable information.

               

              I have checked alter table move with parallel 10 on partitions size in mb's on fragmented table and did not remove fragmented data.

               

              But one I exclude parallel from alter move, it works and defragmented partitions.

               

              We have some tables partitions size in 50gb, 100+ and 200+.

              Due to huge size, what you recommend how much parallel I use for it?

              • 19. Re: Fragmented tables
                jgarry

                If you are doing anything like updating blocks with uncommitted deletes or you have rows that extend across blocks, or maybe some other who-knows-what, watch for assm performance or space bugs in your version.  I say this even though you are talking about quiesced data, since I always get twitchy when I see fragmented tables queries, and can't help wondering why it's an issue, especially when management makes sweeping pronouncements.

                • 20. Re: Fragmented tables
                  user3240461

                  jgarry,

                   

                  After end of month we move data to another tablespace and compress. No DML are perform on it. New data are moved to new partitions. Yesterday I have done some tests on data size in MB's with parallel 10 and check it for fragmented data and it did not remove it.

                  Then I exclude parallel from query and it remove fragmented data.

                   

                  Now the problem is We have some table partitions size in 50G, 100G+ and 200G+. With out parallel I can not move and shrink partitions data.

                   

                  We perform compression activity on weekend?

                   

                  Now my questions is "with the size 50G, 100G+ and 200G+ how much parallel I use for alter move+compression?

                  • 21. Re: Fragmented tables
                    Billy~Verreynne

                    PQ processing ceiling is pretty much determined by the sizes of the I/O pipes, and amount of I/O bandwidth available.

                     

                    And this may not even be server specific, as the very same identical server (from h/w to ASM/RAC) will have a different ceiling from another, due to the I/O fabric layer used. FCoE over 1Gb Ethernet (single cable) does not give the same performance and capacity as dual 8Gb fibre channels, does not give the same performance and capacity as dual 40Gb Infiniband SRP channels, etc.

                    • 22. Re: Fragmented tables
                      Hemant K Chitale

                      You should re-read Jonathan Lewis's explanation as to why you are seeing "negative fragmentation".  Why do you care about what you call "fragmentation" ?  Can you not afford to "lose" a few MBs which would be much less than the MBs you gain from COMPRESSing the data ?

                       

                       

                      Hemant K Chitale

                      • 23. Re: Fragmented tables
                        pudge

                        In addition to the fine overall scenario already suggested by JL and others, for the copy that becomes a non-modifiable table a serious consideration should be made of determining a most useful ORDER based on the queries that are likely to be used (checking for the corner case of one popularly used order makes some different but not rarely used index have a very much worse cluster factor than currently). This will shrink the index that matches the physical order of the table. Some may question the utility of this extra step. I suggest finding one table where the order of insertion is drastically different from the order most recently queried and using that as a proof of concept.

                         

                        Also, regarding parallelism: You have plenty of things you've been ordered to rebuild. Within the horsepower of your machine complex, avoid the single job parallelism overheads and side effects and get the total work done faster by running several (non-conflicting disk destination if possible) JOBs at the same time with each JOB non-parallel. The primary point of parallel operations is to do one query as quickly as possible (selfishly) using all resources. Your current task seems to be to get many independent strands of work done as quickly as possible.

                         

                        Reading Tim Gorman's stuff on "Scaling to Infinity" would not hurt a bit to help understand the strategy and tactics deeply and make it easy for you to observe various opportunities in your exact situation in the future. (Way too much material for a forum post.)

                        1 2 Previous Next