gettin cputime from v$sqlarea

[Dodo_5]

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

[Oviwan]

Hy

Check this link:
http://www.oracle.com/technology/pub/articles/schumacher_analysis.html

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

Regards

[Hein van den Heuvel]

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 sqlplus_sleep.pl 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

------------ sqlplus_sleep.pl -----------
$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;
EXIT;

[Dodo_5]

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]

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

SELECT SUM(cpu_time) from v$sqlarea

try

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



Hein.

[Yogeeraj_1]

hi,,

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
yogeeraj