Re: Oracle 9i tunning

Joe wrote:

> Hi,
>
> Environment: Oracle 9i running on Windows 2000.
> Hardware: Dual AMD, 2 Gig Ram, RAID 5 disk array (ATA/100)
> Application: Datawarehousing
>
> I am process of evaluating Oracle 9i and SQL server for a datawarehouse
> application and have some questions on how to tune 9i to maximize
> performance on index building and sorting in general.
>
> There is a large fact table (80 million rows) that I am testing in both
> environments (SQL and Oracle). I am attempting to build an index on the
> table want to insure that I have things set up so that Oracle will use as
> much memory and processor as possible. When building indexes Oracle is
> using about 400MB or the 2 Gigs of RAM and only uses 10%-15% of one
> processsor and 0% of the other.
>
> Here is what I have done so far.
> - Created a temporary 4GB tablespace and set it to default for the user
> account I use.
> - increased the sort_area_size for the session to 1GB.
> - specify nologging and paralell 2 in the create index DDLs
>
> Is this the best I can do to maximize sort procesess?
>
> Joe

I hope I am misunderstanding what you have written. Please tell me that is so.

You assigned a 4GB temporary tablespace as the default for a user?

Based on this and the above statements I think your impact somewhere between neutral and call 911 for an ambulance.

80 million rows for Oracle is nothing. I have databases that add more rows than that in a week. You are spending time trying to optimize something that likely doesn't require any optimization whatsoever. An index is something you build once and perhaps rebuild from time-to-time but likely, in most systems, will be ignored for a year or forever.

My advice is: If it ain't broke ... don't fix it.

Note: The following is not intended to be as harsh as it may sound but rather to convey a sense of forboding!
From my perspective you will likely decide that SQL Server is the better product because it appears that the assignment to evaluate Oracle has been given to someone that doesn't understand the product well enough to not make it work as well as a stack of 3x5 cards. Please find someone with at least three to five years of Oracle knowledge to help you set this up. And if you don't, please don't blame Oracle Corp. and it software for the almost inevitable result.

Daniel Morgan Received on Mon Jun 24 2002 - 11:02:47 CDT