| 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?
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 spending our time we will get most.
As Jonathan Lewis note there are can be situations then some activates takes CPU resources, but doesn’t reflect in the logical read statistics. As mentioned Jonathan latch contention can be the issue or buffer is pinned.
But I would like to pay your attention to activities which, by my option, 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
vvvvvvvvvvvvvvvvv Elapsed: 00:00:01.35
^^^^^^^^^^^^^^^^^^
vvvvvvvvvvvvvvvvv
CPU used by this session 137
^^^^^^^^^^^^^^^^^^
2. Activity
vvvvvvvvvvvvvvvvv Elapsed: 00:00:14.22
^^^^^^^^^^^^^^^^^^
NAME VALUE
vvvvvvvvvvvvvvvvv
CPU used by this session 1422
^^^^^^^^^^^^^^^^^^
========================================
As you can see both activities have taken equal amount of “logical reads”,
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 just on BUFFER_GETS as in statspack.
Please correct me if I am wrong.
Jurijs
9268222
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
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
To: <oracle-l_at_freelists.org>
cc:
Subject: Re: The best CPU usage measurement in Oracle:
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?
I would like to identify TOP CPU consumers. As we all know I the V$SQL is the best information source in this case.
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 -----------------------------------------------------------------Received on Sat Jun 19 2004 - 08:05:37 CDT
 |
 |