FSFI(Free Space Fragmentation Index)

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??

It is less than 30, not more than 30! The Free Space Fragmentation Index (FSFI) shows the size of the largest extent as a percentage of the total free space. 100% is best, optimal, less than 20,30% is bad.

Thanks Julian for the correction

If u see the following link u will see that here it says

"Tablespaces with an FSFI value greater than 30 may need free space manually coalesced."

http://udlcomputing.cjb.com/udl/trai...s-9.htm#item11


Thats the reason I got mislead and confused.

Thanks

I saw it. There is mistake, should be smaller, not greater.

Hi sabita,

This is shankar from bangalore. I need lots info about sga and cpu utilization and all.

plz let me know ur id.

My id is [email protected] and [email protected]

Regards,

G.shankar

dear all ,

interesting topic , today I created new tablespaces and imported a user in database but still FSFI is less than 20%

where is the problem lies ?


bye
virajvk