Re: Any Info Would Help...

From: <hilljm1974_at_gmail.com>
Date: 15 Jan 2007 13:38:52 -0800
Message-ID: <1168897130.590830.317560@11g2000cwr.googlegroups.com>

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:
> > hilljm1974_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
> > damorgan_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 --
Received on Mon Jan 15 2007 - 15:38:52 CST