sventhan
asked on
SQL by Hourly break down
Experts -
How can I convert the output of this SQL into a hourly format based on logon time or start_exec time? Is that possible to convert the CPU_THIS_CALL to %CPU utilization?
Thanks,
Sve.
How can I convert the output of this SQL into a hourly format based on logon time or start_exec time? Is that possible to convert the CPU_THIS_CALL to %CPU utilization?
Thanks,
Sve.
SELECT s.USER#,
s.USERNAME,
s.STATUS,
s.OSUSER,
s.PROCESS,
s.MACHINE,
UPPER (s.PROGRAM) PROGRAM,
s.LOGON_TIME,
s.sql_exec_start stime,
s.LAST_CALL_ET,
stat.cpu - stat.CPU_this_call_start cpu_this_call,
stat.CPU
FROM V$SESSION S,
( SELECT ss.sid stat_sid,
SUM(DECODE (sn.name,
'CPU used when call started', ss.VALUE,
0))
CPU_this_call_start,
SUM(DECODE (sn.name,
'CPU used by this session', ss.VALUE,
0))
CPU,
SUM (DECODE (sn.name, 'session uga memory', ss.VALUE, 0))
uga_memory,
SUM (DECODE (sn.name, 'session pga memory', ss.VALUE, 0))
pga_memory,
SUM (DECODE (sn.name, 'user commits', ss.VALUE, 0))
commits,
SUM (DECODE (sn.name, 'user rollbacks', ss.VALUE, 0))
rollbacks
FROM v$sesstat ss, v$statname sn
WHERE ss.STATISTIC# = sn.STATISTIC#
AND ( sn.name = 'CPU used when call started'
OR sn.name = 'CPU used by this session'
OR sn.name = 'session uga memory'
OR sn.name = 'session pga memory'
OR sn.name = 'user commits'
OR sn.name = 'user rollbacks')
GROUP BY ss.sid) stat
WHERE ( (s.USERNAME IS NOT NULL)
AND (NVL (s.osuser, 'x') <> 'SYSTEM')
AND (s.TYPE <> 'BACKGROUND'))
AND (stat.stat_sid = s.sid)
and s.username = 'username'
ORDER BY "USERNAME", OWNERID
Providing the procedure isn't enough to evaluate your request. You have several functions with unknown use and without some insight into your data I'd be fumbling around in the dark. I'm sure that grouping by HOUR(LOGON_TIME) is possible but don't have enough to go on at this point.
ASKER
Thanks for your time. This SQL can run on any oracle environment, if you would like to know about the data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
This SQL can run on any oracle environment, if you would like to know about the data.
Then please request that the SQL Server topic be removed. It just creates confusion.
Then please request that the SQL Server topic be removed. It just creates confusion.
ASKER
Thanks for your time. That works for me.