Questions on Fragmentation

[anuragmin]

Hi,
I have a couple of questions on Fragmantation.

-Does a Highly Fragmented Rollback segment affect performance and what does it signify.

-Why do indexes and primary keys get fragmented and what is their affect on performance.Are they linked to the tables they are created on. I mean if the table is fragmented does this mean that the primary key and undexes on that table will also be fragmented.

Any inputs will greatly be appreciated.

Thanks
Anurag

[Halo]

1. rb fragmentation is at extent level. This does not affect performance. It is better to have large extent sizes for the ts and the rbs, since extent allocation is resource intensive.
2. when tables undergo deletes, it causes fragmentation in the table and associated indexes; this is why they should be rebuilt after bulk deletes.

[anuragmin]

Hi,
Thanks a lot Halo.

But why should the indexes get fragmented after deleted.??

Thanks
Anurag

[pando]

when you delete index the b-tree structure will become unbalanced and the way to balance it to by rebuilding the index

the number of extents doesnt really matter (specially in LMT), wehn we talk about fragmentation we are really talking about the free space fragmentation and the block fragmentation

[anuragmin]

Originally posted by pando
the number of extents doesnt really matter (specially in LMT), wehn we talk about fragmentation we are really talking about the free space fragmentation and the block fragmentation

I am getting a very high values for the number of extents (> 50) on some of my indexes and primary key. Does this mean that I should ignore them.

How do I determine free space fragmentation and block fragmentation for indexes.

Also what is LMT.

Thanks
Anurag

[pando]

do you have tablespace manager? if you have you can see the fragmentation better other wise query dba_free_space

something like

select tablespace_name, count(*) "Free extents"
from dba_free_space
group by tablespace_name
/

Free extents should be as low as possible, if your extent sizes are uniform or multiple of each other (such as 128K, 256K, 512K etc ) the chance you get a value > 1 in the query result is pretty small since all free extents would be reused

block fragmentation is when you dont set your pctused, pctfree properly causing row migration. Large rows would cause row chaining but that is something we cant avoid I guess unless we use larger db block sizes or tell your analysts to normalize the database properly (if its OLTP of course...)

LMT is Locally Managed Tablespace

[Edited by pando on 05-14-2001 at 05:10 PM]