Home » RDBMS Server » Performance Tuning » % cpu used by a session
% cpu used by a session [message #614149] Sat, 17 May 2014 09:59 Go to next message
Malakay79
Messages: 31
Registered: September 2007
Member
Hi,
I need to get the cpu seconds used by a session during the execution of a command (insert as select command).
I used this query:
SELECT
   SUM(VALUE/100) cpu_seconds
FROM
   v$session s,
   v$sesstat t,
   v$statname n
WHERE
   t.STATISTIC# = n.STATISTIC#
AND
   NAME like '%CPU used by this session%'
AND s.sid=312;


These are my step:

1) Execute the query above to get the start cpu seconds (Example:230)
2) Execute the statement.
3) Execute again the select in order to have the new value. (Example: 2324)
4) Then I calculate the difference (2324- 230 = 2094 seconds or 34,9 minutes)

My statements runs in 15 minutes. How can be possible that a statements that run in 15 minutes uses 34 minutes of cpu?

[Updated on: Sat, 17 May 2014 10:07]

Report message to a moderator

Re: % cpu used by a session [message #614150 is a reply to message #614149] Sat, 17 May 2014 10:11 Go to previous messageGo to next message
John Watson
Messages: 4676
Registered: January 2010
Location: Global Village
Senior Member
From where did you get that query? It is rubbish. There are several statistics whose names match that string, and you have no join condition to v$session. This is another example of how failing to use ANSI join syntax lets people make stupid mistakes.

You need to query v$mystat for the exact statistic# that you want.
Re: % cpu used by a session [message #614154 is a reply to message #614150] Sat, 17 May 2014 11:00 Go to previous message
Michel Cadot
Messages: 59414
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, why LIKE? why not the specific statistics name you want? and why SUM? It just hides your mistake, you want one statistic and only one. If the query returns more than one row it is wrong, do not hide the error using SUM or whatever.
To write SQL you must know exactly what you want and if you don't get it you are wrong, not SQL.

Previous Topic: Procedure runs slow whereas
Next Topic: dba_hist_snapshot vs dba_hist_active_sess_history
Goto Forum:
  


Current Time: Tue Oct 21 15:52:40 CDT 2014

Total time taken to generate the page: 0.08977 seconds