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

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0307012209.64f46837_at_posting.google.com>...
> 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? :-)

Only in that the OP was w specific, I always feel the need to cya with _anything_ w.

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

omigodyes. But at this point in time, w-bashing is counterproductive.

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

Combined environment(s). Isn't this the 80's? It'll probably be years before computer prices come down enough for separate reporting and OLTP boxes. And then in the next century, I bet Oracle will do something like have multiple blocksizes so everything can be combined again. :-)

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

I keep thinking it should be, yet I keep seeing CPU binding. Of course, I'm not on 2GHz wintel's, either (well, there's one waiting for me to finish up some other stuff... yes, the same one that fried the capacitors next to the cpu's a few months ago just got 9iAS and "a db" installed with defaults by an MS person <sigh>).

>
> 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? ;-)

They be long gone, and insulated by an OCI generator anyways :-)

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

Well, I won't go 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.

Not if it's in the SGA, and less with an well-cached set of controllers.

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

Naw, only a little like that just now. But there will be more, that's what the Wintel is for. Hopefully will help unix box be more pure OLTP.
>
> > 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. ;-)

Would you believe I live a few miles from the Craftsman house in that movie? I've also been to that bar... :-)

jg

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