How to find Tablespace Fragmentation in Oracle Database
To determine if your tablespaces are having a problem with fragmentation, you can use the below script:
set pages 50000 lines 32767
select tablespace_name,count(*) free_chunks,decode(round((max(bytes) / 1024000),2),null,0,
round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
from sys.dba_free_space group by tablespace_name order by 2 desc, 1;
Here,fragmentationindex column will give your tablespace an overall ranking with respect to how badly it is actually fragmented. A 100% score indicates no fragmentation at all. Lesser scores verify the presence of fragmentation.
The free chunks count column will tell you how many segments of free space are scattered throughout the tablespace. One thing to keep in mind is that tablespaces with multiple datafiles will always show a free chunk count greater than one because each datafile will likely have at least one pocket of free space.
To determine if your tablespaces are having a problem with fragmentation, you can use the below script:
set pages 50000 lines 32767
select tablespace_name,count(*) free_chunks,decode(round((max(bytes) / 1024000),2),null,0,
round((max(bytes) / 1024000),2)) largest_chunk, nvl(round(sqrt(max(blocks)/sum(blocks))*(100/sqrt(sqrt(count(blocks)) )),2),0) fragmentation_index
from sys.dba_free_space group by tablespace_name order by 2 desc, 1;
Here,fragmentationindex column will give your tablespace an overall ranking with respect to how badly it is actually fragmented. A 100% score indicates no fragmentation at all. Lesser scores verify the presence of fragmentation.
The free chunks count column will tell you how many segments of free space are scattered throughout the tablespace. One thing to keep in mind is that tablespaces with multiple datafiles will always show a free chunk count greater than one because each datafile will likely have at least one pocket of free space.
No comments:
Post a Comment