Re: Oracle 9i tunning

hmmm...several months ago I made some tests with 8.1.7.3 on NT4, Intel box with two PIII 1GHz and 1GB memory. Build several indexes on a 74.000.000 rows table. It was pretty cool to *hear* the sorting:

SORT_AREA_SIZE = 2M

rrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrrr

SORT_AREA_SIZE = 10M rrrrrrrrrrrrr.............rrrrrrrrrrrrr.............rrrrrrrrrrrrr.............rrrrrrrrrrrrr.............

SORT_AREA_SIZE = 100M

..................................................................................................

:-)  

On Tue, 25 Jun 2002 06:30:01 GMT, Herman de Boer <h.de.boer_at_itcg.nl> wrote:

>Hello,
>
>please note that the oracle internal sorting routines in 9i have been
>rewritten. Before 9i, for very large sorts, I personally never set
>sort_area_size higher than 2Mb - agree with sg. But, in 9i, more
>memory *does* shorten the sorting time.
>
>
>Kind Regards,
>
>Herman de Boer
>sr consultant
>IT Consultancy Group bv.
>
>sg wrote:
>
>>hi
>>
>>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
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>
>>>
>>>
>>

Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Tue Jun 25 2002 - 14:14:56 CDT