DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: FSFI(Free Space Fragmentation Index)

  1. #1
    Join Date
    Jan 2002
    Posts
    65
    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

  2. #2
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  3. #3
    Join Date
    Jan 2002
    Posts
    65
    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

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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

  5. #5
    Join Date
    May 2001
    Location
    India
    Posts
    55
    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

  6. #6
    Join Date
    Jan 2002
    Location
    India
    Posts
    105
    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
  •  


Click Here to Expand Forum to Full Width