1 2 Previous Next 23 Replies Latest reply: Jan 24, 2014 8:04 AM by pudge RSS

    Fragmented tables

    user3240461

      Hi,

       

      We are using following query to check fragmented tables. Is it correct?

      Oracle 10.2.0.4 (64bit) and OS AIX 6.1 (64bit).

       

      select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",

       

      round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",

       

      round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"

       

      from all_tables WHERE Owner IN ('USERNAME');

       

       

      I am using alter table move with parallel (due to huge size of data ) option  and rebuild index for De-fragmentation. Is its OK?

        • 1. Re: Fragmented tables
          sb92075

          Is there any difference between free space & fragmented space?

          • 2. Re: Fragmented tables
            Baris Yildirim

            Hi

            move + rebuild -> it's OK

            you can also use Alter table ... enable row movement + alter table ... shrink space

             

            Regards

            • 3. Re: Fragmented tables
              user3240461

              Hi BarisYildirim,

               

              Thank you for your reply.

               

              The query I have posted is fine?

               

              Due to huge size of data we compress it. And on compress data shrink space does not work.

               

              I have one question please that can alter table move with parallel option creates fragmentation?

              • 4. Re: Fragmented tables
                Suntrupth

                You can rebuild your tables and indexes, But the question is how did you end up with fragmentation in the first place?

                 

                 

                If there are huge number of deletes and inserts (DMLs) on a tables, you would have to end up performing a re-org each time.

                 

                 

                You need to check the pattern of data load in such tables and check if re-org is required or not.

                 

                 

                If your have performed a huge delete and are never going to insert again, then it makes sense to re-org.

                 

                 

                You can use the following sql to know how formatted your blocks are in the table.

                SQL> set serveroutput on

                SQL> declare

                          v_unformatted_blocks number;

                          v_unformatted_bytes number;

                          v_fs1_blocks number;

                          v_fs1_bytes number;

                          v_fs2_blocks number;

                          v_fs2_bytes number;

                          v_fs3_blocks number;

                          v_fs3_bytes number;

                         v_fs4_blocks number;

                         v_fs4_bytes number;

                         v_full_blocks number;

                         v_full_bytes number;

                     begin

                       dbms_space.space_usage ('<USER_NAME>', '<TABLE_NAME>', 'TABLE', v_unformatted_blocks,

                       v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,

                       v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);

                       dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);

                       dbms_output.put_line('FS1 Blocks       = '||v_fs1_blocks);

                       dbms_output.put_line('FS2 Blocks       = '||v_fs2_blocks);

                       dbms_output.put_line('FS3 Blocks       = '||v_fs3_blocks);

                       dbms_output.put_line('FS4 Blocks       = '||v_fs4_blocks);

                       dbms_output.put_line('Full Blocks       = '||v_full_blocks);

                end;

                /

                 

                Regards,

                Suntrupth

                • 5. Re: Fragmented tables
                  Suntrupth
                  I have one question please that can alter table move with parallel option creates fragmentation?

                   

                  Are you moving in the same tablespace or to a different tablespace? Moving in the same tablespace you will need to have space as much as twice the size of the table consumed during move.

                   

                  You can use parallel option but the degree of parallelism depends on several factors like number of CPUs.

                   

                  alter table OWNER.TABLE_NAME move tablespace NEW_TBS parallel <number> nologging;

                   

                  Regards,

                  Suntrupth

                  • 6. Re: Fragmented tables
                    user3240461
                    Suntrupth,

                     

                    The sql you provided, I have checked as well. It takes time on table size in GB.

                     

                    Only for clarification the query I posted is correct? Is I have to check many tables in our dataware house.

                    • 7. Re: Fragmented tables
                      Hemant K Chitale

                      Your script doesn't report "fragmentation"  but attempts to report "excess blocks".

                       

                      Your multiplier of 16 is correct if the block size of the tablespace containing that table is 16KB.

                       

                      The script ignores the fact that every block has certain overheads -- thus an 8KB block probably stores somewhere like 7600bytes of data, not 8192bytes of data.

                       

                      What amount / proportion of "FRAGMENTED_SPACE" would you consider as qualifying a table for a rebuild ?  Why ?  What if you insert more rows into the table ?

                       

                      Are the tables in tablespaces with LMT and Segment Space Management AUTO ?  OR do you need to consider PCTUSED (which is ignored with Segment Space Management AUTO) ?   Are the tables set to the default PCTFREE of 10 ?

                       

                      Why do you want to / think you need to rebuild tables ?

                       

                       

                      Hemant K Chitale


                      • 8. Re: Fragmented tables
                        user3240461

                        Hemant K Chitale,

                         

                        Your multiplier of 16 is correct if the block size of the tablespace containing that table is 16KB.

                        DB block size is 16 kb.

                         

                         

                        What amount / proportion of "FRAGMENTED_SPACE" would you consider as qualifying a table for a rebuild ?  Why ?  What if you insert more rows into the table ?

                         

                        Following is the ouptput of query I have posted in my first post. These are partitions tables

                         

                        TABLE_NAMEBLOCKSNUM_ROWSAVG_ROW_LENTOTAL_SIZEACTUAL_SIZEFRAGMENTED_SPACE
                        CRBT_DLY_SU3426310113435020010453536.09MB112507.27Mb-58971.17MB
                        PST_VOICE_SU561976337300653337687808.8MB133753.26Mb-45944.46MB
                        PRE_WEB_SU254584362777170012539778.8MB74836.22Mb-35057.42MB

                         

                         

                        After the end of month I move partitions of tables into new created tablespace (alter table move partition with parallel option) , then rebuild index with parallel option. Now I am confused that how tables are fragmented with huge amount of data.

                        Are the tables in tablespaces with LMT and Segment Space Management AUTO ?  OR do you need to consider PCTUSED (which is ignored with Segment Space Management AUTO) ?   Are the tables set to the default PCTFREE of 10 ?

                        Yes tablespaces are LMT and Segment Space Management is Auto.

                        • 9. Re: Fragmented tables
                          Hemant K Chitale

                          Your Total_Size formula is in KBytes, your Actual_Size formula is in MBytes.

                           

                          Hemant K Chitale

                          • 10. Re: Fragmented tables
                            Baris Yildirim

                            Hi

                             

                            Compression? it's a new information for me.

                             

                            Are these tables uncompressed?

                              if these table are uncompressed and you are going to compress them, I think we don't need to talk about fragmentation anymore?

                             

                             

                            Are these tables partitioned?

                              if these table partitioned and you are going to compress them, I think you should learn (sorry you may already know) about sorting data

                              before compression to accomplish the best compression ratio, and write a script for this(sort + compress for partitioned tables)

                             

                               http://husnusensoy.wordpress.com/2008/02/01/using-oracle-table-compression/

                             

                            Old data in these tables might be changed in the future?

                              if old data in these tables might be changed, I think you should consider that in terms of compression unless you use advanced compression.

                             

                            Best Regards

                            • 11. Re: Fragmented tables
                              user3240461

                              Hemant K Chitale,

                               

                              Your Total_Size formula is in KBytes, your Actual_Size formula is in MBytes.

                              block size is in kb (16kb).

                              • 12. Re: Fragmented tables
                                user3240461

                                BarisYildirim,


                                Compression? it's a new information for me.

                                 

                                Are these tables uncompressed?

                                  if these table are uncompressed and you are going to compress them, I think we don't need to talk about fragmentation anymore?

                                Only for clarification compression remove fragmentation from table or partititions?

                                 

                                I use following command:


                                alter table table_name move partition partititon_name tablespace ts_name compress parallel 100;

                                • 13. Re: Fragmented tables
                                  Baris Yildirim

                                  Hi,

                                   

                                       You can think so because you do "move" at the same time

                                  Only for clarification compression remove fragmentation from table or partititions?

                                   

                                       if your huge tables won't be updated, you should use "PCTFREE 0" to gain additional %10 space


                                  alter table table_name move partition partititon_name tablespace ts_name compress pctfree 0 parallel 100;


                                  Regards


                                  • 14. Re: Fragmented tables
                                    Billy~Verreynne

                                    user3240461 wrote:

                                     

                                    I use following command:


                                    alter table table_name move partition partititon_name tablespace ts_name compress parallel 100;

                                     

                                    Parallel 100 - why?

                                     

                                    What do you expect will be better after the addressing the "fragmentation"?

                                     

                                    Hopefully you do realise that I/O is the slowest operation on the database and the biggest contributor to slow performance. Do you also realise that "fixing" the so-called "fragmentation" problem means I/O? And lots of it? And the more data there is to "fix", the more very expensive I/O is needed?

                                     

                                    What is the problem you are trying to solve on the data warehouse? What evidence do you have that the problem is related to "fragmentation"?

                                    1 2 Previous Next