Fragmenation

[jomon_s]

Hi...


Can anyone tell me how do we find object level fragmentation.


Regards

Joe

[sgodbole]

Fragmentation in an Oracle table has long been pointed at as a key cause of poor performance. Studies by Oracle Core Technologies group show that mere fragmentation of the table itself isn't a cause for concern, however, when table fragmentation is accompanied by data fragmentation (row chaining) there is a definite impact on performance.

First thing, you need to analyze tables and indexes. Then look for chained and/or migrated rows in tables. If you find lots of chained rows, then you can rebuild tables by moving them in same or another tablespace and while moving change storage parameters. Adjust pctfree (to reduce row-level fragmentation) and pctused (to reduce block-level fragmentation). You can also adjust initial and next. Another option is to use LMT with uniform sizes.

It should be noted that indexes on tables that undergo repeated insert and delete operations face the highest risk of fragmentation. So you need to find how fragmented are the leaf blocks in a given index. So it is important to determine the leaf block density of an index. The denser the contents of the leaf blocks, the better health the index is in. It is very useful to determine the density by running a script that retrieves the number of row values in the column(s) of the index and number of leaf blocks present in the index. For example, if there are 1000 row values adn 10 leaf blocks today, the leaf block density is equal to 1000/10 = 100 rows. If a week from today the number of rows is 1200, but there are 20 leaf blocks, the leaf block density is equal to 1200/20 = 60 rows. So there has been a 20 percent increase in the number of rows, but a 40% decrease in leaf block density. It is time to rebuild this index, and it potentially contains a lot of empty blocks.