Is Fragmentation Really Bad ???

Quote:

---

Originally posted by tamilselvan:
For a large table, I do not allow Oracle to allocate the next extent, because Oracle always use the first available data file assigned to the tablespace. In that process many data files are not at all used for a longer time.
I DO NOT LIKE IT. That hurts performance.
I always spread the I/O on all the available disks.

---

Hi tamilselvan,
How we can force Oracle to allocate the next extent from a particular datafile if there are several datafiles having enough space for next extent ?

Sanjay

Hi,

alter table vj_test allocate extent (datafile 'e:\oracle\oradata\pandasup\sys\user01.dbf') ;

Thanks.
Vijay.

Quote:

---

Originally posted by tamilselvan
Fragmentation within an extent is bad. That is a different story. I will talk about later.

---

Tamil,

Since we are all talking about fragmentation here lets talk about the aspect of extent fragmentation also.

The notion of uniform extents means you never have to worry about fragmentation works for tables BUT NOT INDEXES (of tables with inserts, updates and deletes).

Regular index rebuilds will help recover space and improve performance.

Index Browning
Index browning is the process where btrieve leaf nodes are deleted leaving holes in the index. Under the btrieve structure the vacancies left by data deletion aren't filled. Using the analagy of a tree it is as if the leaf has turned brown because it is dead.

If an index shows excessive browning it will impact performance due to the large number of transversals required to find the required values.

A browned index should be dropped and rebuilt.

Determining Index Browning

The number and length of deleted leaf nodes to the total leaf node length and number are important ratios to be considered. These can be calculated as:

% of Leaf Node Length is deleted =

del_lf_rows_len / ( del_lf_rows_len + lf_rows_len )

% of Leaf Nodes are deleted =

del_lf_rows / ( del_lf_rows + lf_rows )

" The notion of uniform extents means you never have to worry about fragmentation works for tables BUT NOT INDEXES
".

I believe you are talking about fragmentation WITHIN an index, in which you'd be correct.

UNIFORM Local Managed Tablespaces WILL eliminate index SEGMENT fragmentation within a tablespace.

Just clarifiying to readers who may have been confused.

Cheers


[Edited by grjohnson on 05-21-2002 at 07:34 PM]

Quote:

---

Originally posted by KenEwald
The notion of uniform extents means you never have to worry about fragmentation works for tables BUT NOT INDEXES (of tables with inserts, updates and deletes).

Regular index rebuilds will help recover space and improve performance.

Index Browning
Index browning is the process where btrieve leaf nodes are deleted leaving holes in the index. Under the btrieve structure the vacancies left by data deletion aren't filled. Using the analagy of a tree it is as if the leaf has turned brown because it is dead.

If an index shows excessive browning it will impact performance due to the large number of transversals required to find the required values.

A browned index should be dropped and rebuilt.

Determining Index Browning

The number and length of deleted leaf nodes to the total leaf node length and number are important ratios to be considered. These can be calculated as:

% of Leaf Node Length is deleted =

del_lf_rows_len / ( del_lf_rows_len + lf_rows_len )

% of Leaf Nodes are deleted =

del_lf_rows / ( del_lf_rows + lf_rows )

---

Hi Ken,

Thanks for the Info.
Can you or others please throw more light on this.