Re: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 19 Jun 2004 08:35:35 +0100
Message-ID: <00c901c455d0$02781840$7102a8c0@Primary>

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_at_alise.lv> To: <oracle-l_at_freelists.org> 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_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 - 02:32:29 CDT

Re: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?