On May 11, 8:00*am, Pat <pat.ca...@service-now.com> wrote:
> On May 10, 8:00 am, "Mike Jones" <mj20...@hotmail.com> wrote:
>
>
>
>
>
> > "Pat" <pat.ca...@service-now.com> wrote in message
>
> >news:c3b1b601-abd9-4d2c-acaa-e4d947b44bc5@d77g2000hsb.googlegroups.com...
>
> > > The question I have is, is there any downside to me buying, say, a 32G
> > > box and setting the SGA size at 20G? Will I actually end up harming my
> > > performance with an over-large SGA (assuming I have enough physical
> > > memory to keep the box out of swap)?
>
> > I am in agreement with bhonaker. I do not see any issues with doing this..
> > However, as others have indicated, this may not solve your problem. But I
> > don't see it causing harm. Is it possible you could comment on the
> > configuration of your current system as well as the configuration of the
> > replacement system?
>
> > Mike
>
> Current system is:
>
> 4 X Intel(R) Xeon(TM) CPU 2.80GHz (8 cores)
> 8 G RAM
> 1.1 TB of storage (SCSI RAID 10 array, direct attached)
> Red Hat AS4, 32 bit
>
> Oracle 10.2.0.3 (32 bit)
> 2.3 G SGA
>
> Replacement system is:
>
> 2 X Intel(R) Xeon(R) CPU * * * * * E5310 *@ 1.60GHz (8 cores)
> 16 G RAM
> 1.6 TB of storage (SCSI RAID !0 array, direct attached)
> Red Hat
>
> Oracle 10.2.0.3 (64 bit)
> 12 G SGA

I pretty much agree with the others in this thread, though I have a
quibble with a statement in David's first post:
"> > A smarter solution is to add an index to reduce I/O by orders of
magnitude.

Possibly. It's also possible that this is a highly transactional
configuration, effecting large volumes of data changes. An index
won't help there"

What I've seen in most transactional configurations is that indices
help a lot. What often winds up happening is certain objects thrash
the SGA, so Oracle spends a lot of time doing housekeeping on the
buffers there, as well as all the other things David mentioned. What
I've seen on my systems is that there are usually just a few objects
responsible for most of the thrash (which makes sense, since most
activity is the mission critical app), and separating them into their
own cache (particularly a recycle cache) helps enormously. See the
performance tuning manual for the basics about looking at V$BH, and
google about for other commentary about it. But definitely don't
listen to anybody who advocates using tablespaces with different block
sizes for performance.

If you look at what is happening on your current configuration, I'd
expect you'll see there's not all that much size involved in the
candidates for recycling. Then when you look at the total PGA usage
you'll see how much your users really need. Check out your advisor
views.

The more modern Oracle handle large SGA better than in the olden days,
so using an oversize SGA isn't really the big deal it was in the past,
as long as you aren't way stupid in something.


>
> I've got the hardware budget left to bump up the memory on the new
> system a bit if that'll help and I'm tempted to do it (hence this
> thread).

My experience indicates it is best to grab what you can when you can,
YMMV.

>
> One totally off topic question I do have though is, does anybody have
> experience with the new 4 core line of intel chips? My operations guys
> are procuring these now instead of the old dual core Xeons, but they
> make me nervous since the new ones report lower bogomips than the old
> ones. I'm aware that the "bogo" in bogomips stands for bogus, so I'm
> totally wiling to be convinced the new chips are, in fact, faster, but
> I'd love to hear any real world experience here.

Tuning becomes complicated because it involves twiddling many
different bottlenecks, so it is entirely possible to make things worse
in an unexpected way. For example, making the SGA larger could take
the bottleneck away from I/O and put it onto cpu, suddenly making
bogomips important, and making everything slower in both
configurations, even more so in the "faster" one. That's when it is
important to already understand methodologies like Cary Milsap
publishes. I'm not disagreeing with Mladen, in fact I agree with what
he posted, but I'm pointing out that lots of factors are involved that
can make a specific situation "interesting."

jg
--
@home.com is bogus.
"...the Internet is the best way to disseminate accurate
information..." - Mark Cogan, attorney for alleged baby killers.
http://www.worthingtondefense.info

"I've got the hardware budget left to bump up the memory on the new
system a bit if that'll help and I'm tempted to do it (hence this
thread). "

This seems as an uninformed attempt to solve a problem. If it doesn't
harm - it might work.

Resist your temptation to this "checklist approach" if you seek
insight in the problem. Adopt a methodology as Cary Milsap provides.
Analyse before implementing a solution.

But the temptation to buy more hardware is strong. I remember a
situation where a new and critical application was performing very
badly. The CEO personally ordered a new 8 CPU machine with more RAM to
push performance in order to buy time for the programmers to tune the
application.
The old machine was a 1 CPU machine. There was an increase of
performance between 0 to 2 % (if i remember well) , instead of the
800% the CEO expected.

So if you want to try the RAM scenario, do as you please. But please
measure your performance in response times, and not some hitratio. And
then have a look at the difference in performance.

H.