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 
========================================
PL/SQL procedure successfully completed.

vvvvvvvvvvvvvvvvv
Elapsed: 00:00:01.35
^^^^^^^^^^^^^^^^^^
NAME VALUE
---------------------------------------------------------------- 
----------
vvvvvvvvvvvvvvvvv
session logical reads 23002
CPU used by this session 137
^^^^^^^^^^^^^^^^^^

2. Activity 
========================================
PL/SQL procedure successfully completed.

vvvvvvvvvvvvvvvvv
Elapsed: 00:00:14.22
^^^^^^^^^^^^^^^^^^

NAME VALUE
---------------------------------------------------------------- 
----------
vvvvvvvvvvvvvvvvv
session logical reads 23006
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
============================================
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 
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
 
        To:     <oracle-l@xxxxxxxxxxxxx>
        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

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 ----- 
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 
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?


=======================================================
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.
=======================================================

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 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: