Is Fragmentation Really Bad ???

Hi,

I came across this on the web

http://www.evdbt.com/MythsExtPerf.doc

This totally debunks the theory of fragmentation and the effects it has on the database.

My whole life till today I was under the impression that fragmentation is Bad and used to constantly monitor it.

Can others please give their opinions on fragmentation and its effects.

Thanks

I read another article about a year ago saying basically the same thing. It had a graph showing that performance was only adversely effected when there were hundreds or thousands of extents.

The key, however, was uniform sized extents. We've since completely revamped our object sizing in tablespaces with uniform extents (tablespace1_64k, tablespace2_512k, tablespace3_1m, etc.)

I agree completely with Ken! We use Uniform Extents and don't reorg unless we get ~1000 extents. We use size 128K, 4M and 128M extent_size tablespaces. We have seen some improvement in performance, but mostly we save because we don't have "unusable" space in our tablespaces. Everything is reused.

For a good article on this and how to implement, please see:
http://otn.oracle.com/deploy/performance/pdf/defrag.pdf or article "How to Stop Defragmenting and Start Living" at http://www.hotsos.com/catalog/

Good luck!

:)

I'd be more concerned about SPACE Utilisation and Administration TIME rather than PERFORMANCE. The more fragmented your tablesapce, the more space you'll need to store the segments it contains, and the higher the change of processes falling over therfore increase time to fix the issue.

I have a case in which I have > 2000 tables truncated and reloaded each week and their associated indexes (of course are rebuilt). Not all together mind, about 400 each week day. Therefore, truncating and recreating indexes that are different sizes can can cause the process to fail because the associated index with different sized extents live in the same tablespace. Even coalescing doesn't soemtime return enough free space to recreate the object. Therefore leaving the only optioon to expand the tablespace again.

Obviously, maintaining uniform extents is the answer, but I'm just pointing out that the issue with fragmentation is more than just a PERFOMANCE issue. It is also a SPACE and TIME factor in which additional administration is required for fragmentated tablespaces... and thereby elevating my hate of teh COMPRESS=Y optiin in Exports.

Cheers,

I have a few doubts....

1. If uniform extents is/was the solution why was this never told in oracle documentation or anywhere till recently. I am sure they all knew it all along.

2. There is no smoke without fire .. why did this myth ever start in the first place if it has no effects. It only make learning/managing oracle a nightmare.

I believe that changes to more recent versions have made the # of extents relatively a non-issue.

I'm with you .. I remember having a rule that we configure our tables and indexes to have no more than 5 extents over 2 years of use. I was deathly afraid of having a bunch of extents. It only makes sense to lessen the number of hops from extent to extent, right? That's got to have some additional overhead, right?

I've got a better technical explanation in a white paper somewhere around here. I'll dig through my stuff and post the details when I find them.

The performance impact of multiple extents is historic, not a myth. The amount of time to move from extent a to extent b is nothing today. Fifteen, twenty, twenty-five years ago that was not the case; the amount of time required to move the head and await the rotational delay could be the difference between acceptable and unacceptable.

I can tell you from experience, if you've gotten your burnt by something it's real difficult to 'put it in the past'.

FYI. Try uniform extents, it's a lot of work up front, but saves a huge amount of work later.

THere is another aspect to fragmentation.

15 years ago (when I started with Oracle) the emphasis was not on transactions but on reports. Yes, we could do transactions, but the 'paradigm' was report generation.

Large numbers of small extents (the modern tendency) gives better transaction performance, but 'worse' report performance. Small numbers of large extents give better report performance but waste a lot of time and resources for small transactions.

heres another link supporting this

http://asktom.oracle.com/pls/ask/f?p...0_P8_DISPLAYID,F4950_P8_B:730289259844,Y

Does all this mean that one day pctincrease and dictioanry managed tablespaces will be obsolete.

Thanks

They are already made 50% obsolete in 9i

and whats all the Noise about Tablespace reorganisation. If there is no fragmentation then why do we need it.

Thanks

because everyone believed fragmentation is bad for performance

because fragmentation is bad for space management

Quote:

---

Originally posted by pando

because fragmentation is bad for space management

---

and why is that

because I can have 1000 free extents of 1MB each and none of them is next to each other (i.e not contiguosly) but all my segments need 2MB extent, so what does this means?

I have 1000MB free but I cant use any of them and I have to extend my datafile thus wasting 1GB of my disk space

This is called FreeSpace fragmentation and this is why we must use uniform extent sizes. REUSING them

Tim Gorman is right.
I do not expect one extent for one segment would improve performance.
I am administering a VLDB. There is one table that has 120 Million rows. Its size is 30GB. The indexes take around 40GB disk space. After I took over this database, I closely observed inserts and deletes on the table. Previously the initial extent was defined with 1GB and next 512M. When ever space is needed, new data files were added, the next extent size was changed to less than 512M. Finally the table had multiple size extents.
The data tablespace has more than 15 data files and the index tablespace has around 27 data files.

I did reorganize the table in such a way that minimum space was wasted on each data file. The initial and next size are 160M. So one 2000M data file can hold 12 extents of 160M each.
Before I load the data, I manually created around 6 extents on each data file. The idea is: I/O would occur on all data files.
The same logic was applied on the index segment.

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.

With the tuning of SQL and I/O, I finally achieved 300% performance improvement.

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


[Edited by tamilselvan on 05-16-2002 at 05:39 PM]

Quote:

---

Originally posted by WROOS
The performance impact of multiple extents is historic, not a myth. The amount of time to move from extent a to extent b is nothing today. Fifteen, twenty, twenty-five years ago that was not the case; the amount of time required to move the head and await the rotational delay could be the difference between acceptable and unacceptable.

---

Even in an ancient history that was only a myth, nothing else. Unless of course you were using single-user, single-session database, and all the selects on that fragmented table were perfomed by doing full table scans. And even if both the above condition were actually met (not very likely, I guess), your disc head was still forced to perform many zig-zag movements because of physical filesistem datafile fragmentation, so even in this case it is simply a myth, nothing more and nothing less.

I am glad that ,here all are expert.
yes, there is no myth, but just fit with you situation.
such as transaction faced,report faced,or focus in batch job or data warehouse.

and in fact,uniform extents is not the highest SPACE Utilisation way, if you have thourands of tables, the size of tables are variable , then you may be need such 3 tablespace:

ts_smaill:contain 64k,256,512k
ts_middle:1m,4m,16m
ts_big:32m,64m,128m

There is no dening it, good segment management requires LMT's and unifom extents. A minumum of 3 Tablespace for data and three for indexes (each a different uniform extent size). It's what I try to use.

Cheers,

yes, there is no myth.
in fact, in many situation, 3 size of extent is not enough, if small table has big extent size, and it will wast space too.
we can do like this:
ts_small:64k,256k,512k
ts_middle:1m,4m,16m
ts_big:16m,32m,64m,128m

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.