-
Hi All,
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.
-
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
-
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 ?.
Thanks,
-
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.
-
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
-
How do you check these parameters?
Ac
-
-
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%.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|