Re: Oracle 9i tunning

Hi Joe,

You seem to have had a run of bad luck with the responses you;ve received.

First, if it were that hard to use Oracle effectively, I'd be out of a job teaching people to do it. It's not hard, the responses you've receoved are atypical, and you're on the right lines.

Bumping up sort_area_size to extremely large levels within the session is a good move, actually, because nobody gets ANY sort_area_size until they actually use it, at which point it grows up to the maximum specified by that parameter. At the end of the sort, it shrinks right back down to zero. Unfortunately, it's the case that the memory is often not returned back to the operating system, merely freed within the memory space allocated to Oracle. I haven't actually tested it on Windows... but it would be easy enough to do. Call up Task Manager, and look at the 'Performance' tab as the sort proceeds. If the memory is going to be released back to the O/S, you'll see the graph rise and then drop back. Also, though, keep an eye on how much paging Windows starts doing. If your huge sort_area_size induces extra paging, then that's a BAD thing!!

1b might be a bit ambitious... but test it and see. If it comes back to the box after the sort, you've done the right thing. If it doesn't, re-think.

Bump up your degree of parallelism, too. I was testing a 24-processor solaris box the other day. Got my degree of parallelism up to 3000!! That's going way over the top, though. On a dual-processor box, try bumping it up to 4 or 8. Should be perfectly acceptable.

Nologging is fine for create index.

RAID 5 isn't. But I guess you can't do much about that! (And it's not the total and utter evil it's made out to be, either).

You are, in short, on the right track. A bit more parallelism, and go easy with sort_area_size in respect of possible additional paging. Otherwise, I can't think of an awful lot more than you can do.

And don't use SQL Server. Oracle is just fine and dandy!!

Regards
HJR "Joe" <joegenshlea_at_attbi.com> wrote in message news:d9HR8.139197$nZ3.58364_at_rwcrnsc53...
> 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 - 14:31:46 CDT