Yes, the table has lot of activity of inserts, delete and updates. The procedure which does these activities goes slow at times and i use to do exp and imp, however, not been able to find out how much the table is fragmented. If you can suggest me a way to find out huch the table and its associated index have gone fragmented.
Updates and inserts are not much of a significance in relation to fragmentation. I guess one of the ways to monitor the amount of fragmentation due to deletes is to monitor the # rows in the table(s) regularly. Instead of export/import every now and then, consider rebuilding the indexes frequently (say everyday) and perform export/import, say weekly.
Thanks, Halo for your information. I am always doing the way you have suggested, however, without knowing how much these object are fragmented. Is there any information which i can gather from data dict views/tables about the fragmentation, say, user_tables, dba_extents, dba_segments, index_stat etc. If yes, then what all should i see ?.
I believe the easiest way to determine frag. is to monitor the count(*) on the table frequently and find the min and max # rows (for say a given day). The difference*average_row_length gives the amount of frag. Another thing, configure the pctfree and pctused for the table, so that you won't end up with too much frag. at block level.
I found this note on index fragmentation. Its asks to check ration pct_del to pct_used, if its more than 20%, then its fragmented and its a good candidate for rebuilding
Then check INDEX_STATS view particularly columns LF_ROWS
and DEL_LF_ROWS which shows current number of entry slots in
leaf blocks. On the other hand LF_ROWS_LEN and DEL_LF_ROWS_LEN shows
the total number of bytes associated with these entries.
As a rule of thumb, when the number of or spaced used by
deleted entries is greater than 15 - 20% of the total entries
you should consider rebuilding the index.
The script below calculates the ratio of the DEL_LF_ROWS and LF_ROWS
col name heading 'Index Name' format a30
col del_lf_rows heading 'Deleted|Leaf Rows' format 99999999
col lf_rows_used heading 'Used|Leaf Rows' format 99999999
col ratio heading '% Deleted|Leaf Rows' format 999.99999
lf_rows - del_lf_rows lf_rows_used,
to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio
where name = upper('&index_name');
You should also check the PCT_USED column of INDEX_STATS:
select name, btree_space, used_space, pct_used from index_stats;