Home » RDBMS Server » Performance Tuning » Remove Table Fragmentation (oracle 11.2.0 , linux X86)
Remove Table Fragmentation [message #537643] Thu, 29 December 2011 23:59 Go to next message
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
Member
Hi All,
I have tried below steps for removing the table fregmentation but for some table i am not getting good result here.

Please have a look at below steps & help me to resolved the issue..

1. It will collect the data which are having more than 100MB fragmentation.

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len
/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE" from
dba_tables where owner in('a','b','c','d') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;


2. then move the object(table) to the same tablespace.

alter table abc move;

alter table bcd move;

alter table efg move;

3. also rebuild the dependent objects.

alter index abc_PK rebuild online;

4. Then analyze the table which are having more than 100MB of fragmentation.

exec dbms_stats.gather_table_stats('a','abc');

exec dbms_stats.gather_table_stats('b','bcd');

exec dbms_stats.gather_table_stats('c','cdf');


after that when check the table fragmentation, i am getting the same result, which i have collected from the 1st query.

Please let me know if i am missing any thing.

Regards,
Vimlendu
Re: Remove Table Fragmentation [message #537651 is a reply to message #537643] Fri, 30 December 2011 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 67948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
after that when check the table fragmentation, i am getting the same result, which i have collected from the 1st query.


This first query is wrong.

Regards
Michel
Re: Remove Table Fragmentation [message #537655 is a reply to message #537651] Fri, 30 December 2011 01:42 Go to previous messageGo to next message
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
Member
Hi Michel,

Please suggest me the different query .. or let me know where it is wrong..?

Regards,
Vimlendu
Re: Remove Table Fragmentation [message #537656 is a reply to message #537655] Fri, 30 December 2011 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What make you think there is some fragmentation?
When you will have an answer to this question, it is easy to create a query that reports what you think.
In other words, define "fragmentation".

Regards
Michel
Re: Remove Table Fragmentation [message #537669 is a reply to message #537656] Fri, 30 December 2011 02:52 Go to previous messageGo to next message
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
Member
Table fragmentation: - When a lot of DML operations are applied on a table, the table will become fragmented because DML does not release free space from the table below the HWM.

I have tried below query to identify the fragmentation.

"select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len
/1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE" from
dba_tables where owner in('a','b','c','d') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2)
> 100 order by 8 desc;"

I have tried the second query to identify the same.

select owner,table_name,round((blocks*8),2)/1024||' MB' "TABLE SIZE",
round((num_rows*avg_row_len/1024),2)/1024||' MB' "ACTUAL DATA",
round((blocks*8),2)- round((num_rows*avg_row_len/1024),2)/1024 "FRAG_SIZE"
from dba_tables
where owner in('a','b','c','d')
and round((blocks*8),2)/1024- round((num_rows*avg_row_len/1024),2)/1024>100
order by 4;

Can you please help me if i am wrong.


Regards,
Vimlendu
Re: Remove Table Fragmentation [message #537680 is a reply to message #537669] Fri, 30 December 2011 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't define what fragmentation is.

Quote:
the table will become fragmented because DML does not release free space from the table below the HWM.

This is wrong (in a certain point of view, it depends on what is the definition of your terms).

Regards
Michel
Re: Remove Table Fragmentation [message #537683 is a reply to message #537680] Fri, 30 December 2011 03:45 Go to previous messageGo to next message
crussed_sonu
Messages: 51
Registered: July 2007
Location: Delhi
Member
please correct me ... if you think that i am wrong .... & also provide me the link so that i can follow and get it resolve the fragmentation issue.

Regards,
Vimlendu
Re: Remove Table Fragmentation [message #537687 is a reply to message #537683] Fri, 30 December 2011 04:09 Go to previous message
Michel Cadot
Messages: 67948
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no fragmentation. Issue resolved.

Regards
Michel
Previous Topic: performace tunning
Next Topic: Performance problem with table when using one column as reserved word METHOD
Goto Forum:
  


Current Time: Wed Sep 22 05:40:13 CDT 2021