Re: Oracle 9i tunning

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
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>
>>
>
Received on Tue Jun 25 2002 - 01:30:01 CDT