Total Request by user

select fu.user_name,COUNT(*) COUNT from APPLSYS.fnd_Concurrent_requests a,apps.fnd_user fu
where  a.requested_by = fu.user_id
AND a.phase_code = 'C'
AND a.status_code = 'C'
AND a.actual_start_date  like ('%09-MAY-12%')
group by user_name;

Daily Status

 
   --- 1 -- log sequence
select * from v$archived_log order by stamp desc;
-----


---2 -- Workflow Mailer Status

SELECT component_name as Component, component_status as Status FROM apps.fnd_svc_components WHERE component_type = 'WF_MAILER';

----

---3 ---Gather schema statistic For Last week  For Apps schema :

select count(1)
from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r
where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id
and p.user_concurrent_program_name in (
'OnDemand Gather Schema Statistics',
'Gather Schema Statistics',
'Gather Schema Statistics (IT_ANALYZE)'
)
and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-7)


---Gather schema statistic For Last week Non-Apps schemas :

set pages 0 echo off head off termout off verify off feed off lines 1000
select count(1)
from apps.fnd_concurrent_programs_vl p , apps.fnd_concurrent_requests r
where r.concurrent_program_id = p.concurrent_program_id
and r.program_application_id = p.application_id
and p.user_concurrent_program_name in (
'Gather Statistics for Non-Apps Schema'
)
and (r.phase_code = 'C' and r.status_code= 'C' and r.actual_start_date >= sysdate-7)

-----------

---4 -----locked objects


SELECT c.owner,b.client_identifier,c.object_name,c.object_type, b.SID, b.serial#, b.status,
       b.osuser, b.machine
  FROM v$locked_object a, v$session b, dba_objects c
 WHERE b.SID = a.session_id AND a.object_id = c.object_id;


 ---5 -------check long running concurrent request

SELECT a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.phase_code,a.status_code,actual_start_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
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
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'R'
AND a.status_code = 'R'
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 a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;


---6 -----Checking status of all the Concurrent Managers


