-
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??
Sabitabrata
-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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
Sabitabrata
-
I saw it. There is mistake, should be smaller, not greater.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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 shankar_gk@hotmail.com and kgshangs@yahoo.com
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
A Wise Man Knows How much he doesn't know !!!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|