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

Other related posts: