Solved

Oracle CPU utilization

Posted on 2003-10-21
8
1,916 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
 
LVL 5

Expert Comment

by:Pontis
ID: 9594201

Defending Statspack :)

Simplest install of Statspack:

?/rdbms/admin/spcreate
?/rdbms/admin/spauto
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 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 47

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

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.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Salary Amount Format 13 56
T-SQL Convert to PL/SQL 23 62
Dataware house query tuning 9 33
PAYER_ID has both atributes 4 19
Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now