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  |
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 #537669 is a reply to message #537656] |
Fri, 30 December 2011 02:52  |
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
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Jan 10 16:05:28 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
