Re: Oracle 9i tunning

From: sg <s4v4g3_at_europe.com>
Date: Mon, 24 Jun 2002 23:07:32 +0200
Message-ID: <3D178A14.8050505@europe.com>

I tested it in Oracle 8.1.7, Tru64 5.0. I did the test when I had to rebuild around 120 indexes in two databases (same database but different for different purpose). Tested one with 100MB sort_area_size and the other with 50MB and the time was not much difference, one took around 6 hours, the other 6 hours 20 minutes or so

Howard J. Rogers wrote:

> I'd be interested in the details of those tests. Even operating system and
> version would be a start.
> 
> Regards
> HJR
> 
> "sg" <s4v4g3_at_europe.com> wrote in message
> news:3D1775F6.6070604_at_europe.com...
>

>>hi
>>
>>dont set sort_area_size too big, I have tested and setting it higher
>>than 50MB does NOT get you extra benefits
>>
>>Parallelism normally 2xCPU does fine for me, may be you can try it
>>
>>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

>>>

>>>

>>>

>>>

>>>

>>>

>>>

>>>

>>>
>>>

>
> Received on Mon Jun 24 2002 - 16:07:32 CDT