Disk/tspace config. for Oracle on AIX

I'm trying to determine the best set up for a data warehouse application: Using parallel query, summary tables, denormalisation of keys onto fact tables, partition views, etc. for Oracle 7.2.3.2.0 on AIX 4.2

The machine is pretty much dedicated to the data warehouse and I have 24 disks available.

I've pretty much ruled out using raw devices as I believe each successive version of AIX has cut away most/all of the performance benefits they gave, but am unsure as to the best combination of mirroring, striping and 'single disk'-ing to use.

For example, taking the TEMP tablespace I've tried various combinations of (say) 4 Dedicated physical disks for temp: 1. OS striped - 2 logical volumes with 2 disks to each with128k stripe size for both.
2. OS striped - 1 logical volume of 4 disks with 128k stripe size. 3. 'Oracle striped' - 4 logical volumes, one per disk with a couple of datafiles on each disk belonging to temp.

I tried a query on an isolated large partition view spanning 3 partitions with a nasty group by and order by. The partitioning is by month, with around half a million rows per partition: Results (in tkprof) came out much the same regardless, even when I tested temp made up as a single disk with 4 datafiles on it! I've tried to perform my tests with as little other activity on the machine as possible - there has been some sporadic work going on making comparisons difficult, but I was still expecting (hoping for) a clear winner. (The temp disks and partition view tables are all on their own isolated disks, so it should only be CPU power being drained by other users.)

Anyone performed any testing in this area?

As for the partition tables and their indexes - I'm about to try various disk config. scenarios with them, but any help to cut down this (so far inconclusive) testing will be very greatfully received!

Regards

Mark Received on Tue Jul 14 1998 - 15:48:29 CDT