Re: Does optimizer's behaviour change with this ?

spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0407020158.ae03ec1_at_posting.google.com>...
> Hi,
> Do you think the # of CPUs on a server is a variable with which
> the optimizer may play ? We've migrated a DB from 8.1.7.1 on
> a 4-CPU machine to 64-bit 8.1.7.4 on an 8-CPU box, and for
> exactly the same activity on this database we're experiencing
> A LOT more CPU usage: do you think the optimizer may iterate
> more to find the best path of a statement since it's found it's
> got more CPUs at its disposal, and thus require more processing ??
> I'm talking about SunOS 5.8 here.

Is it the SGA much larger? You may be running into the "must do more work in the SGA because some data constructs are now much larger" issue.

And of course, what else have you changed? Are you using OPS?

I've seen this sort of thing happen when upgrading, simply because better I/O lets more things become cpu-bound. I've also seen it happen because tuning has not been done for the new configuration, and the cpu has to work harder to deal with resulting issues (see threads about LIO and latch waits and redo buffer and flushing dirty buffers and etc and etc.) If the cpu's are not 100% all the time, there may not even be a problem - are things running faster?

This is an interesting thread:
http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&th=427f965f44cd05d8&rnum=15

I don't think it's the optimizer, but you probably want to run the usual tools to decide that.

jg

--
@home.com is bogus.
"Both companies declared victory after the rulings."
http://www.signonsandiego.com/uniontrib/20040702/news_1b2qcom.html