Tablespaces/Datafiles Management - Better Practices

[rsuri]

Hi All,
We have a 500GB Oracle database on HP-UX 11
currently it's on EMC disk array with RAID 1 mirroring (single controller)
In a few weeks we plan to move to a SAN device with RAID 5 (probably 3 controllers)
During/after this period I plan to reorg my tablespaces and datafiles to practice what is thought to be a better design in DBASupport.com community.
This system was migrated from legacy and has seen times of earlier versions of Orcale 7.x and 8.0; currently it's on 8.1.7
Currently some of the tablespaces are as big as 90GB (tables underneath are upto 20GB)
Most of the datafiles are 2GB (because large files option was not set on unix) so in all there are about 250 datafiles.

My questions to you -

- should I try to set the tablespace size to a smaller number?
- should I expand datafiles to 5GB or even more? the new system has an array of 7GB disks

Thanks,
- Rajeev

ps. most tablespaces are LMT

[slimdave]

RAID 5 eh? Go to www.oraperf.com, register, and download the white paper on I/O tuning.

Best practice is to isolate big tables into their own tablespaces - smaller tables/indexes can be divided into categories by size and assigned to TS's appropriately. Small, Medium and Large Segment Tablespaces can hold both tables and indexes at once -- no need to seperate them.

You data files could easily afford to grow up to 4Gb.

Migrate to LMT for all TS's (except SYSTEM until you are at 9i), using uniform extents, as soon as possible.

I'm sure there are other things to add. anyone?

[Mr.Hanky]

Hmmm
On one hand I'd say sure take full advantage of newer technologies.
On the other hand if it ain't broke don't fix it.

Have you been able to simulate/test any of this in development?


MH

[marist89]

Would it be good to move things around? Sure. If you can convert to LMT, then go for it.

However, you could be setting yourself up to be the fall guy. If there are any performance problems after the move, all fingers will be pointing at you because you futzed th the tables and indexes. Personally, I would do it gradually:

1. Move everything to RAID 5 on the SAN.
2. The next weekend, move 1/3 of your tables and indexes to LMT's (system managed where appropriate).
3. repeat 2 the next weekend
4. repeat 2 the next weekend

Also, measure your I/O performance before and after each step. This way you can prove/disprove improvements.

[slimdave]

I have a feeling than RAID5 is going to take a big bite out of your I/O potential, so Jeff' advice seems v. prudent. So many changes at once, you won't know where to point your finger if something goes pear-shaped.

[stecal]

And be sure to involve other people in the disk reorganization. That way, there are other people to point fingers at. Strength in numbers. Think of swimming with sharks.

http://www.marquitta.com/Sharks%20and%20Suits.html

[slimdave]

To add to Steve's insightful comment -- If possible, introduce a deliberate performance damper on the system at the time of migration. Preferably it'll be something that others wouldn't spot, but it'll allow you to step in and boost performance by 200% whenver you feel like it, thus making you Hero Of The Hour, and Employee Of The Nanosecond.

[slimdave]

An excellent choice by the way would be to introduce DBMS_LOCK.SLEEP(5) into a key procedure. If the code is wrapped no-one will be able to easily tell, and when you remove it you can say you simply removed some of the inefficient developers' code and replaced it with your own. Double Kudos!!!