Solved

Oracle CPU utilization

Posted on 2003-10-21
8
1,922 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
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…

732 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