Hi All,

I am a bit confused about this issue FSFI ( Free Space Fragmentation Index)

SELECT Tablespace_Name,SQRT( MAX( Blocks ) / SUM( Blocks ) ) * (100 / SQRT( SQRT(COUNT(Blocks ) ) ) ) AS FSFI FROM DBA_Free_Space GROUP BY Tablespace_Name

I got output as

TABLESPACE_NAME FSFI
------------------------------ ---------
ORGSAUDITDATA 100
ORGSAUDITINDX 100
ORGSBCDATA 49.017478
ORGSCCRDATA 33.437056
ORGSCCRINDX 59.460356
ORGSDATA 100
ORGSINDX 18.850928
ORGSNAEXDATA 65.452198
RBS 11.937363
SYSTEM 47.801384
TEMP 62.266835
TESTS 100
TOOLS 100


Tablespaces with an FSFI value greater than 30 are fragmented.

So what I did was created a new Tablespace "XXX" and ran the above query.The output was

TABLESPACE_NAME FSFI
------------------------------ ---------
XXX 100

Now if FSFI value >30 is really Bad then how come a newly created tablespace show FSFI value of 100???

Please help I am a bit confused on this...
What should be the proper interpretation of FSFI value??


Now look at the following observation for the tablespace TEMP.

Initially:--
TABLESPACE_NAME FSFI
------------------------------ ---------
TEMP 62.266835


After this I did ALTER TABLESPACE TEMP COALESCE

And got the following
TABLESPACE_NAME FSFI
------------------------------ ---------
TEMP 100


This is crazy I suppose going by the logic that coalescing removes fragmentation.

Can some one please enlighten me on this??