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 90Above 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.77Above 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.66Shrink 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.27Now 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.02Thats 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 20Table 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.
Nice Explanation and easy to understand ..Good Work
ReplyDeleteThanks a lot.
DeleteGreat Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
please give us the query for this output. thanks in advance
ReplyDeleteWNER SEGMENT_NAME SEGMENT_TYPE TBS_NAME MBs WASTED_MB Wasted %
--------- ------------------------ ------------- ------------ ---- ---------- ----------
SPSDD KDM_SUPPLY_DEMAND_QUERY TABLE SPSDDDAT 41 8 20
Hi,
ReplyDeleteHere 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
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.
ReplyDeleteproduct details:
Oracle
DB Version 11.1.0.7.0
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 .
ReplyDeleteMost 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 .
ReplyDeleteGreat Explanation and more useful for anyone.Thanks for sharing...
ReplyDeleteoracle course in chennai
Hey, Wow Provided Post will be Very much Informative to the People Who Visit this Site. Good Work! Thank You for Sharing.
ReplyDeleteSEO Training
This article is well written and quite informative.
ReplyDeleteMore articles should be written and you have just found a follower.and more visit.
mainframe training in hyderabad
Very informative and well written post! Quite interesting and nice topic chosen for the post.
ReplyDeletethanks for sharing this nice post,
tally course in hyderabad
Can we do DML while alter table move ?
ReplyDelete