Re: Any Info Would Help...

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

Before I made any changes to hardware I would run a StatsPack to determine which resource(s) are at issue. There is on reason, based on what you've posted, to indicate the new hardware will accomplish anything other than the spending of money.

I'd also suggest monitoring those indexes to see if they are doing anything other than producing additional overhead.

ALTER INDEX <index_name> MONITORING USAGE;

Then after a reasonable amount of time for usage ... SELECT * FROM v$object_usage;

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