Friday, August 16, 2013

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.

12 comments:

  1. Nice Explanation and easy to understand ..Good Work

    ReplyDelete
  2. please give us the query for this output. thanks in advance

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

    ReplyDelete
  3. Hi,

    Here is the query to find table fragmentation.
    consider a table is fragmented only when Wasted %>30

    select a.owner,a.segment_name,a.segment_type,a.tablespace_name Tbs_name,
    round(a.bytes/1024/1024,0) MBs,
    round((a.bytes-(b.num_rows*b.avg_row_len))/1024/1024,0) Wasted_MB,
    round(((a.bytes-(b.num_rows*b.avg_row_len))/1024/1024/(a.bytes/1024/1024)),1)*100 "Wasted %"
    from dba_segments a, dba_tables b
    where a.segment_name = b.table_name
    and a.segment_type='TABLE'
    and a.owner='SCOTT'
    group by a.owner, a.segment_name, a.segment_type,a.tablespace_name,round(a.bytes/1024/1024,0),round((a.bytes-(b.num_rows*b.avg_row_len))/1024/1024,0),round(((a.bytes-(b.num_rows*b.avg_row_len))/1024/1024/(a.bytes/1024/1024)),1)*100
    having round(a.bytes/1024/1024,0)>19
    order by 7 asc;


    Regards,
    Sivakumar

    ReplyDelete
  4. Hi shiva my production server is slow, might be there is fragmentation... could you please mention and send me the complete steps to find fragmentation and defragment it. Also step should be reliable as it is production server.

    product details:

    Oracle
    DB Version 11.1.0.7.0

    ReplyDelete
  5. Most of the tables in my database is 100 per wastage.IF I go for 1 step table shrink in production hour will table will get lock or users can not access the table ?. My production database version is 11g ram is 25 GB and 8 CPUs .My table size is 50gb will it impact to any application .

    ReplyDelete
  6. Most of the tables in my database is 100 per wastage.IF I go for 1 step table shrink in production hour will table will get lock or users can not access the table ?. My production database version is 11g ram is 25 GB and 8 CPUs .My table size is 50gb will it impact to any application .

    ReplyDelete
  7. Great Explanation and more useful for anyone.Thanks for sharing...
    oracle course in chennai

    ReplyDelete
  8. Hey, Wow Provided Post will be Very much Informative to the People Who Visit this Site. Good Work! Thank You for Sharing.
    SEO Training

    ReplyDelete
  9. This article is well written and quite informative.
    More articles should be written and you have just found a follower.and more visit.
    mainframe training in hyderabad

    ReplyDelete
  10. Very informative and well written post! Quite interesting and nice topic chosen for the post.
    thanks for sharing this nice post,
    tally course in hyderabad

    ReplyDelete
  11. Can we do DML while alter table move ?

    ReplyDelete