Re: CPU Time

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "Active DBA" <oracle-l@xxxxxxxxxxxxx>
  • Date: Sun, 9 Jan 2005 17:35:14 -0000

The point I was trying to make was:

    Why do you think you have to do anything
    to reduce it. You haven't told us anything that
    shows it to be a problem.

You report 1 hour 16 minutes of CPU usage
in one hour.  If you have just 2 CPUs, then
they are quite busy - but that doesn't mean you
need to worry about them.  If you have 16 CPUs,
then 5 minutes per CPU per hour doesn't sound
like a problem - but it might be if there is one
person who keeps putting in a request for a
report that should take 1 second but which 
takes 5 minutes of CPU.

The only thing you can say about the fact that
CPU is up at 91% is that your database doesn't
seem to be losing a lot of time.  So you don't
seem to be waiting for much I/O, you don't 
seem to be having problems with buffer collision,
you don't seem to be having problems with locking,
you don't seem to be having significant problems with 
excessive amounts of hard parsing (although that last
one doesn't mean that you are doing well, only that
if you are doing badly it doesn't seem to be having
much impact).

Looking for SQL with high logical I/O costs is
a reasonable (though not complete) way of looking
for 'easy' ways of reducing your CPU consumption.
And since you have Oracle 9, you can check v$sql
for statements with the highest recorded CPU
usage - that might pick up a couple of statements
that are clearly very CPU-intensive per execution.

But even after you have done the easy bits, remember
there are lots of other little reasons why you  MIGHT 
be using a lot of CPU, which don't stand out so easily.


Jonathan Lewis
The Co-operative Oracle Users' FAQ
Public Appearances - schedule updated Dec 23rd 2004

----- Original Message ----- 
From: "Jaffar_DBA" <sjaffarhussain@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: "Active DBA" <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, January 09, 2005 3:16 PM
Subject: Re: CPU Time

: Jonathan,
: Thanks for the correction.
: Only my question was, why CPU time appears in the top 5 timed events
: as 91% and the rest timed events are just 9%.
: for your information, statspack snap elapsed time was 1 hr.
: When I use response time formula, CPU Recursive show around 80% from
: my statspack report. I tried to look for the SQL order by gets from my
: statspack report. I just wanted to confirm, I am at the current place
: or not to identified resource consuming queries.


Other related posts: