| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?
Jurijs,
What you're saying is one of the key reasons that I pay attention almost always to trace data and almost never to V$ data.
The problem with paying attention to sort counts and things like that =
(even
LIO counts!) is that some sorts are cheap, and some are really =
expensive.
You cannot tell how long they take by counting how many times they =
happen.
However, from your trace data, it's pretty simple to see when c has a =
value
that's larger than is normally explained by your cr+cu value, and you =
can
corroborate the time consumption by noticing SORT row source operations =
in
your STAT lines. In 9.2.0.2 and beyond, you can even see elapsed time
consumption per row source operation in your STAT output (but watch for =
bug
3009359).
With V$ data, you have to be lucky to find the problem, because of the =
way
the information gets aggregated before you can even collect it. For =
example,
it's entirely possible for a workload A with sorts=3D1 to take more time =
than
a workload B with sorts=3D1003. All it takes is for the sort of A to be =
1004
times more expensive than the average sort duration of B. It is MUCH =
more
difficult to tell whether this is happening by looking at your V$ data =
(if
it's even possible at all)...
The big problem with Statspack or ANY other tool that relies upon V$ =
data is
that, because of the aggregation done inside the Oracle kernel, it =
presents
very little solid evidence about response time. What you by looking at =
event
counts and aggregations of response time consumption qualifies =
technically
as only circumstantial evidence about the response time of an individual
user action under inspection.
It is also very simple with trace data to find the "time spent =
preempted"
problem that Dan brought up. It is considerably more difficult to do =
with V$
data. I'm not sure how you'd possibly go about it, actually, because =
there's
no place in the V$ data where you can pick up statistics about =
individual
executions of dbcalls (parse, exec, fetch, etc.).
More generally, the problem is not V$ data in particular, it's ANY
performance diagnostic data that is aggregated before you can collect =
it. If
you're interested in this phenomenon, there is a lot more detail present =
in
the book "Optimizing Oracle Performance"--especially chapters 1 and 8.
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_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of J.Velikanovs_at_alise.lv
Sent: Saturday, June 19, 2004 8:01 AM
To: oracle-l_at_freelists.org
Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org
Subject: Re: The best CPU usage measurement in Oracle: BUFFER_GETS or
CPU_TIME?
Thank you all for responses.
I totally agree with Daniel regarding "CPU starvation" issues. But if we =
have one we cant see it ether from BUFFER_GETS or CPU_TIME any way ;)
Lets imagine we have no one. And we would like find TOP SQL on which=20 spending our time we will get most.
As Jonathan Lewis note there are can be situations then some activates=20 takes CPU resources, but doesn't reflect in the logical read statistics. =
As mentioned Jonathan latch contention can be the issue or buffer is=20 pinned.
But I would like to pay your attention to activities which, by my =
option,=20
takes place more often then others CPU consuming activates and can takes =
much significant CPU resources.
It is !SORTING!
I have gotten results bellow:
1. Activity=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
PL/SQL procedure successfully completed.
vvvvvvvvvvvvvvvvv Elapsed: 00:00:01.35 ^^^^^^^^^^^^^^^^^^
----------------------------------------------------------------=20
vvvvvvvvvvvvvvvvv
CPU used by this session 137 ^^^^^^^^^^^^^^^^^^
2. Activity=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
PL/SQL procedure successfully completed.
vvvvvvvvvvvvvvvvv Elapsed: 00:00:14.22 ^^^^^^^^^^^^^^^^^^
NAME VALUE
----------------------------------------------------------------=20
vvvvvvvvvvvvvvvvv
CPU used by this session 1422 ^^^^^^^^^^^^^^^^^^
As you can see both activities have taken equal amount of "logical =
reads",=20
but CPU consuming for second one is 10 times bigger.
The key answer why I have gotten such unproportional results is SORTING.
For fists activity
sorts (memory) 1
For second
sorts (memory) 1001
So. I would say, it is better from this perspective to take a look first =
on CPU utilization not on BUFFER_GETS, or if you want on both. But not=20 just on BUFFER_GETS as in statspack.
Please correct me if I am wrong.
Jurijs
9268222
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
http://otn.oracle.com/ocm/jvelikanovs.html
PS Activities code
1.
declare
vn number;
begin
for f in 1..1000 loop
select count(*) into vn from (select * from testsort);
end loop; end;
/
2.
declare=20
vn number;
begin
for f in 1..1000 loop
select count(*) into vn from (select * from testsort order by 1);
end loop; end;
/
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
Sent by: oracle-l-bounce_at_freelists.org
19.06.2004 10:35
Please respond to oracle-l
=20
To: <oracle-l_at_freelists.org>
cc:=20
Subject: Re: The best CPU usage measurement in Oracle:=20
BUFFER_GETS or CPU_TIME?
There is no one safe measure to pursue -
buffer_gets is a good guideline - but different activities on the buffer consume different amounts of CPU - so you can legally have high CPU with relatively low buffer_gets, even buffer_gets is the most significant area of activity.
Latch contention pushes up CPU - so two statements with the same number of buffer-gets may report different amounts of CPU because one of them was constantly competing for a hot latch.
v$sql doesn't tell you about 'buffer is pinned' activity, which is buffer activity that doesn't require latch access, so SQL with very low buffer_gets can use very large amounts of CPU.
In short - neither measure is guaranteed to be sufficiently meaningful to be the better one to monitor.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html Optimising Oracle Seminar - schedule updated May 1st
I wonder which figure is the best measurement of CPU usage BUFFER_GETS =
or
CPU_TIME?
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
Lets imagine I have system with CPU bottleneck (can see height "load
average" from OS) for a 1-3 ours. No particular long sessions have been
executed. It is seams mainly OLTP system. Parse CPU usage not the issue.
I would like to identify TOP CPU consumers. As we all know I the V$SQL =
is
the best information source in this case.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D
I wonder which figure is the best measurement of CPU usage: BUFFER_GETS =
or
CPU_TIME? Statspack report, as well as Anjo Kolk www.oraperf.com
recommending to look on BUFFER_GETS not to CPU_TIME. Is it just =
tradition
or there are some arguments not to look on CPU_TIME as main CPU usage
indicator.
One reason I can imagine why CPU_TIME better indicator then BUFFER_GETS =
is
sorting. I can imagine that BUFFER_GETS not taking in account CPU spent =
to
sorting staff. Then from CPU usage perspective better indicator is
CPU_TIME.
Please correct me if I am wrong.
Thanks in advance,
Jurijs
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sat Jun 19 2004 - 10:31:43 CDT
 |
 |