Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Disk/tspace config. for Oracle on AIX

Re: Disk/tspace config. for Oracle on AIX

From: Mark Baker <mark_at_mrb-basys.demon.co.uk>
Date: Wed, 15 Jul 1998 00:27:58 +0100
Message-ID: <900459173.6513.0.nnrp-04.c1ed77a9@news.demon.co.uk>


Steve, thanks for taking the time for such a detailed response.

Many of the things you mention are in place - separated partitions, 2 loops per adapter , parallel query, etc... but I've read conflicting (Oracle and other) documentation on many points, so your comments are most helpful.

Unfortunately Oracle 8 is not an option for a while or else we'd have jumped at it. We're restricted to 7.3.2 so can't even get the best from bitmap indexes, let alone partition tables/views. We're likely to upgrade at a less sensitive time for the business, probably spring '99 - and we need to go on a few Oracle 8 courses before then too!

Could you tell me if your testing revealed any performance differences with separated month partitions set up as the following (or similar) scenarios?

We have allocated 6 disks in particular to housing partitions for our main fact tables (summary tables, 36 months of data). We can either place them thus:

  1. Without LV striping, by specific tablespace storage definition on partition creation:

disk 1 : month 1, month 7, month 13, ... disk 2 : month 2, month 8, month 14, ... ...
disk 6 : month 6, month 12, month 18, ...

... or we can

2. stripe with the LVM across all disks on 1 (or several) logical volume(s) :

Disk 1 -6 : Months 1-36 thinly sliced in 64k chunks.

or maybe even

3. stripe with the LVM having striped 2 logical volumes

Disk 1-3 : Even months, (+indexes for odd months) Disk 2-6 : Odd months, (+indexes for even months)

and many more, there are so many combinations!

With parallel query and option 1 isn't there a certainty of there being disk contention between parallel query fetches all attempting to read from all disks? Or maybe this doesn't add up to much of an overhead anyway?

With option 2 I'm assuming many LVs rather than just 1 will help with the optimizer to break up the query in parallel with each LV treated as separate physical disks. (?)

Did you rule out mirroring, which as well as the extra data security (which we don't need for summary tables) can improve the speed of 'reads' by AIX making use of both copies, because it still lagged behind striping the data with LVM?

Once again thank you for your help.

Best regards

Mark Received on Tue Jul 14 1998 - 18:27:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US