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

joel-garry_at_home.com (Joel Garry) wrote
> >
> > 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.

Why unfortunate? :-)

Give me ksh anytime and everytimg instead of cmd.exe.

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

Interesting. Is that in the OLTP or batch processing environment? Personally I would think such high CPU usage to be the exception. As I mentioned above with my rule of thumb, is that _normal_ (or standard) OLTP/batch processing deals with data processing. With i/o becomming a problem as dealing with data processing means loads of i/o, oppose to doing CPU intensive number crunching.

As you said, rather heavy buffer cache usage and the CPU bearing the burden than to have the i/o channels trying it instead.

> Sometimes fixing an errant
> bit of SQL blows a whole bunch of i/o to cpu, but for a much shorter
> time.

I would not be so kind as to call that an "errant" SQL... I trusted that you dealt swiftfully and inflicted sufficient pain (with a lead pipe I hope) on the party responsible for that SQL? ;-)

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

Not sure what you're hinting at there... :-)

i/o is IMO *the* limiting factor. Even when having fibre running into an EMC array. There is an inherit latency in every i/o call - and this adds up to a CPU (or CPUs) waiting on i/o.

Unless you are refering to non-standard OLTP processing - meaning that this is not the normal kind of insert,update,delete and select, but also has Oracle doing complexing business logic, data transformations, data manipulations and the like. In that case, common sense goes a long way, but as you probably well know, that is lost on more than a few developers. Like putting transformation functions on a column instead of an index. Or coding guff in a loop making it fat and bloated.

> 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!

.. while playing the themesong of Top Gun, making grunting noises  and wearing shades.

Hey, there's nothing against looking kewl while turning. ;-)

--
Billy