Re: CPU Time

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.

Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated Dec 23rd 2004

• Original Message ----- From: "Jaffar_DBA" <sjaffarhussain_at_gmail.com> To: <jonathan_at_jlcomp.demon.co.uk> Cc: "Active DBA" <oracle-l_at_freelists.org> 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.
:

--
http://www.freelists.org/webpage/oracle-l