Table Defragmentation

In this post I am going to explain the easiest method to eliminate the fragmentation on the table.

Usually in a OLTP environment, tables are often get fragmented due to many DML activities on the table. When the table are fragmented, queries on those tables will automatically get slow due to scanning many blocks which has no data on it! so how do we eliminate the fragmentation.

There are two ways to eliminate table fragmentation on a table.

 1. Shrink space compact
 2. DBMS_REDEFINITION

If the fragmented table is small in size we can go for the first option else the second option would be perfect.

In this post we are going to discuss using the SHRINK method.
consider the below table it had a huge fragmentation upto 90%!
When we use SHRINK method,

  
OWNER    SEGMENT_NAME             SEGMENT_TYPE       TBS_NAME       MBs  WASTED_MB   Wasted %
-------- ------------------------ ------------------ ------------ ----- ---------- ----------
SPSDD    KDM_SUPPLY_DEMAND_QUERY  TABLE              SPSDDDAT       290        258         90

Above output says KDM_SUPPLY_DEMAND_QUERY table sized 290M out of which 258M got wasted!

When we run any querys against this table it has to scan all the (both data & emmpty) blocks on the table i.e the whole 290M which is time consuming task, which leads to increase in execution time on the queries.

Since this is a samall table we can use SHRINK command to eleminate the fragmentation on the table.

the first step is to check whether row movement is enabled on the table which is going to reorganized, if not we need to enable it, like below

SQL> select row_movement from dba_tables where table_name='KDM_SUPPLY_DEMAND_QUERY' and owner='SPSDD';

ROW_MOVE
--------
DISABLED

SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY enable row movement;

Table altered.

Now we can rearrange used and empty blocks using below command.

  
SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY shrink space compact;

Table altered.

Elapsed: 00:04:42.77

Above command does segregating the data and empty blocks. At this time High Water Mark of this table still remains the same. DMLs are allowed in this stage.

Now, we just need to reset the HWM of this table. Be very caution in this stage because if the table being reorganized is heavily used (i.e concurrent DML activities) then Locks will be occurred which will impact the DB (if its in Prod)s. As a best practice while running this command parallely check any blocking on this session if any blocking occurred for a period of time then it should be terminated.

  
SQL> alter table SPSDD.KDM_SUPPLY_DEMAND_QUERY shrink space;

Table altered.

Elapsed: 00:00:14.66

Shrink space command will get complete in a short period of time for smaller tables.
At this stage HWM will get reset. Now we need to rebuild indexes for this table if any.

  
SQL>  select index_name,index_type from dba_indexes where table_name='KDM_SUPPLY_DEMAND_QUERY';

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
ODM_SUPPLY_DEMAND_QUERY_I1     NORMAL

SQL> alter index SPSDD.ODM_SUPPLY_DEMAND_QUERY_I1 rebuild online;

Index altered.

Elapsed: 00:00:09.27

Now gather stats on this table.

  
SQL> EXEC DBMS_STATS.gather_table_stats('SPSDD', 'KDM_SUPPLY_DEMAND_QUERY', estimate_percent => 55, cascade => TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:09.02

Thats it! we have successfully defragmented KDM_SUPPLY_DEMAND_QUERY table!

Below is the table info after defragment.

  
OWNER     SEGMENT_NAME             SEGMENT_TYPE  TBS_NAME      MBs  WASTED_MB   Wasted %
--------- ------------------------ ------------- ------------ ---- ---------- ----------
SPSDD    KDM_SUPPLY_DEMAND_QUERY  TABLE         SPSDDDAT       41          8         20

Table size got reduced from 290M to 41M!

All queries running against this table definitely will give better performance.

In the next post we will discuss about using DBMS_REDEFINITION method.