FSFI is low in RBS and TEMP tablespaces

[mybear]

My FSFI is very low in my temp and RBS(roll back) tablespaces. Can any body tell me the best
way to defrag those tablespaces?

With the tablespaces containing my data tables, I use export and import and that takes care of the problem. But with TEMP and RBS tablespaces, I don't know which way to go.

[pando]

you dont defragment those tablespaces, by nature they are fragmented, just leave as they are

[JDoyle]

While rollback tablespaces do naturally become fragmented by the growth and shrinkage of individual rollback segments, this effect can be lessened by creating each rollback segment with a proper setting for Min_extents. The default during database creation is 2 and this is way too few for maintaining "contigousity". The standard formula is set minextents so that the sum of all extents in the rollback tablespace equals the expected number of users. Put another way, divide the number of users by the number of rollback segments to get minextents. That way, theoretically, each user could be assigned to its own extent without having too much dynamic allocation.
Jurij Modic, who is very knowledgeable, has recently argued in this forum that fragmentation is way overdone as a concern for DBAs and I think he's probably right. It's a very interesting idea. Still, it seems to me that some time is consumed in having the disk head find the next extent when it's somewhere else on the disk.
As to your temporary tablespace, if it's set as type temporary it can contain no permanent objects and fragmentation is a nonissue. Select tablespace_name, contents from dba_tablespaces; Also your temporary tablespace should have all equal sized extents (initial=next, and pctincrease=0).

[pando]

If fragmentation wont affect perfomance I dont know why we have defragmentation tools for operating systems which is same idea in Oracle.
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.

However as I have said once for some time in this forum even you see your extents are uniformely distributed contiguosly in tablespace manager but in OS how do you know that the extents are contiguos, probably they are not contiguos at all in the disks. May be this is what jmodic is suggesting?

[jmodic]

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,

[Sweetie]

OK i am convinced by you too about this, that Oracle uses indexes to access data so basically the head would move all the time anyway since it has to access index segment first then from rowid to table segment to retrive data then back again to index and then again table and so on.
Our DBA here is wrong then

[Edited by Sweetie on 02-05-2001 at 07:02 AM]