fragmentation ?

[jlakhani]

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

[sambavan]

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

[pando]

if the value is < 30 then it's considered high fragmentation

[jlakhani]

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

[jgmagnus]

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

[manjunathk]

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