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.
rem file: tabspfrag.sql
rem Created By: Vitaliy Mogilevskiy (email@example.com)
rem Note: This script is from Kevin Loney "ORACLE8 DBA Handbook"
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 largest extent 100
rem FSFI = 100 * sqrt(------------------) * (-------------------------------)
rem sum of all extents sqrt(sqrt((number of extents)))
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 After running this script look at the rating number -- if < 30 then run
rem s_tabsp_detail.sql (see tabsp_detail.sql)
set newpage 0
set pages 60
col fsfi format 999.99
, sqrt(max(blocks)/sum(blocks)) *
group by tablespace_name
order by 1
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?