From: Don Berryman <berryman@mail.qp.gov.bc.ca>
Subject: Re: Oracle performance
Date: 1996/04/04
Message-ID: <316381F6.35E2@mail.qp.gov.bc.ca>#1/1
references: <4jvdf8$3nm$2@mhafc.production.compuserve.com>
to: mj <71760.1371@CompuServe.COM>
content-type: text/plain; charset=us-ascii
organization: Queen's Printer
mime-version: 1.0
newsgroups: comp.databases.oracle,fido7.rdbms.oracle
x-mailer: Mozilla 2.0 (Win16; I)


mj wrote:
> 
> We are running a 7.2 Database on a Sun server 1000e:
> 256 MB RAM, 2 CPU, 12x2 GB storage and one IO controller card.
> 
> I have been trying to optimize tablespace distribution across
> devices to improve performance, here are the results:
> 
> 1) All tables in one TS on one device all indexes in another TS on
> another device
>         Total TS's 2
>         Total devices 2
>         Time taken to complete three way join query: 20 seconds
> 
> 2) All tables in separate TS on separate devices and all indexes
> in separate TS's on                             separate devices.
>         Total TS's 6
>         Total Devices 6
>         Time taken to complete three way join query: 20 seconds
> 
> The only common element here is the temporary tablespace which was
> the same for both and is on one device.
> 
> I had hoped to see some big difference in performance here....?
> 
> Any ideas would be welcome..
> MJ
> 
> --
> QMICK@PIPELINE.COM

I'm no expert but I suspect that you need more IO controllers.  From your 
posting take it that you have 12 disk on one controller. I recently went 
to a Data Warehousing Talk and a Sun Tuning Expert recommended no more 
than 3 or 4 disk per controller.  The problem is that a SCSI bus is rated 
at MAXIMUM through put (if I remember correctly) 10-20 Meg/Second thus 
this would be the next bottle neck you'd run into after moving eash Table 
and Index to a seperate disk.