Urgent!! Table and Index fragmentation ??

[gtm]

Hi All,

Is there a way to find out the table and index fragmentation.


Thanks in Advance

[Halo]

If the table(s) undergo deletes, then the table and it's assocaited indexes will get fragmented.

[gtm]

Hi,

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.

Thanks

gtm

[Halo]

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.

[gtm]

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 ?.


Thanks,

[Halo]

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.

[gtm]

Hi Halo,

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.

gtm

[ac]

How do you check these parameters?

Ac

[Halo]

dba_tables

[gtm]

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


Thanks

Note :

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

SELECT name,
del_lf_rows,
lf_rows - del_lf_rows lf_rows_used,
to_char(del_lf_rows / (lf_rows)*100,'999.99999') ratio
FROM index_stats
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%.