Post date: 04-Jun-2009 11:52:06
Run the following as scripts (tfrag.sql)
------------------------------------------------------
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)
col val1 new_value blks_w_rows noprint
col val2 new_value blks_above noprint
execute dbms_stats.gather_table_stats('&towner','&tname', degree=>4, estimate_percent => 100,method_opt => 'for all indexed columns size auto');
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 * Load the TFRAG table with the just gathered information.
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
Run the following as script of sql to get the result gathered from first script
----------------------------------------------------------------------------------------------------------
col towner format a70
col tname format a70
col exts format 999999
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
/