[Oracle-l] question about cpu usage

Ryan,

From my Unix SysAdmin once you reach 60% CPU utilization
you've reached the point of diminishing returns, namely things are going
to slow down real fast. This is one time I believe him as we've seen
the results.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

Question asked in such generality really doesn't make much sense and can
only have one
answer: it depends. Mostly, it depends on what is CPU doing. Well optimized
queries will
typically have a short burst or two of intense CPU activity and then will
finish. Using
100% of CPU power is, unfortunately, also characteristic for "well cached"
queries which
can perform a gazillion logical block gets with no phyisical disk reads. An
example of
such query is the following:

SELECT COUNT(*) FROM EMP,EMP,EMP,EMP,EMP,EMP,EMP,EMP,EMP,EMP;

Table emp normally has 14 rows so the number of rows to count is POW(14,10)
and
RDBMS process will be spinning using 100% of CPU for approximately 30
minutes.
What will you get? A worhless number which could have been computed in a
much, much
cheaper fashion. So, the answer to your question is: optimize your most
expensive
queries and only then try predicting scalability and growth of your
database.

ok i will refine the question.
Assuming I am at a constant rate X of CPU usage, will there be a declining returns as CPU usage increases to X + n for a constant period of time.

Assume CPU usage holds steady. CPU u sage is CPU usage. It shouldnt matter what its doing in terms of performance.
-------------- Original message --------------

It is not as simple as you might initially think it is.
Consider that in reality, saying a CPU is 50% busy/idle is somewhat
nonsensical.
Either is it doing useful work (at the request of the OS) [100% busy]
or it is idle [0% busy].
At any & EVERY point in time the CPU is either 100% or 100% idle;
nothing in between.

Now over a period of time it could be busy half the time (50% busy & 50% idle).

What happens if 10 different folks concurrently as the CPU to do work for them?
The answer is that 9 folks go into the CPU-wait queue.

As system system statistics display the reality the CPU is approaching
100% busy,
users may or may not experience a degredation in response time.

It all depends on the charateristics of the work load.
It might even depend upon the law of large number.

HTH & YMMV!

On Fri, 22 Oct 2004 20:57:15 +0000, ryan_gaffuri_at_comcast.net
wrote:

Ryan

Perhaps you can tolerate a mini-O.S. lesson.
A process can be in several states -- running (only one process/CPU),
waiting (for I/O, user response, etc.), or ready-to-run (or run queue). The
O.S. lets each process have the CPU (running) for a short interval of time,
or until it must wait for something like I/O. Once a process has its I/O
satisfied, it is put on the ready-to-run queue until the O.S. decides to put
it back on the CPU.

Basically there is a queue of processes waiting for the CPU. If the
system is nearly idle, that queue will be very short. When the process is
back from the I/O, it is quickly put back on the CPU. If the system is
heavily loaded, then the queue will get very long. If you are interested and
on Unix, check out the uptime command, which will show you the length of the
run queue on your server.

Many people find the run queue a better indicator of how heavily loaded
an OLTP server is.

Dennis Williams
DBA

Lifetouch, Inc.

I'm assuming you mean vmstat? what do you look for?

Response time for interactive users is non-linear with respect to cpu
utilization. The curve looks like a hockey stick with the puck striking
surface pointing up. If you are past the knee of the curve your
interactive users are receiving unpredictable response times. If you
are at 90% utilization and you have 4 CPU's or less you are probably
driving your interactive users mad. Query response time for them will
swing wildly between "normal" to 10 to 100 times normal. Your batch
jobs and your total throughput on the other hand might be doing pretty
well. You can get Cary Millsap and Jeff Holt's Optimizing Oracle
Performance from O'Reilly. You can go to hotsos.com and register for a
=66ree acount and get some white papers and an excel spreadsheet that will
show you where the knee on the curve is relative to the number of CPU's.

BTW, number of cpu's is the only factor that affects the knee of the
curve and for most affordable configurations and normal mixes of OLTP vs
batch you will find the you really want to stay less than 85% and for
small machines with 4 or less cpus you are best of to stay below 70%.

Allan

