Oracle SQL script to report table fragmentation
Access Privileges:
Requires user with SELECT privileges on DBA_TABLES, DBA_SEGMENTS, DBA_EXTENTS and ANALYZE privilege on table.
Usage:
sqlplus [user]/[password] @[script_name]
where, [script_name] is one of the following scripts.
Script 1
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)
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 PURPOSE:
REM Load the tfrag table with a given table's fragmentation stats.
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
Script 2 and 3
Scripts two and three display table fragmentation information. The information is queried from the TFRAG table which is created during script one.
SET ECHO off
REM NAME: TFSTFRAG.SQL
REM USAGE:"@path/tfstfrag"
REM ------------------------------------------------------------------------
REM REQUIREMENTS:
REM SELECT on TFRAG
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 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
3rd 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 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 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