Script to Report Table Fragmentation

Note : We have 3 script in sequence to capture the table fragmentation , each script having there own responsibility.

First script :The first script, TFSLDTFR.SQL, gathers table fragmentation
characteristics and inserts it into the newly created TFRAG table for
subsequent reporting.


Second Script : This script displays summary table fragmentation information.

Third Script : Detailed report of table fragmentation characteristics based on the
data in the tfrag table.



*********************First Script *****************************

The first script, TFSLDTFR.SQL, gathers table fragmentation characteristics
and inserts it into the newly created TFRAG table for subsequent reporting.

TFSLDTFR performs a single "analyze table 't' compute statistics" command.
You may want to change the compute to estimate for speed and usability.

Currently the following characteristics are gathered:

- Owner of table.
- Name of table
- Number of data blocks with rows
- Number of table extents
- Number of chained rows
- Number of blocks that have ever contained a row (high water mark)

Scripts two and three display table fragmentation information. The
information is queried from the TFRAG table which is created during
script one.




******************* Script *********************************


=============
First Script:
=============

SET ECHO off
REM NAME: TFSLDTFR.SQL
REM USAGE:"@path/tfsldtr table_owner table_name"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM ANALYZE on table, SELECT on DBA_TABLES, DBA_SEGMENTS, DBA_EXTENTS
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Craig A. Shallahamer, Oracle US
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Load the tfrag table with a given table's fragmentation stats.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM N/A
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:

set feedback on
set echo on
set verify off

def towner=&1
def tname=&2

rem *******************************************************************
rem * Goal: Analyze table to gather statistics
rem *******************************************************************
rem Specifically we are looking for:
rem - blocks ABOVE the hwm, i.e. empty blocks (dba_tables.blocks)
rem - average row length (dba_tables.blocks)

analyze table &towner..&tname compute statistics
/
col val1 new_value blks_w_rows noprint
col val2 new_value blks_above noprint
select blocks val1,
empty_blocks val2
from dba_tables
where owner = upper('&towner') and
table_name = upper('&tname')
/
rem *******************************************************************
rem * Goal: Get the number of blocks allocated to the segment
rem *******************************************************************
rem Specifically we are looking for:
rem - allocated blocks dba_segments.blocks

col val1 new_value alloc_blocks noprint
select blocks val1
from dba_segments
where owner = upper('&towner') and
segment_name = upper('&tname')
/

rem *******************************************************************
rem * Goal: Calculate the HWM
rem *******************************************************************
rem Specifically we are looking for:
rem HWM = dba_segments.blocks - dba_tables.empty_blocks - 1
rem HWM = allocated blocks - blocks above the hwn - 1
col val1 new_value hwm noprint
select &alloc_blocks-&blks_above-1 val1
from dual
/

rem *******************************************************************
rem * Goal: Get the Number of Fragmented Rows or Chained Frows (cr)
rem *******************************************************************
col val1 new_value cr noprint
select chain_cnt val1
from dba_tables
where owner = upper('&towner') and
table_name = upper('&tname')
/

rem ***********************************************************
rem * Goal : Determine the Segment Fragmentation (sf)
rem ***********************************************************
col val1 new_val sf noprint
select count(*) val1
from dba_extents
where owner = upper('&towner') and
segment_name = upper('&tname')
/
rem ***********************************************************
rem ***********************************************************
rem * Load the TFRAG table with the just gathered information.
rem ***********************************************************
rem ***********************************************************
rem *
rem * Create the tfrag table if it does not exist.
rem *
drop table tfrag;

create table tfrag
(
owner char(30),
name char(30),
hwm number,
blks_w_rows number,
avg_row_size number,
possible_bytes_per_block number,
no_frag_rows number,
no_extents number
)
/
create unique index tfrag_u1 on tfrag (owner,name)
/
rem *
rem * Delete and insert the new stats.
rem *
delete
from tfrag
where owner='&towner' and
name='&tname'
/
insert into tfrag values
('&towner','&tname',&hwm,&blks_w_rows,0,0,&cr,&sf)
/
commit;

set echo off
set verify on



*************************Second Script*********************************


SET ECHO off
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

ttitle -
center 'Table Fragmentation Characteristics' skip 2

select owner towner,
name tname,
no_extents exts,
(hwm - blks_w_rows)/(hwm + 0.0001) omega1,
no_frag_rows chains
from tfrag
order by 1,2
/



===========================================
Sample Output from the tfstfrag.sql script:
===========================================


Table Fragmentation Characteristics


Owner Table Name Exts Omega1 Chains
-------- ---------------------------------------- ---- ------ -------
scott s_emp1 0.000 0



*******************Third Script ************************************

SET ECHO off
REM NAME: TFSDTFRG.SQL
REM USAGE:"@path/tfsdtfrg"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on TFRAG table created by TFSLDTFR.SQL
REM ------------------------------------------------------------------------
REM AUTHOR:
REM Craig A. Shallahamer, Oracle US
REM ------------------------------------------------------------------------
REM PURPOSE:
REM Detailed report of table fragmentation characteristics based on the
REM data in the tfrag table.
REM ------------------------------------------------------------------------
REM EXAMPLE:
REM Detailed Table Fragmentation Characteristics
REM
REM
REM Table Owner : scott
REM Name : s_emp
REM Extents : 1
REM High water mark : 1
REM Blocks with rows : 1
REM Block frag: Omega1 : 0
REM Migrated rows : 0
REM
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 format a70
col tname format a70
col exts format 999
col omega1 format 90.9999
col chains format 99,990
col rpb format 999
col hwm format 9,999,999
col bwr format 9,999,999

ttitle -
center 'Detailed Table Fragmentation Characteristics' skip 2

set heading off

select 'Table Owner : '||owner towner,
' Name : '||name tname,
'Extents : '||no_extents exts,
'High water mark : '||hwm hwm,
'Blocks with rows : '||blks_w_rows bwr,
'Block frag: Omega1 : '||(hwm - blks_w_rows)/(hwm + 0.0001) omega1,
'Migrated rows : '||no_frag_rows chains
from tfrag
order by 1,2
/

set heading on


===========================================
Sample Output from the tfsdtfrg.sql script:
===========================================


Detailed Table Fragmentation Characteristics


Table Owner : scott
Name : s_emp
Extents : 1
High water mark : 1
Blocks with rows : 1
Block frag: Omega1 : 0
Migrated rows : 0