Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle CPU utilization

Posted on 2003-10-21
8
Medium Priority
?
1,947 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 500 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

927 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