Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
Showing results for 
Search instead for 
Did you mean: 

gettin cputime from v$sqlarea

Frequent Advisor

gettin cputime from v$sqlarea

hi all,
how can i measure cpu time at any instant from v$sqlarea without using Statspack
pls help...its really urgent
Honored Contributor

Re: gettin cputime from v$sqlarea


Check this link:

don't forget to assign points, also in others threads.

Hein van den Heuvel
Honored Contributor

Re: gettin cputime from v$sqlarea

That's a pretty good link. Thanks.

avik.tcs, You can use statspack. Google and read! You have the power. You can do this!

If still you can not use statspack (privs?) then you should not be (asked to) do this job.

If someone instructs you to do this job anyway, then tell them "fine, but I'll need 'xyz' to accomplish this".

Many other tools exist in this space, notably the oracle enterprise manager.
Many give nice timelines on resource usage.

The best tool to use, is not yet clear from your question.

What is the real problem you are trying to solve? Please re-read you question and explain it better.

The exact answer to the current question is "0".
Since an instant is infinitely small, just a point in time, the cpu time at that instant must be zero. Correct?

So you mean something else. Perhaps you want to know the total cpu time used according to oracle during an interval.
A little like 'vmstat'?

So something needs to loop and wait.
This can be done in Oracle, but is gets a bit tricky with dbms_lock.sleep perhaps.
It can be done outside Oracle in a perl or shell script, but it gets a bit expensive to run.
It can be done in a program executing a cursor ( C, or maybe a perl module ) but that is a little harder and a waste of time not knowing waht you wnat , and possibly not knowing what to do with the result.

So here is a brute force, expensive but working solution for a question you may or might not have asked: How to show the total cpu time used by oracle in a given time interval.
This (perl) solution takes two arguments: interval_time and repeat_count
It uses an external sqplplus script which of course can be modified to get other stuff.
The perl script could get other stuff like system wide cpu time used and roll it all up.

#perl 5 5
Thu Feb 1 10:19:49 2007 total cpu 36438457, this interval 36438457
Thu Feb 1 10:19:54 2007 total cpu 36449829, this interval 11372
Thu Feb 1 10:19:59 2007 total cpu 36470107, this interval 20278
Thu Feb 1 10:20:05 2007 total cpu 36484245, this interval 14138
Thu Feb 1 10:20:10 2007 total cpu 36513640, this interval 29395

------------ -----------
$sleep = shift;
$repeat = shift;
$repeat = 10 unless $repeat;
$sleep = 10 unless $repeat;
while ($repeat--) {
@sql_output = qx (sqlplus -S user/pass\@xe \@get_cpu_time.sql);
$cpu = $1 if ($sql_output[1] =~ /(\d+)/);
$current = $cpu - $last;
print localtime()." total cpu $cpu, this interval $current\n";
$last = $cpu;
sleep $sleep if $repeat;

----------------- get_cpu_time.sql ------
set heading off
SELECT SUM(cpu_time) from v$sqlarea;
Frequent Advisor

Re: gettin cputime from v$sqlarea

thanks for the answer..
actually i want to measure the cpu time for different sql id running at a time.then i again want to get the same after a certain interval.
Hein van den Heuvel
Honored Contributor

Re: gettin cputime from v$sqlarea

That would only be a minor tweak on the general principle. instead of:

SELECT SUM(cpu_time) from v$sqlarea


SELECT cpu_time from v$sqlarea where sql_id = ...

Honored Contributor

Re: gettin cputime from v$sqlarea


Bear in mind that the query with the biggest cpu time is not always the WORST query!!

Things that you may need to also look at are: - physical IO
- logical IO

Also consider the following fact:

query 1, 10000000 cpu seconds
query 2, 10000000 cpu seconds

and query 1 is executed 10000 times
and query 2 is executed 10 times

which one do you "fix"? They both consume tons of cpu!

What would i do?
1. fix query 1 so as to not execute it so often (algorithm change, probably a programmer doing slow by slow coding instead of set based programing).

2. fix query 2 to be more efficient

and i would like to fix both.

Hope this helps too!

kind regards
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)