Re: oracle 9i r4 on win2000 pro with 8GB

"Matthias Wirtz" <Matthias.Wirtz_at_abbrevation.com> wrote in message news:2n7h0cFt909hU1_at_uni-berlin.de...
> Howard J. Rogers wrote:
>
> >> I have a big box (Intel - win200) with 8GB of memory. I thought having
a
> >> total SGA (oracle 9i r4) of 4 - 5 GB
> >
> > Why?
> >
> > I'll lay odds you don't need such a large SGA. Most people don't.
>
> Most of it would be used as db_cache in order to minimize hard drive
access

Oh, sheesh. That old chestnut. Don't even think about it. Why do you think Oracle invented the buffer cache, the LRU list, the checkpoint queue and all that other good cache management stuff in the first place, if the answer to everyone's problems was simply to load the entire database into memory and keep it there?

Think about it: instead of spending tens of thousands of dollars on expensive consultant's fees, I could simply spend a few thousand dollars on extra RAM, and have all my performance tuning problems resolved by the extra hardware at a stroke! If that was true, don't you think the entire industry would have gone that route a long, long time ago? Which is a clue that it doesn't work like that, and what you are aiming to do will cause more problems than you can shake a stick at.

Size your buffer cache appropriately. Do not go beserk thinking you are somehow going to eliminate physical I/O, because you won't. And bear in mind that caches of whatever size need internal management, and that the bigger your cache gets, the more the administrative overhead gets... and hence a huge cache will merely *cause* waits and other performance penalties, not cure them.

> and to have space for even big sort operations usually performed in the
> temporary tablespace.

Sorts are done in the PGA (well, OK. They're actually done in the UGA component of the PGA). In dedicated server, the PGA is not a part of the SGA, and hence having a huge SGA is not going to affect your sort performance one way or another. Given that you have 9i, you are presumably using pga_aggregate_target: in which case, set that to something appropriate and thoughtful which maximises optimal sorts, and minimises multi-pass sorts, and leave it at that.

In short, I am sorry to say, there is no hardware-related shortcuts to tuning an Oracle instance properly the long-winded and hard way.

Regards
HJR Received on Mon Aug 02 2004 - 14:14:45 CDT