Re: help: bottleneck because Oracle db exists ob only one hd

I hit this all the time. The problem is that if you have a small application (under 10 GB), it may not make much difference if you use one or several disks. Practically speaking you need to balance your time investment versus the sort of performance gain you expect to achieve. I have some large applications that use serveral disks, but are all part of one large disk array . The true bottleneck is the channel between the computer and the disk..

Let's see, if you have three disks, say, 18 GB each: Disk1
Operating System
Oracle Software
Control1 file
Redo Logs

Disk2
Data tablespaces
Control2 file
rollback TS1

Disk3
Index Tablespaces
Control3 file
rollback TS2

You might be better off with 15 4GB disks!!! But with a lot of disks:

Disk1:
Operating system

Disk2:
Oracle bin, init, alert log, etc.
Control file1

Disk3:
Control file2
Redo Log Group 1 -- Here is where it looks like you're "wasting" space. You need to balance the size of your redo logs with performance. If you want a switch to occur every 20 minutes or so, you might only have 20-200 MB of redo log. That looks mighty small on a 9 GB disk. You would be tempted to combine disks 3&4 here with 5&6. Your call.

Disk4:
Control file3
Redo Log Group 2

Disk5:
Tables

Disk 6:
Indexes

Disk 7:
Rollbacks -- I usually never have enough disks to put RBS on its own set of disks.

Disk 8:
TEMP tablespace (for sorting)
SYSTEM -- if there is heavy sorting, there isn't much lookup in the data dictionary at the same time.

Disk 9:
Archive Logs (and spooling to tape)

Some of my rules of thumb:
1. Always have three control files. I've had one or two go bad, but never all three at once.
2. Mirror Redo Logs only if you have the disk capacity and you can afford the split. However, in a transaction system, every transaction is important, so a small hit is acceptable versus any loss of data. For a decision support system, it may be adequate to just do a log switch after the last batch load, then a back up of the whole system. In the DSS world, large data updates are done off-hours with virtually no updates during the day. You just have to monitor the alert log to find out when and how often your log switches are taking place.
3. For Unix, occastionally check iostat to make sure you have some disk i/o balancing. If you have a large disk array, it may look like one big disk anyway.
4. Redo logs have been my biggest pain in the ass lately. If they're out there on that big disk array with the data and the indexes, you will never get good upload performance.
5. Alway split data (tables) and indexes. If queries are written correctly, they should make heavy use of indexes instead of sorting tables.

As far as "how" to do this, I would recommend that you get the new database ready first. The export your old database on a schema-by-schema basis. Use the indexfile and show options on import. Create the tables and indexes in their new tablespaces where you want them. Then you can use the ignore=y to import the objects with having to worry "table already exists" message. Rearranging an existing database can be done, but for the same amount of effort, you can usually set up a new instance the right way.

Dechi <rp44905_at_online-club.de> wrote in message news:7vb3rj$fmi$1_at_news.rp-plus.de...
> hello,
> our Oracle 8 db exists on one hd. a supporter tells me that it is better
to
> put it on more
> hd's.(Probably 7 to 14) how can i do this with Oracle 8 and HP-Unix ?
> I need help very fast.
> thank you
>
>
>
>
Received on Mon Nov 01 1999 - 10:06:30 CST