Oracle CPU utilization

We have an Oracle database running on Unix with 8 CPUs.  Are there any system tables I could query to determine the CPU utilization, or is this something I can only get from Unix?  If I can only get the CPU utilization from Unix is there a way I can use a Stored Procedure to get this information and populate it into a table?
LVL 2
jbauer22Asked:
Who is Participating?
 
PontisCommented:
Use Oracle Statspack to collect historical CPU usage and to produce reports on it .

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/toc.htm

It will give you insights into what was overall CPU usage, what wait events were there and you can see what SQL is using most of CPU.

Generally speaking, total instance CPU used is in v$sysstat view:

select * from v$sysstat where upper(name) like '%CPU%';

You will get (depends on version, 9i - microseconds, 8i - centiseconds)

recursive cpu usage  - cpu used by recursive calls (triggers, procedures, internal Oracle processing etc)
CPU used by this session - cpu used by instance cumulative from startup
parse time cpu - cpu used for parsing
There is no need to write your own procedure - use Statspack and you can use the standard Statspack report or query statspack tables to calculate any kind of statistics you want historically.

That's what we do a lot :)

And you can even integrate vmstat output into Statspack

http://www.dba-oracle.com/art_tr_vmstat.htm






0
 
seazodiacCommented:
your best shot will be using UNIX command:
there are quite a few you can choose:

vmsstat 10 10

sar -qu 10 10

top

or ps -ef |grep ora

you can echo these result to text file,
and then in PL/SQL , use utl_file to read the file into a table in oracle.
or use the sql*loader to load file into the table.

0
 
seazodiacCommented:
Oracle Statspack can do the job, but way too complicated to set up comparatively speaking. but it depends on what granularity of CPU statistics you want to collect.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PontisCommented:

Defending Statspack :)

Simplest install of Statspack:

?/rdbms/admin/spcreate
?/rdbms/admin/spauto
0
 
jbauer22Author Commented:
I'm looking to collect CPU utilization % every at time of query.
0
 
PontisCommented:
You mean that you want to profile your query?

Well, best way is to run the system for sometime with test or real load and collect statspack data at the same time and then you will get averaged performance statistics.
 
 To profile the query, use  autotrace in SQL*Plus  - will show you some stats in there.
0
 
schwertnerCommented:
Both methods are good.
Seazodiac method gives you a snapshot - easy, fast.
Pointis method give you full insight in the behavior of your DB.

There are some other tool, mainly on Quest.
You can visit and download trial versions (seems for one month usage).
It will give you an excelent GUI snapshot how your DB works.
0
 
harnaudCommented:
All those answers are well, you should accept one.
Thx for them.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.