1 2 Previous Next 18 Replies Latest reply on Jan 11, 2015 5:06 PM by rp0428

    fragmentation

    don123

      Hello experts

       

      This is with reference to the link on table fragmentation   http://www.orafaq.com/node/1936

      I have table and verified for table fragmentation.

       

      select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'MYTABLE';

      MYTABLE     704kb

       

      select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'MYTABLE';

      MYTABLE     25.4kb

       

      I am assuming that it is due to table fragmentation and applied the methods like CTAS, DBMS_REDEFINITION as suggested in the link.

       

      We do bulk delete and insert (data loading) on the tables in  Oracle 11g database and tablespaces are created with extent management LOCAL and segment space management AUTO.

       

      I observed that there is reduction is space and also there is lot of improvement (very fast) in delete and insert operations.

       

      I want to know whether the improvement in delete and insert operations is due to CTAS and DBMS_REDEFINITION ? Whether this techniques can be applied on production environment ?

        • 1. Re: fragmentation
          JohnWatson2

          Some people get very upset at any suggestion that fragmentation can either be defined or is a problem. You have been warned.

           

          However, that article was written in 2007 and describes for the most part techniques that date back to the last century. If you want to reorganize your tables, you should use ALTER TABLE...SHRINK SPACE;

          • 2. Re: fragmentation
            top.gun

            This is what fragmentation is:

            http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT1053

             

            The improvement of insert performance would be due to inserts using the free list (previously used blocks), instead of allocating virgin space.

            The improvement of delete performance may be due to the smaller table size.

            • 3. Re: fragmentation
              John Stegeman

              That document talks about fragmented free space and when Oracle automatically would coalesce fragmented free space within a block.

               

              That is not what the OP is measuring with the original query  - that query measures allocated but not used space, which has nothing to do with fragmentation.

               

              The only way that delete performance would dramatically improve based on a smaller table size would be if Oracle had to do something like a full scan to locate the row(s) to delete. Otherwise (assuming delete finds rows by index rowid), performance should not matter based on table size.

               

              As I always say to myself - performance tuning without data is meaningless. What I'd want to do is to trace the "slow" inserts/deletes and compare it with the "fast" ones to see what changed.

              • 4. Re: fragmentation
                JohnWatson2

                Point of information: objects in an ASSM tablespace do not have freelists.

                • 5. Re: fragmentation
                  don123

                  Thanks to all for the discussion.

                   

                  (1) I understood that ALTER TABLE ....SHRINK SPACE is to be used for defragmentation. Whether my first query identifies fragmentation ? How to identify fragmentation ?.

                  (2) If FREELISTS are not there in ASSM tablespace, how the INSERT operations are improved ?

                  • 6. Re: fragmentation
                    John Stegeman

                    alter table...shrink space has nothing to do with fragmentation, really; it has to do with re-claiming unused space.

                     

                    As I said earlier - tracing queries before and after will give you the definitive answer as to what is going on. Usually when people talk about fragmentation in the true sense of the word, it's not so much related to performance as it is to wasted space.

                    1 person found this helpful
                    • 7. Re: fragmentation

                      View your measure as similar to measuring the air inside your gas tank.

                      Does the car perform differently if you replace the existing gas tank which contains some air with a gas tank that contains no air (free space)?

                      • 8. Re: fragmentation
                        Jonathan Lewis

                         

                        select table_name,round((blocks*8),2)||'kb' "size" from user_tables where table_name = 'MYTABLE';

                        MYTABLE     704kb

                         

                        select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "size" from user_tables where table_name = 'MYTABLE';

                        MYTABLE     25.4kb

                         

                        I am assuming that it is due to table fragmentation and applied the methods like CTAS, DBMS_REDEFINITION as suggested in the link.

                         

                        We do bulk delete and insert (data loading) on the tables in  Oracle 11g database and tablespaces are created with extent management LOCAL and segment space management AUTO.

                         

                        I observed that there is reduction is space and also there is lot of improvement (very fast) in delete and insert operations.

                         

                        I want to know whether the improvement in delete and insert operations is due to CTAS and DBMS_REDEFINITION ? Whether this techniques can be applied on production environment ?

                        Assuming your are using 8KB blocks, and there are no LOBs in the table, then the two queries tell you avbout the number of formatted blocks and (very roughly) the minimum number of blocks you would need to hold the data if you recreated the table with PCTFREE set to zero. Since the difference is huge this suggests that a significant fraction of the table is formatted free space; in absolute terms, though, a change from 700KB to 25KB seems unlikely to do anything that would make a "lot" of improvement in the speed of bulk insert and delete operations.

                         

                        However, the nature of your bulk processing, in combination with the pattern of your indexing, could have a significant impact on performance for 2 reasons: first, some of your indexes may become extremely inefficient if the bulk deletes leave a large number of leaf blocks NEARLY empty while allocating new leaf blocks for subsequent inserts; secondly, if there is a time/sequence based pattern to your data and you fail to update the object-level statistics in a timely fashion you could find the optimizer switching from good plans to very bad plans as it uses historic statistics to optimise for current querying inputs.

                         

                        The benefit from your rebuild activities may actually appear because part of the process includes collecting new statistics.

                         

                        As John Stegeman points out - you need information about where the time goes before you can figure out whether or not the CTAS or DBMS_REDEFINITION is the correct strategy; he suggested tracing, I'd consider checking the sessions stats and wait events through v$sesstat and v$session_event as an alternative starting point to the investigation.

                         

                        Regards

                        Jonathan Lewis

                        1 person found this helpful
                        • 9. Re: fragmentation
                          Mark D Powell

                          John, I think Top's reference does apply since Table fragmentation is all about reuse of the space below the HWM and the material does cover how Oracle manages the space below both HWM's though this is only partially related to the OP's questions at the end of his post.

                          - -

                          don123, I think that running SQL trace on the insert and delete processes both before and after you perform the reorganizations (as John suggested) would give you support for why you see what you have observed.  The application of object reorganizations to a production environment is something that has to be determined on a case by case basis.  Observed performance benefits from reorganization are often transitory and sometimes a benefit in observed performance may be offset by a reduction in performance elsewhere.  For example after an index rebuild the measured performance of a range scan may be improved however the performance of the insert process will be adversely impacted because of an increase in the number of block splits required to support the insert activity.  Over time the improvement measured in the range scan will disappear as the index key distribution returns to the relative pattern that existed prior to the reorganization.

                          - -

                          HTH -- Mark D Powell --

                          1 person found this helpful
                          • 10. Re: fragmentation
                            jgarry

                            It's possible I'm misreading some of this, but anyways.

                             

                            Are you saying you only do append operations to load data, then delete a lot?  That would raise the high water mark, and not reuse the space below there, wasting it (though whether that would make a performance difference... depends... on all the stuff the others mentioned).  If you subsequently insert without append operations, that would reuse some of the space, but apparently there is not a lot of that happening.  Would it be possible to truncate?  Also, are you updating the data after you load it?  When you say fast delete and insert operations, are you doing upserts?

                            1 person found this helpful
                            • 11. Re: fragmentation
                              don123

                              Thanks to all for the suggestions.

                               

                              John stegeman

                              ----

                              As you suggested i  will generate trace and investigate the issue.

                              If alter table ....shrink space has nothing do with fragmentation and it is only reclaiming unused space, then what is fragmentation ?

                               

                              Jonathan

                              ----

                              I will investiage based on the reasons you mentioned.

                              The block size is 8k, we have sdo_geometry data type and this generates some LOB, PCTFREE is 10

                              • 12. Re: fragmentation
                                Jonathan Lewis

                                Don123,

                                 

                                sdo_geometry could be the source of most of the discrepancy. dbms_stats.gather_table_stats() doesn't gather the sizes of the columns "inside" the type, and the avg_row_len is based on the sum of the avg_col_lengths.

                                 

                                As a check - but not a long-term change to the data - you could try using the ANALYZE command (analyze table X compute statistics) then check the avg_row_len, you should find that it has got a lot longer and may give you a result that matches the block allocation.

                                 

                                Regards

                                Jonathan Lewis

                                • 13. Re: fragmentation
                                  don123

                                  Jonathan Lewis

                                   

                                  Really thanks. The average row length is increased after using ANALYZE command. The difference with block allocation is only 10%.

                                  In this user forum, i learnt that ANALYZE command is deprecated. How to gather stats in this case ?


                                  • 14. Re: fragmentation

                                    >How to gather stats in this case ?

                                     

                                    http://docs.oracle.com/database/121/ARPLS/d_stats.htm

                                    1 2 Previous Next