Re: Oracle 9i tunning

Oh yeah...

80 Million rows may be nothing to Oracle but...

In the last job I had our fact table was 3 million rows and with the default Oracle 8i installation. I was constantly getting errors when executing DML SQL (It's been over a year so forgive me I am not entirely accurate with the syntax). The errors had to do with Snapshot too old, and failing to extend tablepace extents, etc...and we had a DBA with 15 years experience (mostly OLTP experience albeit). We solved these issues by tweeking the tablespaces, rollback segments,etc. From my experience with this situation, I decided to mess around with the configuration of the insallation in my test environment.

By the way, I am a one man shop, my background is in Economics not Computer Science or anything of the sort, so be kind! Most everything I know about Oracle I've picked up doing in a research and analysis capacity and also what I've gleaned from "Oracle 8i Data Warehousing" by Dodge and Gorman.

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:38:39 CDT