RE: see higher CPU usage after increase SGA
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 9 Jun 2004 11:21:05 -0500
Zhu,
I think what’s happening to you is strictly a function of how
you’re
looking at your measurements.
First, I like to distinguish between the terms “usage” and
“utilization.” (You’ve used them in your post as
synonyms.) This
distinction helps me make a point. So, first, some definitions:
Usage - the total duration for which a resource is “in
use.”
Utilization - usage divided by capacity for a specified time
interval.
Now, “utilization” is a concept inherently susceptible to ratio
fallacies.
This is because utilization, of course, IS a ratio. I think the ratio
fallacy that’s victimizing you right now is this one...
Conjecture: If CPU utilization went up, then performance must have
gotten worse.
This conjecture is false. It’s a commonly-believed rule of thumb, but
it’s
just not right. Here’s what I think has happened to you. When you reduced
the [so-called] physical I/O count, you probably reduced the total run-time
of your program. But of course, you have kept your CPU usage constant (you
haven’t reduced the LIO count by making your buffer cache bigger). So
look
at the impact of what you did upon utilization. Remember,
Utilization = (Usage) / (Capacity for a specified time interval).
You have kept usage constant, but you’ve reduced the specified time
interval. Therefore, your utilization went up.
The fallacy is that when CPU utilization goes up, it can also be an
indication that you’re simply able to get more work done now in the same
time interval as before.
Now, my questions for you:
1. Did your action reduce your total response time by some small percentage
(maybe 5-6%)?
2. What’s more important to you: (a) Utilization statistics? Or (b) total
runtime?
I hope your answer to question 2 is (b), because (a) is a flawed metric;
utilization is NOT a reliable measure of the quality of PERFORMANCE. Answer
(b) does not leave you vulnerable to the ratio fallacies that (a) does.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 Boston
- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Mark W. Farnham
Sent: Wednesday, June 09, 2004 9:04 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: see higher CPU usage after increase SGA
okay, I'm a sucker for mystery puzzles with partial information. Take a look
at Hotsos' site to get an idea of figuring out where you're burning your
time. In the meantime, from what you've written I have these observations:
1) Neither disk farm/io channel capacity nor CPU utilization is what limits
your performance to 76.54/sec.
-- How can I say this? Well, in the boundary case that you exactly shifted
the bottleneck from disk to CPU I'd be wrong,
but otherwise you previously had CPU to burn and now you've got io capacity
to burn, and the rate didn't change.
2) Probably the increase in CPU utilization is just searching longer memory
structures in Oracle, although I suppose it could be something like a bit of
increased paging. OR, now that you've marginally reduced waits for disk
reads you're pounding a bit harder on the actual bottleneck.
Someone might have an educated guess at your new bottleneck, but without
data about where your processes are spending time and which resources are in
short supply compared to load, I'm guessing all they'd be doing is guessing.
Now, trying to be actually useful, I think your next task is to find out
where time is being spent. If analysis shows that the big consumer(s) is/are
already doing as little work as possible, then you make the restrictive
system component faster (or discover that it is not cost effective to make
the bottleneck resource faster.) If analysis shows that big consumer(s)
is/are doing more work than required for the task at hand, you work to
improve the big consumer(s).
mwf
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of zhu chao
Sent: Wednesday, June 09, 2004 9:31 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: see higher CPU usage after increase SGA
Hi,
I once saw Jonathan said at metalink that huge SGA does not help in many
case, But no further discuss at that topic later. Last night we added 1Gb
to oracle sga and we see fewer disk read but higher CPU usage.
Fewer disk read of course cut CPU usage, but larger buffer cache
management in unix and oracle, seems caused higher CPU usage. Has someone
also have similar experience? How to explain the higher CPU usage?
We have a 16GB memory sun 880 with 10G data cache. As disk read get
higher and higher , and not much SQL to tune we deciede to increase data
buffer from 10G to 11GB, as there is still 1.5G free memory on the host.
We expect to see some CPU usage drop, as disk read drop by 30%. But
after 1 day's run, we saw higher CPU usage then before we increase the SGA.
http://www.cnoug.org/attachments/LDBn_cpu.bmp (the Excel picture that
shows the CPU usage before and after increase sga).
The following Statistics from Oracle shows the load profile before and
after SGA increase:
LIO PIO Transaction/Second
CPU usage in oracle
10gb 47,990.70 448.68 76.54
177.9
11gb 47,707.28 325.95 76.54
187.9
Change: Nearly same Disk read dropped Transaction rate
CPU used increased.
30% keep consistent
by 5%
Time I measure： 9 am - 15pm.
Oracle: 5% increase.
Unix: 6% increase.
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
