RE: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 19 Jun 2004 10:32:39 -0500
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.).
* There are certain problems (a lot of them in my experience) that you =
will
never be able to solve reliably with Statspack or ANY other tool that's
based upon V$ data. *
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@xxxxxxxxxxxxx =
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of J.Velikanovs@xxxxxxxx
Sent: Saturday, June 19, 2004 8:01 AM
To: oracle-l@xxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
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
^^^^^^^^^^^^^^^^^^
NAME VALUE
----------------------------------------------------------------=20
----------
vvvvvvvvvvvvvvvvv
session logical reads 23002
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
session logical reads 23006
CPU used by this session 1422
^^^^^^^^^^^^^^^^^^
=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
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@xxxxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
19.06.2004 10:35
Please respond to oracle-l
=20
To: <oracle-l@xxxxxxxxxxxxx>
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
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
----- Original Message -----=20
From: <J.Velikanovs@xxxxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Friday, June 18, 2004 8:47 AM
Subject: The best CPU usage measurement in Oracle: BUFFER_GETS or=20
CPU_TIME?
My regards to all members,
Just would like to know your opinion.
Since 9i (suppose 9.2) we have CPU_TIME column in the V$SQL view.
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
----------------------------------------------------------------
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:
