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

Thread: fragmentation ?

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    Exclamation

    how do i interprete this output? I got this sql from dbasupport.com

    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;


    BBI_TOMPASS_A 62.46
    BBI_TOMPASS_B 65.38
    BBI_TOMPASS_ROLAP_A 58.62
    BBI_TOMPASS_ROLAP_B 49.33
    BBI_TIMENSION_A 61.41
    BBI_TIMENSION_B 44.53
    BBI_TSSBASE_A 100.00
    BBI_ESSBASE_B 56.66
    BBI_SHIPMENTS_A 26.81
    BBI_SHIPMENTS_B 49.81
    BBI_STAGE 61.37
    BBI_TRADE_SPENDING_A 33.50
    BBI_TRADE_SPENDING_B 33.76
    RB 9.68
    SYSTEM 83.97
    TEMP 45.09
    USERS 84.06

    THANKS
    jigar


  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    FSFI = Free Space Fragment Information

    The numbers tell how much your free space on each table space had not been fragmented. The reason behind this interpretation is I have a user table space with no users and that seems to show 100. Fron this I interpret, the the 100 means it is not fragmented and 100> means it is fragmented to that extent, or that you have only that specified amount of continuous unfragmented segments.

    Hope this would fit, your question.
    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    if the value is < 30 then it's considered high fragmentation

  4. #4
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    I dont feel that the sql i ran to show %percent fragmentation helps. I need a sql script to show me how much fragmentation there exists. Does anybody have a script for that? I don't like 1 by metalink

    Jigar

  5. #5
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking voila

    Here ya go
    - Magnus


    CREATE OR REPLACE VIEW sys.mon_tot (tablespace_name, total, data_files)
    AS
    (
    SELECT tablespace_name, CEIL(SUM(bytes) / 1048576), COUNT(*)
    FROM sys.dba_data_files
    GROUP BY tablespace_name)
    /
    CREATE or replace VIEW sys.mon_free
    (tablespace_name, free, bigchunk, frags, percfrag)
    AS
    (
    SELECT tablespace_name,
    CEIL(SUM(bytes) / 1048576),
    CEIL(MAX(bytes) / 1048576), COUNT(*),
    100 - (MAX(bytes) / SUM(bytes)) * 100
    FROM dba_free_space
    GROUP BY tablespace_name)
    /

    --clear screen
    SET ECHO OFF
    SET PAGESIZE 35
    SET FEEDBACK OFF
    SET LINESIZE 132
    COLUMN Tablespace FORMAT A20 HEADING 'Tablespace Name'
    COLUMN Total_Size FORMAT 999,999 HEADING 'Total(M)'
    COLUMN Free_Space FORMAT 9,999 HEADING 'Free(M)'
    COLUMN Percentage FORMAT 90.0 HEADING '% Used'
    COLUMN decode FORMAT A14 HEADING '(Space Gauge)'
    COLUMN fragdec FORMAT A14 HEADING '(Frag Gauge)'
    COLUMN percfrag FORMAT 90.0 HEADING '% Frag'
    COLUMN frags FORMAT 9,999 HEADING 'Frags'
    COLUMN bigchunk FORMAT 9,999 HEADING 'Big|Chunk'
    COLUMN gap HEADING ' '
    COLUMN data_files FORMAT 99 HEADING 'Data|Files'
    --TTITLE 'Tablespace Analysis - Space Management and Fragmentation'
    set pagesize 0
    SELECT 'Analyzing the '||upper(value)||' database.'
    FROM v$parameter
    WHERE name = 'db_name'
    /
    set pagesize 35
    SELECT free.tablespace_name Tablespace, tot.total Total_Size,
    100 - ((free.free / tot.total) * 100) Percentage,
    DECODE
    ((CEIL(10-(free.free / tot.total) * 10)),
    0,'| .......... |',
    1,'| *......... |',
    2,'| **........ |',
    3,'| ***....... |',
    4,'| ****...... |',
    5,'| *****..... |',
    6,'| ******.... |',
    7,'| *******... |',
    8,'| ********.. |',
    9,'| *********. |',
    10,'| **danger** |') DECODE,
    free.free Free_Space, bigchunk, '|' gap, data_files, frags, percfrag,
    DECODE ((CEIL(percfrag / 10)) ,
    0,'| .......... |',
    1,'| *......... |',
    2,'| **........ |',
    3,'| ***....... |',
    4,'| ****...... |',
    5,'| *****..... |',
    6,'| ******.... |',
    7,'| *******... |',
    8,'| ********.. |',
    9,'| *********. |',
    10,'| **danger** |') fragdec
    FROM sys.mon_tot tot,
    sys.mon_free free
    WHERE Free.tablespace_name = tot.tablespace_name
    order by Percentage, percfrag
    /

    *** SAMPLE OUTPUT (note: looks good on SQL page) ***
    Big Data
    Tablespace Name Total(M) % Used (Space Gauge) Free(M) Chunk Files Frags % Frag (Frag Gauge)
    -------------------- -------- ------ -------------- ------- ------ - ----- ------ ------ --------------
    TEMP 15 20.0 | **........ | 12 10 | 1 35 16.1 | **........ |
    COMP 15 26.7 | ***....... | 11 3 | 1 10 78.8 | ********.. |
    TOOLS 20 45.0 | *****..... | 11 5 | 1 24 51.6 | ******.... |
    RBS 50 64.0 | *******... | 18 14 | 1 7 20.2 | ***....... |
    V0108 25 68.0 | *******... | 8 8 | 1 8 5.7 | *......... |
    SYSTEM 80 70.0 | *******... | 24 17 | 1 126 28.4 | ***....... |
    V0109 30 73.3 | ********.. | 8 8 | 1 1 0.0 | .......... |
    INDEX02 30 73.3 | ********.. | 8 7 | 1 48 16.0 | **........ |
    INDEX03 35 82.9 | *********. | 6 6 | 1 1 0.0 | .......... |

  6. #6
    Join Date
    Oct 2000
    Posts
    211
    starting from oracle8.0, the view dba_free_space_coalesced also gives the required things to know about tablespace fragmentation.
    The value of the field, percent_extents_coalesced should be as close to 100 as possible for better performance.
    HTH
    manjunath

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