I am not graduated from Computer Science major, so I only talk about
my own experience.
I have seen both cases,
1. CPU runs at 100% and application performance does not degrade. (We
run tuxedo middleware and I measured response time via txrpt). This
application use database heavily and SQL was not good.CPU number was 4
and load (uptime) was 8-10. The response time was same between CPU
usage at 60% and 100%. The bottleneck in the database was purely
Logical IO(no disk IO contention and lock contention etc).

2. CPU usage rise to 70% and we see a lot of application report error.
(Time out)This was because business grows and our server reached its
IO capacity. A lot of disk io wait event and also a lot of Enqueue
wait contention.
Regards
Zhu Chao
www.cnoug.org

Of course it's non-linear. If OLTP program is well written, it will mostly=20
utilize I/O - screen, disk and network. Solving a small 100,000x100,000 lin=
ear=20
system by using Gauss-Jordan method is seldomly a part of an OLTP program.
Also, very few programs are created optimal. If I, for the sake of speed of
development write a perl/DBI program that utilizes perl "FORMAT" statement,
I'll sacrifice the speed of the program itself. I'll page in the whole perl
interpreter, then by executing "use DBI;" I'll load and map an additional d=
ynamic=20
library, and I'll do so again when I execute "DBI->connect("dbi:oracle:$db"=,$usr,$pwd)".
If I start using additional modules like LWP or MIME::Lite it will expand t=
hat
perl interpreter to monumental proportions. Address space can easily exceed=
100MB.
The same thing will happen if Java is used. Any other application developme=
nt tool=20
like ColdFusion or Oracle*Forms is many times as expensive as perl or Java.=
If=20
your box is running only a single database, the situation is clear but if t=
here are
things like LVM, backup server, print spooler or X11, the situation can be =
very=20
different. Sendmail with decent rule set, connecting to SpamAssassin and Cl=
amAV can
also waste quite a bit of resources, including the CPU, especially if SpamA=
ssassin=20
is version 3.0 equipped with all the submodules (Pyzor, DCC, Spamcop). It i=
s=20
multithreaded and humongous and can easily waste a whole SMP system. On the=
other=20
hand, if the app system is written by using an efficient TP monitor like Tu=
xedo, Top End
or CICS and C/C++, then running out of CPU can mean that you desperately ne=
ed a HW=20
upgrade, not just a better system administrator.
To summarize, high CPU utilization is frequently a sign of suboptimal compo=
nent. If the=20
CPU is spent in the system mode, there is a problem with a driver or kernel=
component,
like excessive paging. If the CPU is spent in user mode, then it is most l=
ikely an
application which shouldn't be on an OLTP system in the first place. Genera=
l connections
between OLTP user response time and CPU (ab)use are completely irrelevant a=
nd make no=20
sense.
--=20
Mladen Gogala
Oracle DBA

I forgot to cc the list last Friday when I responded to this...

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *

Upcoming events:
- Performance Diagnosis 101: 10/26 Toronto, 1/4 Calgary

SQL Optimization 101: 10/18 New Orleans, 11/8 Dallas, 12/13 Atlanta
Hotsos Symposium 2005: March 6-10 Dallas
Visit www.hotsos.com for schedule details...

Ryan - No I meant the "uptime" command. On most Unix systems uptime lists
the run queue length over several time samples.


Dennis Williams

DBA

Lifetouch, Inc.



From: ryan_gaffuri_at_comcast.net
Sent: Friday, October 22, 2004 5:46 PM
To: DENNIS WILLIAMS; oracle-l_at_freelists.org
Cc: DENNIS WILLIAMS
Subject: RE: question about cpu usage





i'm assuming you mean vmstat? what do you look for?

All these talks about why CPU usages should be
below 100% would make an experienced Mainframe
system programmer -that is an syste administrator
in unixspeach for self-respecting system administrator
*do* write programs in assembly- smile. Everyone knows that a system
need to be at 100% CPU usage or
otherwise the system is underutilized.

The reason why UNIX administrator loses his temper
when CPU utilisation goes beyond 60% is that UNIX
monitoring tools do not count pure CPU usage. They
add system time, time spent in I/O and everything
else in the count. UNIX task chedulers are so lame
that they can not properly distribute CPU time
between interactive processes wen load average
goes beyond %60.