asked on

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?

seazodiac

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.

ASKER CERTIFIED SOLUTION

Pontis

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

seazodiac

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.

Pontis

Defending Statspack :)

Simplest install of Statspack:

?/rdbms/admin/spcreate
?/rdbms/admin/spauto

jbauer22

ASKER

I'm looking to collect CPU utilization % every at time of query.

Pontis

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.

schwertner

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.

harnaud

All those answers are well, you should accept one.
Thx for them.