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

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).

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.

> 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).

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.

--
Billy