tablespace fragmentation

[una]

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.

[sreddy]

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]

[Kishore]

Can you give is more information on the storage parameters you are using.

Then some one can help you.

[linda]

Kishore,

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

[sreddy]

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

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

[Dave_A]

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]

[tamilselvan]

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.

[pando]

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

[jmodic]

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...).