tablespace fragmentation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: tablespace fragmentation

  1. #1
    Join Date
    Jul 2000
    Posts
    31
    Hi,

    I am having performance problems with my database. How can I tell if my tablespace has excessive fragmentation. The tablespace uses only a single datafile with autoextend on. The datafile is stored on a raid-5 level disk.

    Thanks.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    What storage clause you have used at the time creating tablespace/datafile and what was your database block_size and tablespace size ?
    Do you have segment level storage clause or not ?

    I do have little big script which give little map kinda picture for extents. If you are interested probably I will e-mail you.

    If you are using OEM you can use, tablespace map to see how your tablespace extents spread across files.


    [Edited by sreddy on 01-22-2001 at 06:50 PM]

  3. #3
    Join Date
    Mar 2000
    Location
    CA
    Posts
    317
    Can you give is more information on the storage parameters you are using.

    Then some one can help you.
    Thanks
    Kishore Kumar

  4. #4
    Join Date
    Dec 2000
    Posts
    21
    Kishore,

    How does the storage parameter affect the degree of tablespace fragementation? Thanks!

  5. #5
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843

    linda,

    try analyzing this posting and read the tech. white paper on fragmentation.

    [url]http://www.dbasupport.com/forums/showthread.php?threadid=5723[/url]

  6. #6
    Join Date
    Feb 2001
    Posts
    123
    Are you sure that the performance problems are not due to the fact that it is a RAID 5 disk?

    AFAIK, Oracle only recommends RAID-0 or RAID-0+1 if RAID is used.

    [Edited by Dave_A on 02-02-2001 at 09:30 AM]

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    RAID 5 is suitable for sequential access. It is better to know your application type (OLTP / Datawarehouse), before you change the RAID level.

    If the PCTINCREASE value for the tablespace is set to 0, then you need to manually coalesce the tablespace for defragmentation.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    em
    rem file: tabspfrag.sql
    rem
    rem Created By: Vitaliy Mogilevskiy (vit100gain@earthlink.net)
    rem Note: This script is from Kevin Loney "ORACLE8 DBA Handbook"
    rem Desc:
    rem
    rem This script measures the fragmentation of free space
    rem in all of the tablespaces in the database and scores them
    rem according to an arbitrary index for comparison
    rem
    rem largest extent 100
    rem FSFI = 100 * sqrt(------------------) * (-------------------------------)
    rem sum of all extents sqrt(sqrt((number of extents)))
    rem
    rem FSFI - Free Space Fragmentation index
    rem The largest possible FSFI (for an ideal single-file tablespace) is 100
    rem As the number of extents increases, the FSFI rating drops slowly. As the size of
    rem the largest extent drops, however, the FSFI rating drops rapidly.
    rem
    rem After running this script look at the rating number -- if < 30 then run
    rem s_tabsp_detail.sql (see tabsp_detail.sql)
    rem

    set newpage 0
    set pages 60
    col fsfi format 999.99

    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

    spool fsfi
    /
    spool off

  9. #9
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Back to the root of the original question: "I have performance problems, is this due to the fragmentation?"

    The question should be a litle more accurate, for example what is the manifestation of this performance problems, but the answer (with about 99.999% accuracy) is: NO, the fragmentation is not a source of your performace problems. Not anywhere near.

    Fragmentation has practicaly (read this as "absolutely") no effect on performace in the real life if the extents are sized correctly. But even if they aren't, the performance degradation because of the fragmentation is barely noticable. Segment fragmentation is not an isue, it is only a DBA mith from the old days and a source of great profits for producers of expencive reorg tools.

    If you can show me the facts that fragmentation is realy cousing performance degradation I'll show you that there is something very wrong with your aplication design or that your SQLs need some tuning (of, cource, there are allways exception to the rule...).
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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