Urgent!! Table and Index fragmentation ??
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Urgent!! Table and Index fragmentation ??

  1. #1
    Join Date
    Nov 2000
    Posts
    79
    Hi All,

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


    Thanks in Advance

  2. #2
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    If the table(s) undergo deletes, then the table and it's assocaited indexes will get fragmented.

  3. #3
    Join Date
    Nov 2000
    Posts
    79
    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

  4. #4
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  5. #5
    Join Date
    Nov 2000
    Posts
    79
    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,





  6. #6
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    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.

  7. #7
    Join Date
    Nov 2000
    Posts
    79
    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

  8. #8
    Join Date
    Nov 2000
    Posts
    178
    How do you check these parameters?

    Ac

  9. #9
    Join Date
    Apr 2000
    Location
    Edison, NJ
    Posts
    759
    dba_tables

  10. #10
    Join Date
    Nov 2000
    Posts
    79
    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
  •  


Click Here to Expand Forum to Full Width