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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: