Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: The best CPU usage measurement in Oracle: BUFFER_GETS or CPU_TIME?

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

From: Stephane Faroult <>
Date: Mon, 21 Jun 2004 09:40:24 +0200
Message-ID: <>

Cary Millsap wrote:


>More generally, the problem is not V$ data in particular, it's ANY

I think that presenting things as abruptly requires a bit of qualification. To me, it's the same as saying that macroeconomics are totally useless and microeconomics rule. Well, yes and no. I don't like statspack much and I usually use my own scripts to collect what I need. But at the same time, I very rarely use traces - for one thing, using them in a production environment which has trouble keeping afloat isn't always easy, and I don't always have the freedom to connect as Oracle (or to connect, at the OS level, to the server. No such problem with Oracle, since the password for DBSNMP is rarely changed and ALL_USERS usually shows an acount named SUPERDBA the password of which is ... - I am not sure that some queries on the V$ may not put as much overhead on the system as a localized trace, but it's more discreet :-)). It's probably a frame of mind as well. I have several millions of lines of code behind me, and have always disliked debuggers.
Traces certainly are a great tool in some cases for understanding some complex situations. But to get the broad picture and an understanding of what is going on from a business point of view, V$ are quite valuable. If aggregates were invented, it's simply because people were lost in the details. Something as simple as checking which statements are executed most often is very telling (such as getting four times per second currency exchange rates which are updated once a day ...). To me, understanding what people are trying to do comes first, and how they are doing it second. I am certain that you'll agree with me that it is pointless to minimize wait events on a query which has no reason to be run in the first place - but I am not sure that everybody understands it as clearly, especially some well-intentioned beginners. I have sometimes seen on this list questions such as: 'I am running this query :

                            select distinct a.c1, b.c2
                            from mega_table a, gigantic_table b;
  and it is very slow. What can I do to speed it up ?' To which the answer was :

      'Have you tried to set event 10046?'.

Cough, cough, cough! Well, it's better than 'your BCHR looks dreadful, try to increase your SGA size'.

Stephane Faroult

Please see the official ORACLE-L FAQ:

To unsubscribe send email to: put 'unsubscribe' in the subject line.
Archives are at
FAQ is at
Received on Mon Jun 21 2004 - 02:38:19 CDT

Original text of this message