Free Space Fragmentation Index (FSFI)

[SPV]

Hi folks,

I have been tasked with reviewing one of our Oracle 9i databases to see if we could gain performance benefit by reducing fragmentation. Step 1 of this is, of course, to gauge the current level of fragmentation.

This was done a few years ago when this database was in Oracle 8, but the person who did it has left the company. His documentation talks about a metric called the Free Space Fragmentation Index (FSFI), calculated like this:

[tt]select TABLESPACE_NAME,
SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS)))) Fsfi
from dba_free_space
group by tablespace_name
order by 1;[/tt]

In mathematical terms, this is:
[tt]
100(m/s)^1/2 ( m )
FSFI = ----------- = 100 [√](--------)
c^1/4 ( s x [√]c )

[/tt]where[tt]
m = max(blocks)
s = sum(blocks)
c = count(blocks)
[/tt]

This index is also quoted on a few websites, but I haven't been able to find any reference to its origins. I am keen to know how it works as a metric before I go calculating and quoting it in this piece of work.

Does anybody know of somewhere that explains how this equation comes about, what makes it a useful metric, and how to interpret the values?

Many thanks.

SPV

 

[dbtoo2001]

Are you running locally managed or dictionary managed?

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/tspaces.htm#24551

How was it determined that fragmentation is the cause of performance issues?

http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/instance_tune.htm#13538

 

[SPV]

Thanks for the reply. Those articles don't mention the FSFI itself, but they do seem to pull together useful information that I hadn't seen in one place before.

The fact that our major data tablespaces are all locally managed will seem to stand us in good stead.