Pando (at al),

I suggest everyone to read Cary V Millsap's whitepaper on this subjects "Oracle7 Server Space Management", particulary Chapter 7, where he explains why fragmentation is not an isue at all. BTW, C.V.Millsap is Oracle insider and one of the top performance specialists and one of the most clever guys when it comes to Oracle RDBMS. I warmlly recommend any whitepaper on the subject "Oracle" signed by Millsap! You can find this (and some other papers) at the following URL: [url]http://www.visioncg.com/whitepapers.htm[/url]

In short, performance-vise tablespace fragmentation can have (theoretical) impact only in one situation: you are performing a full tables scan (FTS) or fast-full-index scan (FFIS) and you are the only active user on the system. And even in this situation, the high number of extents have no impact on performance if the extents are sized correctly. As soon as you are accesing table data through indexes, the segment fragmentation becomes totally irrelevant! (Now tell me how often does your application perform a FTS on a very large table that you keep defragmenting?)

[QUOTE][i]Originally posted by pando [/i]
[B]If fragmentation wont affect perfomance I dont know why we have defragmentation tools for operating systems which is same idea in Oracle. [/B][/QUOTE]
Because in OS you read files *sequentially*, you don't have indexes there! In RDBMS, the vast majority of data access is through indexes, so disk hads constantly have to move randomly, no matter if your data is packed in one large extent or bunch of smaller extents. Remember, only at sequential reads there can theoreticaly be extra head movement caused by not-single-extent organization of a table.

[QUOTE][i]Originally posted by pando [/i]
Also if fragmentation doesnt not affect the perfomance I dont know why everytime I defrag the disks I get better perfomance and again I consider same idea applies in Oracle. [/B][/QUOTE]
This is one of the main reasons why this "Oracle fragmentation mith" is still very allive. Usualy the "proof" how defragmentation improves the performance is this:
1. perform some benchmarks on table with many extents
2. do export with COMPRESS=Y and do an import, the table is now in single extent
3. perform those benchmarks again, response time is much better
4. conclusion: defragmenting the table into one single extent improves performace (QUED)

The problem with this proof is that compressing the table into single extent has nothing to do with the improvement! If in step 2. you use COMPRESS=N, your table will still be fragmented in many extents, but you'l get the same performance improvement!!! The reason for improvement is not segment defragmentation, but the *block* defragmentation. After reorg your data is more densly packed in blocks and this is the only cause for performance improvement, number of extents is totaly irrelevant.

HTH,