FW: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?
- From: "Cary Millsap" <cary.millsap@xxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 18 Jun 2004 09:50:08 -0500
Dan, I think you've said this very nicely.
Everyone, For a great example of this phenomenon that Dan's talking about,
take a look at the case study in "Selected topics in Oracle performance
problem diag..." at www.hotsos.com/e-library.
Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com
* Nullius in verba *
Upcoming events:
- Performance Diagnosis 101: 6/22 Pittsburgh, 7/20 Cleveland, 8/10 Boston
- SQL Optimization 101: 5/24 San Diego, 6/14 Chicago, 6/28 Denver
- Hotsos Symposium 2005: March 6-10 Dallas
- Visit www.hotsos.com for schedule details...
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Daniel Fink
Sent: Friday, June 18, 2004 9:40 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Re: The best CPU usage measurement in Oracle: BUFFER_GETS or
CPU_TIME?
Jurijs,
This is not a direct answer to your question, but I do have something you
might want to think about.
Consider this scenario. The CPU time for a given statement/application
decreases...but response time increases. Does this indicate
you have a non-CPU bottleneck? Not necessarily. In the issue I dealt with,
the CPU time in our trace files was rather low compared
to elapsed time (over 40% less). This indicated that over 40% of the
response time was unaccounted for, in essence, there was no
database activity when there *should* have been. We had suspected CPU
starvation (4 databases on the server and the process we were
tracing was running 5 parallel processes on a 4 cpu server) and the trace
analysis and o/s monitoring confirmed it. 40% of the time,
the processes were sitting in the cpu run queue, not performing any actual
work. If we had added more processes, our % of CPU
consumption would have continued to decrease as a response time component as
the response time increased.
If you are concerned about the cpu, you need to check o/s stats and compare
cpu time against elapsed time for the application of
interest. Don't expect cpu = elapsed (Cary's book does a great job of
explaining why). On non-cpu starved systems, the unaccounted
for seemed to range in between 3% and 7%, with a very low contribution to
response time.
You need to look at the whole system/application, not just specific sql cpu
consumption. I'd venture to say that you could decrease
the % of cpu in a response time profile by *increasing* the number of
processes that are running. Of course, your response time will
go through the roof, but your cpu % will decrease. I'm sure Connor has a
script to accomplish this ;)
Regards,
Daniel
J.Velikanovs@xxxxxxxx wrote:
>
> 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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
- » FW: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?
