Re: Any Info Would Help...

On Jan 15, 4:38 pm, "hilljm1..._at_gmail.com" <hilljm1..._at_gmail.com> wrote:
> Sorry, I guess the post was a bit confusing.
>
> I am not having any problems loading records into the tables. The
> issue is the response time when a user issues a query via the ad hoc
> engine. The table is denormalized (this is a data warehouse
> environment) and highly indexed. However, during testing, as I
> approach 13+ million records the query performance is diminished.
>
> Currently we are running dual Xeon 3GHz processors with 16 GB Ram with
> 1 mirrored pair of 160 GB 10K RPM HDD.
>
> Future hardware will be dual Dual-Core 3GHz processors with 24 GB Ram
> with 4 mirrored pairs of 300 GB 10K RPM HDDs.
>
> How would you define your tablespace, etc. to maxmize the DB server's
> performance?
>
> Thanks!
>
>
>
> Mark D Powell wrote:
> > DA Morgan wrote:
> > > hilljm1..._at_gmail.com wrote:
> > > > Hello,
>
> > > > Currently the company I work for is running an Oracle 10gR2 SE1
> > > > database server (using Linux OS) for an inhouse developed ad-hoc
> > > > querying system adding roughly 1 million records / year.
>
> > > > They want to scale it up to handle 5 - 10 million records / year.
>
> > > > While testing to see if the current system design can handle this, I
> > > > have found a lot of performance degradation in the response time from
> > > > Oracle. They have agreed to change the hardware platform, but we are
> > > > still limited to using SE1 only.
>
> > > > Anyone have any suggestions on hardware selection to handle this kind
> > > > of data load with fast query response times (fast meaning under 2
> > > > minutes)?
>
> > > > Any advise on how to arrange tablespace, etc. to tune this system would
> > > > be a great benefit too, since SE1 does not give you any real tools
> > > > (partitioning, bit indexing, diagnostic pack, tuning pack, etc).
>
> > > > Thanks in advance!
>
> > > > Jeremy
>
> > > Based on what you posted I wouldn't spend $1 on new hardware.
>
> > > I can insert 100,000 records/second on my IBM ThinkPad with its
> > > miserable 7500 RPM drive.
>
> > > Far more information is required to make a recommendation.
> > > --
> > > Daniel A. Morgan
> > > University of Washington
> > > damor..._at_x.washington.edu
> > > (replace x with u to respond)
> > > Puget Sound Oracle Users Group
> > >www.psoug.org
>
> > Jeremy, I would suggest that you pay attention to the disk farm. I
> > have found that I get better response time when I can spread the data
> > out over several striped volumes. If you have an unstriped disk setup
> > you may find that a faster processor or more memory does not help
> > anywhere near as much as you expected.
>
> > Daniel is correct in stating that more detail of how the application
> > works and the current hardware: number of cpu's, processor speed, and
> > memory is also needed to be able to make reasonable guesses as to what
> > will work.
>
> > IMHO -- Mark D Powell --- Hide quoted text -- Show quoted text -

Sorry for the late respose but I missed seeing the update. I would stripe the disk accross more than 2 disks. Disks are relatively cheap.  Try to spread the data out over several drives if you can.

Mirrowing in sets is good for safeguarding the data from disk failure but it does not do anything to help performance unless the data is stripped accross multiple disks. Mirrow the disks then stripe: RAID-1 + RAID-0 or RAID-10.

HTH -- Mark D Powell -- Received on Thu Jan 18 2007 - 09:47:53 CST