Re: How to "Defragment" Tablespaces ?

I have to jump into this discussion. In early releases of Oracle, it was taught to "DEFRAG" your tablespace if you got over 4 or 5 extents. This was drilled into DBA's and others. That is just not the case now, but others still beleive this to be true and are always asking that you "DEFRAG" the database. In some cases you may need to do it, but it is no longer a blanket statement. It really depends on your application. If your SQL statements are doing FULL TABLE SCANS, then you need to do it. If you have a tuned database that correctly uses indexes, then it is really not a problem.

The tables and indexes in different tablespaces and data files just makes good sense and does have potential benefit. RAID can be good for you and it can be bad. I am not up to date on all of the RAID options avaiable, but if I had to use it , I would just mirror everything for data protection. In this case you really want them seperate to handle load balancing of I/O. In the RAID that does the stripping for you, you can do ok reading data, but if you have heavy writes, it will slow you down.

--
John Jones
Senior Oracle DBA
Duke University, OIT
john.jones_at_duke.edu
Brian Peasland <peasland_at_usgs.gov> wrote in message
news:3ACDCEED.A7621F58_at_usgs.gov...


> > Okay, finish the paragraph. Why is it a good idea? Is there a

 performance or


> > scalability reason you can think of? Putting indexes and tables in the

 same


> > tablespace is not "getting away" with anything. Presumably this is a

 scaled


> > application and we are not really talking about discrete drives

 anyway...


> > stripe and mirror everything.

> >

> > There was this misplaced notion that started years ago, before raid

 systems


> > became popular, that tables and corresponding indexes had these terrible

> > concurrency conflicts and had to be kept apart. Well, it never was

 really true


> > but the DBA myth persists. If, on the other hand, you manage objects by

 extent


> > size and have a range of standardized tablespaces, I/O balancing becomes

> > trivial. Set it up for manageability first, tune later.

>

> My "misplaced notion" is due to the fact that not every installation of

> Oracle is done on RAID systems. To me, this is your misplaced notion.

> True, if all datafiles were on RAID that striped the data across

> multiple volumes, then there is a really good chance that your indexes

> and your tables will be on different volumes thus increasing performance

> and scalability. This is not a myth, but sound theory and database

> design employed by DBAs worldwide.

>

> The only true way of guaranteeing that indexes and tables are located on

> different volumes is to ensure that they are in different datafiles.

> This can only be accomplished by placing them in different tablespaces.

>

> While everyone can agree that it is best to stripe and mirror

> everything, this ideal situation does not always occur in practice.

> Constrained budgets often lead to comprimises and one of the things that

> often goes out the window is the extra disk that is required to mirror

> data.

>

> Now the onus is on you... In the absence of striping and mirroring, how

> do you propose to improve your performance and scalabilty while placing

> indexes and tables in the same tablespace?

>

> Just my 3.14159265 cents worth,

> Brian

>

>

> --

> ========================================

> Brian Peasland

> Raytheons Systems at

>   USGS EROS Data Center

> These opinions are my own and do not

> necessarily reflect the opinions of my

> company!

> ========================================