Re: Oracle 9i tunning

I don't know why I would construe your comments as harsh -- geez did I strike a nerve or what?

I really would rather not go with SQL server because all my *development* (not DBA as you so eloquently pointed out) experience is on Oracle and I am familiar with Stored Procs/ PL SQL/ functions and I simply prefer Oracle's flavor of SQL in general...and I particularly hate Microsoft's flavor of SQL (try building a complex outer join in anything but GUI). I also like the some of Oracle's innovations such as Materialized views etc (did they get this working better in 9i)?

But if DBA activities are more automated in SQL server then I may be forced to right?

Joe

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3D17428F.ABEA227F_at_exesolutions.com...
> 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:22:32 CDT