Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Tuning SGA

Re: Tuning SGA

From: Robert Blok <>
Date: Wed, 23 Mar 2005 15:24:16 -0500 (EST)
Message-ID: <>

Ok, now first of all in response to Paul; yes, if a lease contract is due then I wouldn't complain about new hardware. I think the question was more like "would I benefit from a new bigger machine?". In my opinion we have to start from the beginning; what's the problem?

from your response, Bryan, I read that the reason for upgrading would be twofold:
- storage demand (new year)
- more functionality (new projects)

Now, when I look at your configuration I see that the machine you bought is a Xeon. Nothing bad about it, though it's not the latest. What processors did your Dell have? ;-)

Anyway, in your mail your tell me it's a 4-way machine. I read from this it's a 2-way processor. Note that hyperthreading is not the same as a dual processor.

Moving towards a san with your data warehouse should be a good solution. Does it include backup facilities or snapshot mechanisms? Which storage is it?

Your connection to your san is through scsi I see. Do you have any idea about how much data traffic goes on around here?

You say that you have 10G left. If your system is a data warehouse and you start thinking about upgrading your storage now, I think the system does not grow very fast. What's the characteristic of your data warehouse. Is it mainly a loading system or mainly used for retrieval? If it's a loading dwh, you don't have to worry about your io (it would be too less I guess). If it is a retrieval system you might.

Now then to your database. Hit ratios are maybe not the best thing to mention around this list. It's a bit of a course method to figure out wether your database is running ok or not. You might want to look for wait events in the database (you can check on the ixora site for more information). Wait events tell you what the database is doing in a certain time. A statspack report may give you a lot of information about your system.

Last thing is the ETL (Extract/Translate/Load) layer. I assume your data warehouse is loaded with some sort of tool? Is this tool placed on the data warehouse machine or on a seperate machine? What is the load generated by this tool?

Bottom line is that the first question about your increased demand for storage would be sufficiently answered. A san is usually a better solution than local storage and I'm sure you would have calculated the size good.

The second question is a bit more complicated. For what the size of the sga concerns, I would like to point you to the information in a statspack report (by the way; it's 9i I hope?). When you look at the system itself, the performance of the entire chain of applications is based on the performance of all the components in this chain (all the different nodes). Was the cpu-load a problem, then placing more data in memory wouldn't decrease the problem, so check whether our new box then has cpu-bottleneck still.

Bryan Wells wrote:

>sorry all. one of these days ill get around to giving all the
>information to my questions in the begining thread.
>the current box is over 3 years old, Dell PowerEdge. here's a list of
>what we are moving to... this also includes a SAN implementation
>since we were less than 10G away from maxing the current disk. with
>starring into the headlights of new projects and a new fiscal year, i
>wanted to be sure we had room for growth.
>IBM x365, 2xXeon MP 2.7GHz/400MHz, 2MB, 2GB, Open Bay, 2x950W p/s, Rack
>2.7GHz/400MHz-2MB L3 Cache Xeon Processor MP
>Memory Upgrade Card
>IBM Total Storage DS4000 FC2-133 Host Bus Adapter
>Internal Storage, using RAID 1
>IBM 73.4 GB Hot-Swap U320 15 K SCSI SSL Drive
>3 YR IOR /24x7x4 (x365 - 8862)
>ServeRAID-6M Controller (128MBCache) (includes 03K9310 2m External SCSI Cable)
>reasons for uping the SGA are performace driven, yes. being a newbie,
>maybe not as much as a month ago, im trying to make sure moving
>forward we can handle new data requirements.
>On Tue, 22 Mar 2005 10:18:03 +0100, Robert Blok <> wrote:
>>Why did you move to the new machine?
>>What was the reason for upgrading?
>>Was the performance bad? Was the response time bad for users (what
>>users)? Did some batch run too slowly?
>>What was your previous machine?

Received on Wed Mar 23 2005 - 15:26:11 CST

Original text of this message