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
Messages: 41
Registered: September 2007
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:
   SUM(VALUE/100) cpu_seconds
   v$session s,
   v$sesstat t,
   v$statname n
   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: 6673
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: 64275
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: Thu Jan 19 22:11:51 CST 2017

Total time taken to generate the page: 0.07639 seconds