select decode(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','AMSDMIN','Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager','FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager','FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER','Transportation Manager','IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session','INVMGR','Inventory Manager','INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PA Streamline Manager','PODAMGR','PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager','STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service','WFMLRSVC','Workflow Mailer Service','WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC','SFM Application Monitoring Service','XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service','XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service','XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service','XDP_Q_ORDER_SVC','SFM Order Queue Service','XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name", max_processes as "TARGET Processes", running_processes as "ACTUAL Processes" from apps.fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');


---7 ----- Invalid Objects.

select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where STATUS='INVALID';

--8- Concurrent Request Count 



SELECT   fcpt.user_concurrent_program_name program_name
        ,COUNT (1) request_count
        ,MIN ((ROUND (fcr.actual_completion_date - fcr.actual_start_date, 3) * 1440)) "MIN_TIME Mins"
        ,MAX ((ROUND (fcr.actual_completion_date - fcr.actual_start_date, 3) * 1440)) "MAX_TIME Mins"
        ,ROUND (AVG ((ROUND (fcr.actual_completion_date - fcr.actual_start_date, 3) * 1440)), 2) "AVG_TIME Mins"
    FROM apps.fnd_concurrent_requests fcr
        ,apps.fnd_concurrent_programs fcp
        ,apps.fnd_concurrent_programs_tl fcpt
        ,apps.fnd_user fu
   WHERE 1 = 1
     AND fcr.concurrent_program_id = fcp.concurrent_program_id
     AND fcp.concurrent_program_id = fcpt.concurrent_program_id
     AND fcr.program_application_id = fcp.application_id
     AND fcp.application_id = fcpt.application_id
     AND fcr.requested_by = fu.user_id
     AND fcpt.LANGUAGE = 'US'
     AND trunc(fcr.actual_start_date) >= '05-May-2012'
     AND trunc(fcr.actual_start_date) <= '11-May-2012'
    AND ROUND ((fcr.actual_completion_date - fcr.actual_start_date), 3) * 1440 > 0
GROUP BY fcpt.user_concurrent_program_name
order by 5 desc

Responsibility

---- check the who have System Administrator Responsibility.

SELECT FURGA.USER_ID
, FU.USER_NAME
, FURGA.RESPONSIBILITY_ID
, FRTL.RESPONSIBILITY_NAME
, FURGA.RESPONSIBILITY_APPLICATION_ID
, FA.APPLICATION_SHORT_NAME
, FURGA.SECURITY_GROUP_ID
, FSG.SECURITY_GROUP_KEY
, FURGA.START_DATE
, FURGA.END_DATE
, FURGA.CREATED_BY
, FUCB.USER_NAME
, FURGA.CREATION_DATE
, FURGA.LAST_UPDATED_BY
, FULUB.USER_NAME
, FURGA.LAST_UPDATE_DATE
, FURGA.LAST_UPDATE_LOGIN
, FULUL.USER_NAME
FROM
apps.FND_USER_RESP_GROUPS_ALL FURGA,
apps.FND_USER FU,
apps.FND_USER FUCB,
apps.FND_USER FULUB,
apps.FND_USER FULUL,
apps.FND_APPLICATION FA,
apps.FND_RESPONSIBILITY_TL FRTL,
apps.FND_SECURITY_GROUPS FSG
WHERE
FURGA.USER_ID = FU.USER_ID (+)
AND FURGA.CREATED_BY = FUCB.USER_ID (+)
and FRTL.RESPONSIBILITY_NAME='System Administrator'
AND FURGA.LAST_UPDATED_BY = FULUB.USER_ID (+)
AND FURGA.LAST_UPDATE_LOGIN = FULUL.USER_ID (+)
AND FURGA.RESPONSIBILITY_APPLICATION_ID = FA.APPLICATION_ID (+)
AND FURGA.RESPONSIBILITY_ID = FRTL.RESPONSIBILITY_ID (+)
AND FRTL.LANGUAGE = 'US'
AND FURGA.SECURITY_GROUP_ID = FSG.SECURITY_GROUP_ID (+)
ORDER BY START_DATE;

Check the long running concurrent request

 --------check long running concurrent request


SELECT a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.phase_code,a.status_code,actual_start_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
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
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'R'
AND a.status_code = 'R'
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 a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;







-----------
SELECT a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name
,a.phase_code,a.status_code,actual_start_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
,(SELECT avg(nvl(a2.actual_completion_date-a2.actual_start_date,0))*1440 avg_run_time
FROM APPLSYS.fnd_Concurrent_requests a2,
APPLSYS.fnd_concurrent_programs c2
WHERE c2.concurrent_program_id = c.concurrent_program_id
AND a2.concurrent_program_id = c2.concurrent_program_id
AND a2.program_application_id = c2.application_id
AND a2.phase_code || '' = 'C') avg_mins
,round((actual_completion_date - requested_start_date),2) * 24 duration_in_hours
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
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'C'
AND a.status_code = 'C'
AND a.actual_start_date >= '20-APR-12' and a.actual_start_date <= '02-MAY-12'
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 a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
ORDER BY a.actual_start_date DESC;


-----------
SELECT a.request_id
,frt.responsibility_name
,c.concurrent_program_name || ': ' || ctl.user_concurrent_program_name "Concurrent Program"
,a.phase_code,a.status_code,
actual_start_date
, fu.user_name
,(nvl(actual_completion_date,sysdate)-actual_start_date)*1440 mins
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
,apps.fnd_user fu
,apps.FND_RESPONSIBILITY_TL frt
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 a.phase_code = 'R'
--AND a.status_code = 'R'
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 a.requested_by = fu.user_id
AND a.responsibility_id = frt.responsibility_id
and trunc(actual_start_date)> trunc(sysdate-18)
and (nvl(actual_completion_date,sysdate)-actual_start_date)*1440 > 20
ORDER BY mins desc;