Re: Disk/tspace config. for Oracle on AIX

On Tue, 14 Jul 1998 21:48:29 +0100, "Mark Baker" <mark_at_mrb-basys.demon.co.uk> wrote:

>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
>
>
>
>
>

24 disks isn't too bad.For datawarehouses it might help to increase the number of disk/raid controllers.

Meinolf Received on Wed Jul 15 1998 - 03:23:22 CDT