Where the Concurrent Manager kept himself Busy

We come across this question many time what Concurrent manager was doing. So here is one script which will help you to find this:

rem ------------------------------------------------------------------
rem Filename: cmup.sql
rem Purpose: Concurrent Manager Usage by Program /day
rem - Can only run from sqlplus
rem -----------------------------------------------------------------------

cl scr
set feedback off
set verify off
set line 1000
set pagesize 1111
set head off
spool d:\cmup.csv
prompt Concurent Program, Program, Total Duration(Min), Avg Duration(Min), Min Duration(Min), Max Duration(Min), Times_Run
select /*+ ALL_ROWS */
fcqtl.user_concurrent_queue_name', 'fcptl.program','round(sum(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(avg(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(min(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'round(max(greatest(actual_completion_date - actual_start_date,0))*60*24, 2)', 'count(*)
from
fnd_concurrent_programs fcp,
FND_CONC_REQ_SUMMARY_V fcptl,
fnd_concurrent_processes fcproc,
fnd_concurrent_queues_tl fcqtl,
fnd_concurrent_queues fcq
where
fcptl.phase_code = 'C'
and actual_completion_date is not null
and actual_start_date is not null
and fcq.concurrent_queue_id = fcproc.concurrent_queue_id
and fcq.application_id =fcproc.queue_application_id
and fcq.manager_type = 1
and fcptl.controlling_manager = fcproc.concurrent_process_id
and fcptl.program_application_id =fcp.application_id
and fcptl.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_name not in ('ACTIVATE','ABORT','DEACTIVATE','VERIFY')
and fcproc.queue_application_id = fcqtl.application_id
and fcproc.concurrent_queue_id = fcqtl.concurrent_queue_id
and fcqtl.language=userenv('LANG')
and actual_start_date >= trunc(sysdate - 1) and actual_start_date <= trunc(sysdate)
group by
fcqtl.user_concurrent_queue_name,
fcptl.program
order by
round(sum(greatest(actual_completion_date - actual_start_date,0))*60*24, 2) desc,
fcqtl.user_concurrent_queue_name asc
/
spool off

Happy Troubleshooting !!!