Re: your experiences to cpu-consumption on multi-cpu-hardware

Billy Verreynne <vslabs_at_onwe.co.za> wrote in message news:<bdonvj$lhr$1_at_ctb-nnrp2.saix.net>...
> Richard Foote wrote:
>
> > You want each "process" (in it's general definition) to be as efficient as
> > possible and to use as little resources, including CPU as possible.
>
> Beg to differ a tad Richard. In the db environment the bottleneck almost
> always turn out to be i/o. It is very frustrating to run on something like
> a cluster will all node CPU's showing a 99% waiting for i/o.
>
> In fact, as a rule of thumb, it is difficult to get a 90+ % CPU load when
> dealing with the normal type of db processing (data loads, batch
> processing, inserts, updates & deletes). The slowest operation is I/O and
> invariably the process needs to wait for the data to arrive (which means
> the CPU has to wait for i/o).

I sortakinda wanta disagree with you, unfortunately most of my recent experience is unix. But anyways, I've seen lots of situations where the cpu load is very high, unexpectedly, until you check to see what is going on - lots of SGA buffer scans may not be a bad thing compared to i/o, latch waits may be a bad thing. Sometimes fixing an errant bit of SQL blows a whole bunch of i/o to cpu, but for a much shorter time. It _should_ be i/o as the limiting factor, but Oracle empirically shows that having a complex algorithm can change the limits enormously, for the better if proper tuning is employed and the OS can get out of the way.

The key to remember (and I'm saying this for the OP, the rest of you know this so well you forget to say it) is that tuning is an iterative process. But you want it to be iterative, not balloon-squeezing!

>
> How does one then deal with this? There are two basic ways IMO.
> - make sure that there are no real disk i/o bottlenecks that can
> be dealt with via a better hardware & software configuration
> - divide and conquer the data (i.e. parallelise and process)
>
> If a single SQL causes a big CPU load, then that query needs to be carefully
> looked at.. 'cause somewhere along the line you are using very CPU
> intensive functions. In that case it makes sense as to what you said - you
> want to tune that query to resources as effectively as possible (which
> includes the load this process puts on the CPU). But this in my experience
> is pretty much the exception.

If the big CPU load is on just one of several identical machines, the likeliest contenders are improper configuration or data loading.

>
> > However it you have a whole bunch of processes each wanting to use CPUs,
> > well that's kinda why they're there and while nothing is unnecessarily
> > waiting for the resource, which is the key point, you have an environment
> > that can cope with the required demand.
>
> Exactly. Personally, I have yet to see an Oracle setup where there are not
> sufficient CPU power. The majority of times it is a case of too much CPU
> power and not big enough pipes to disks. Like having 3 fibreoptic channels
> to an EMC array and causing it to be throttled by a mere CPU load of 50% on
> a single CPU (and this on a 6 CPU SMP platform).

I have, but then I sometimes work with HP boat anchors configured and admin'd by MS certs.

>
> The real "trick" IMO is not how load your CPU's, but how to address the
> problem of getting the data off and on the disks. From the physical
> configuration of the disks, to the number of DB Writers, using IO slaves,
> and issues like log switches.

I agree, mostly, but would direct the OP to the Oracle Tuning Methodology (which I don't entirely agree with, but it's a good starting point). This link might work for starters: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=131759.1

jg

--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/tue/news/news_1n1peregrine.html