Re: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 19 Jun 2004 08:35:35 +0100
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 //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
