Query to see the top 10 jobs based on total runtime for day

select * from (
select q.concurrent_queue_name qname,
c.concurrent_program_name
' (' to_char
(c.concurrent_program_id) ')' intprog,
ctl.user_concurrent_program_name "program",
sum((nvl
(actual_completion_date,sysdate)-actual_start_date)*1440) "TotTime",
count(*),
avg((nvl
(actual_completion_date,sysdate)-actual_start_date)*1440) "AvgTime"
from APPLSYS.fnd_Concurrent_requests
a,APPLSYS.fnd_concurrent_processes b
,applsys.fnd_concurrent_queues q
,APPLSYS.fnd_concurrent_programs c
,APPLSYS.fnd_concurrent_programs_tl ctl
where a.controlling_manager = b.concurrent_process_id
and a.concurrent_program_id =
c.concurrent_program_id
and a.program_application_id = c.application_id
and b.queue_application_id = q.application_id
and b.concurrent_queue_id = q.concurrent_queue_id
and ctl.concurrent_program_id = c.concurrent_program_id
and
ctl.language = 'US'
and trunc(actual_completion_date) = trunc(sysdate)
and actual_start_date is not null
and
actual_completion_date is not null
group by q.concurrent_queue_name,
c.concurrent_program_name ' (' to_char
(c.concurrent_program_id) ')' ,
ctl.user_concurrent_program_name
order by 4 desc)
where rownum < 11; Query to get the application user name based on the SID. select /*+ rule */ to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM') startedat, a.time, floor (s.last_call_et/3600)':' floor(mod(s.last_call_et,3600)/60)':' mod(mod(s.last_call_et,3600),60) "LastCallET", u.user_name, u.description , s.module ' - ' a.user_form_name forminfo from applsys.fnd_logins l, applsys.fnd_user u, apps.fnd_signon_audit_view a, v$process p, v$session s where s.sid = &trgtsid and s.paddr = p.addr and p.pid = l.pid and l.end_time is null and l.spid = s.process and l.start_time is not null -- and l.start_time = u.last_logon_date -- and l.session_number = u.session_number and l.user_id = u.user_id and u.user_id = a.user_id and p.pid = a.pid and l.start_time = (select max(l2.start_time) from applsys.fnd_logins l2 where l2.pid = l.pid) group by to_char (s.logon_time,'mm/dd/yy hh:mi:ssAM'), floor(s.last_call_et/3600)':' floor(mod (s.last_call_et,3600)/60)':' mod(mod(s.last_call_et,3600),60), u.user_name, u.description,a.time,s.module ' - ' a.user_form_name order by to_char(s.logon_time,'mm/dd/yy hh:mi:ssAM'),a.time; Query to get the server tiers active sessions select replace(s.machine,'HEADLANDS\',null) usersrvr, s.sid, to_char(s.logon_time,'mm/dd hh24:mi') logonat, floor(last_call_et/3600)':' floor(mod(last_call_et,3600)/60)':' mod(mod(last_call_et,3600),60) "LastCallET", s.osuser, nvl(s.module,p.program) sessprog, w.event, w.state, p.spid, decode(h.sid,null,'None',h.name) holdinglatch from v$session s, v$session_wait w, v$process p, v$latchholder h where type = 'USER' and status = 'ACTIVE' and s.sid = w.sid and s.paddr = p.addr and s.osuser is not null and s.sid = h.sid (+) order by 1,4