Check table fragmentation in Oracle
Fragmentation is caused in the table when we do update, and delete operations within the table which makes the space available in between the table which leads to fragmentation. The space is available with the table and table size grows even if it has less data in its data blocks. For checking fragmentation we have the following queries which will help to check fragmentation in between tables. If fragmentation is large in large size table it will lead to performance degradation also.
Check the top 25 fragmented tables in the Schema
Enter the schema name in which you find the top 25 fragmented tables.
select * from (
select owner,table_name,round((blocks*8),2) "size (kb)" ,
round((num_rows*avg_row_len/1024),2) "actual_data (kb)",
(round((blocks*8),2) - round((num_rows*avg_row_len/1024),2)) "wasted_space (kb)", ((round((blocks * 8), 2) - round((num_rows * avg_row_len / 1024), 2)) /
round((blocks * 8), 2)) * 100 - 10 "reclaimable space % "
from dba_tables
where owner in ('&SCHEMA_NAME' ) and (round((blocks*8),2) > round((num_rows*avg_row_len/1024),2))
order by 5 desc ) where rownum < 25;
Check-in percentage of table fragmentation
select table_name,avg_row_len,round(((blocks*16/1024)),2)||'MB' "TOTAL_SIZE",
round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE",
(round(((blocks*16/1024)-(num_rows*avg_row_len/1024/1024)),2)/nullif(round(((blocks*16/1024)),2),0))*100 "percentage"
from all_tables WHERE table_name='&TABLE_NAME';
Check the physical size of tables occupied in datafiles
Select table_name, round((blocks*8)/1024,2)||'MB' "size" from user_tables where table_name = 'EMP';
Select table_name, round((blocks*8)/1024,2)||'MB' "size" from dba_tables where table_name = 'EMP' and owner='SCOTT';Check the actual size of the table data present with the following query
select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from user_tables where table_name = 'EMP';
select table_name, round((num_rows*avg_row_len/1024/1024),2)||'MB' "size" from dba_tables where table_name = 'EMP' and owner='SCOTT';
Need clarification on the below.
I see that block*8 is used in “Check top 25 fragmented table in Schema” script and block*16 is used in “Check in percentage of table fragmentation” script. If i use block*8 or block*16 on both the scripts by substituting it vise versa . I getting different values of percentage reclaim and also the space utilization for a same table. Could you please explain block*8 and block*16 purpose. can’t we use either block*8 or block*16 for both the scripts
You can check parameter db block size or block size of your tablespace
You can use this query universally without finding the block_size of every tablespace you encounter.
select a.owner,a.table_name,a.last_analyzed,a.avg_row_len,round(((a.blocks*c.block_size/1024/1024)),2)||’MB’ “TOTAL_SIZE”,
round((a.num_rows*a.avg_row_len/1024/1024),2)||’Mb’ “ACTUAL_SIZE”,
round(((a.blocks*c.block_size/1024/1024)-(a.num_rows*a.avg_row_len/1024/1024)),2) ||’MB’ “FRAGMENTED_SPACE”,
round((round(((a.blocks*c.block_size/1024/1024)-(a.num_rows*a.avg_row_len/1024/1024)),2)/round(((a.blocks*c.block_size/1024/1024)),2))*100,2) “FRAG_PERCENT”, b.index_name
from all_tables a, dba_indexes b, dba_tablespaces c WHERE
a.table_name=b.table_name and
a.tablespace_name=c.tablespace_name and
a.table_name=’&table_name’;
Thanks 🙂
EVERYTHING IS GOOD
Thanks
I found mistake in “Check in percentage of table fragmentation” query. Could you please check once again.
I have found mistake in “Check in percentage of table fragmentation” query. Could you please check one again?
i fixed please verify and give me error if still occur. Thanks for your feedback and support.
Thank you so much for the reply. But I found TOTAL_SIZE, FRAGMENTED_SPACE output still wrong. Could you please clarify me if i am wrong.
With which script you verify please share
Check the physical size of tables occupied in datafiles
Check the physical size of tables occupied in datafiles