Solved

Oracle CPU utilization

Posted on 2003-10-21
8
1,919 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:jbauer22
8 Comments
 
LVL 23

Expert Comment

by:seazodiac
ID: 9593706
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
 
LVL 5

Accepted Solution

by:
Pontis earned 125 total points
ID: 9593905
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
 
LVL 23

Expert Comment

by:seazodiac
ID: 9594190
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 5

Expert Comment

by:Pontis
ID: 9594201

Defending Statspack :)

Simplest install of Statspack:

?/rdbms/admin/spcreate
?/rdbms/admin/spauto
0
 
LVL 2

Author Comment

by:jbauer22
ID: 9595317
I'm looking to collect CPU utilization % every at time of query.
0
 
LVL 5

Expert Comment

by:Pontis
ID: 9595636
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
 
LVL 48

Expert Comment

by:schwertner
ID: 9596893
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
 

Expert Comment

by:harnaud
ID: 9597870
All those answers are well, you should accept one.
Thx for them.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Pivot Question 8 70
having some issue on pl sql procedure 1 23
Oracle Insert not working 10 30
Help with Oracle IF statment 5 22
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question