Is there a way to find out the table and index fragmentation.
Thanks in Advance
If the table(s) undergo deletes, then the table and it's assocaited indexes will get fragmented.
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.
Yes, the clue is pctfree and pctused.
I should check the value of these paramters and set them according to the activities on these tables.
Thanks for all your help.
How do you check these parameters?
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;
and pct_used should not be more than 80%.
Click Here to Expand Forum to Full Width