SET ECHO off
set linesize 200 
REM NAME:    TFSTFRAG.SQL 
REM USAGE:"@path/tfstfrag" 
REM ------------------------------------------------------------------------ 
REM REQUIREMENTS: 
REM     SELECT on TFRAG 
REM ------------------------------------------------------------------------ 
REM AUTHOR:  
REM    Craig A. Shallahamer, Oracle USA       
REM ------------------------------------------------------------------------ 
REM PURPOSE: 
REM    This script displays summary table  fragmentation  information.  The  
REM    information is queried from the tfrag table which  is  loaded via the  
REM    ldtfrag script.  Once the ldtfrag script has been run for a given  
REM    table, this report displays the  following information:  
REM 
REM      - Table owner  
REM      - Table name  
REM      - Segment fragmentation (number of extents)  
REM      - Number of table rows  
REM      - Table block fragmentation (1.0 bad, 0.0 good)  
REM      - Row fragmentation (chains)  
REM ------------------------------------------------------------------------ 
REM EXAMPLE: 
REM                          Table Fragmentation Characteristics 
REM 
REM    Owner    Table Name                               Exts Omega1  Chains 
REM    -------- ---------------------------------------- ---- ------ ------- 
REM    scott    s_emp                                       1  0.000       0 
REM ------------------------------------------------------------------------ 
REM DISCLAIMER: 
REM    This script is provided for educational purposes only. It is NOT  
REM    supported by Oracle World Wide Technical Support. 
REM  The script has been tested and appears to work as intended. 
REM    You should always run new scripts on a test instance initially. 
REM ------------------------------------------------------------------------ 
REM Main text of script follows: 
  
col towner	heading 'Owner'			format a8 	trunc 
col tname	heading 'Table Name'		format a40 	trunc 
col exts	heading 'Exts'			format 999 	trunc 
col omega1	heading 'Omega1'		format 0.999 	trunc 
col chains	heading 'Chains'		format 99,990 	trunc 
col hwm	    heading 'HWM'		format 99,990 	trunc

DOC>
3. Fragmentation within Heap Tables (Metalin note [ID 186826.1])
--------------------------------------------------------------------------------
Fragmentation within heap segments occurs over time as rows get deleted, updated 
or inserted. A segment is fragmented when some free space exists in the blocks 
but not sufficient space in any of them for Oracle to be able to insert a new 
one. This causes the segment to grow.
When a row that originally fitted into one data block is updated so that the 
overall row length increases, and the block's free space is already completely 
filled, Oracle migrates the data for the row to a new data block: but Oracle 
must scan more than one data block to retrieve the information for that row. 
Also, even if some blocks are empty after deletes, the high water mark (HWM) is 
not moved. Full table scans typically read up to the high water mark.
#


  
ttitle - 
  center  'SEGMENT [Table] Fragmentation Characteristics'   skip 2 
  
select owner	                     OWNER,  
       name		                     TABLE_NAME,  
       no_extents					"#EXTENTS", -- how many extents in segment 
       hwm "segment's HWM [#BLOCKS]",           -- HWM 
       blks_w_rows  "#USED_BLOCKS [below HWM]",   -- number of blocks below the HWM (current used block) 
       no_frag_rows					"row frag.(chains)", -- how many chained rows
       abs(hwm - blks_w_rows)/(hwm + 0.0001) 	"seg.block.frag.(0-good,1-bad)"  
from   tfrag  
order by 1,2  
/  
/*
 * omega1 . Table block fragmentation (1.0 bad, 0.0 good)
 *        . (HWM -  USED BLOCK) => "HWM diff" to current higher used block  
 */