| % cpu used by a session [message #614149] |
Sat, 17 May 2014 09:59  |
Malakay79
Messages: 41
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  |
John Watson
Messages: 8966
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 |
 |
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
|
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
