Queries to Monitor Concurrent Requests Daily
Query 1 (CC Manager program wise exec report)
Select to_char(actual_completion_date, 'HH24') "Hour",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Standard Manager',1,0)) "Standard Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Long Running Jobs',1,0)) "Long Running Jobs",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'OE Manager',1,0)) "OE Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'HR',1,0)) "HR",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Long Running Jobs 2',1,0)) "Long Running Jobs 2",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PO Output',1,0)) "PO Output",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Workflow',1,0)) "Workflow",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Inventory Manager',1,0)) "Inventory Manager",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'INV Remote Procedure Manager',1,0)) "INV Remote Procedure Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PA Streamline Manager',1,0)) "PA Streamline Manager"
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PC Dedicated Manager',1,0)) "PC Dedicated Manager",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PO Document Approval Manager',1,0)) "PO Document Approval Manager"
from APPLSYS.fnd_concurrent_requests a,
fnd_concurrent_processes b,
fnd_concurrent_queues_vl c,
fnd_concurrent_programs_tl d
where b.CONCURRENT_QUEUE_ID = c.CONCURRENT_QUEUE_ID and
b.CONCURRENT_PROCESS_ID = a.controlling_manager
and a.CONCURRENT_PROGRAM_ID=d.CONCURRENT_PROGRAM_ID
--and d.user_concurrent_program_name = 'Workflow Background Process'
and TRUNC(a.ACTUAL_START_DATE) = TO_DATE(UPPER('&&enddt'),'DD-MON-YY')
group by to_char(a.actual_completion_date, 'HH24')
Order by to_char(a.actual_completion_date, 'HH24');
Query2 (CCR Running more than 20 min's)
select fcr.request_id,rtrim(fct.user_concurrent_program_name) ccr_name,
fcu.user_name,
to_char(fcr.actual_start_date,'mm/dd/yyyy:HH24:mm:ss') start_date,
to_char(fcr.actual_completion_date,'mm/dd/yyyy:HH24:mm:ss') completion_date,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24, 24 ) ) hrs,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 60 ) ) Min,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60 * 60, 60 ) ) sec
from APPLSYS.fnd_concurrent_requests fcr, APPLSYS.fnd_concurrent_programs fcp,
APPLSYS.fnd_concurrent_programs_tl fct, APPLSYS.fnd_user fcu
where fcp.concurrent_program_id=fcr.CONCURRENT_PROGRAM_ID and
fcp.concurrent_program_id=fct.CONCURRENT_PROGRAM_ID and
fcr.REQUESTED_BY=fcu.user_id and fcr.actual_start_date > ( sysdate - 1 ) and
(fcr.actual_completion_date - fcr.actual_start_date) * (24 * 60) > = 20
order by 7 desc,8 desc
Query 3 (CCR Running within 5 Minutes Details)
SELECT TO_CHAR (a.actual_completion_date , 'mm/dd/yyyy:HH24')||
case
when to_number(to_char(a.actual_completion_date,'mi')) between 01 and 05 then
':05'
when to_number(to_char(a.actual_completion_date,'mi')) between 06 and 10 then
':10'
when to_number(to_char(a.actual_completion_date,'mi')) between 11 and 15 then
':15'
when to_number(to_char(a.actual_completion_date,'mi')) between 16 and 20 then
':20'
when to_number(to_char(a.actual_completion_date,'mi')) between 21 and 25 then
':25'
when to_number(to_char(a.actual_completion_date,'mi')) between 26 and 30 then
':30'
when to_number(to_char(a.actual_completion_date,'mi')) between 31 and 35 then
':35'
when to_number(to_char(a.actual_completion_date,'mi')) between 36 and 40 then
':40'
when to_number(to_char(a.actual_completion_date,'mi')) between 41 and 45 then
':45'
when to_number(to_char(a.actual_completion_date,'mi')) between 46 and 50 then
':50'
when to_number(to_char(a.actual_completion_date,'mi')) between 51 and 55 then
':55'
else
':00'
end time,
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Standard Manager',1,0)) "Standard Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Long Running Jobs',1,0)) "Long Running Jobs",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'OE Manager',1,0)) "OE Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'HR',1,0)) "HR",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Long Running Jobs 2',1,0)) "Long Running Jobs 2",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PO Output',1,0)) "PO Output",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Workflow',1,0)) "Workflow",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'Inventory Manager',1,0)) "Inventory Manager",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'INV Remote Procedure Manager',1,0)) "INV Remote Procedure Manager",
sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PA Streamline Manager',1,0)) "PA Streamline Manager"
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PC Dedicated Manager',1,0)) "PC Dedicated Manager",
--sum(decode(c.USER_CONCURRENT_QUEUE_NAME, 'PO Document Approval Manager',1,0)) "PO Document Approval Manager"
from APPLSYS.fnd_concurrent_requests a,
fnd_concurrent_processes b,
fnd_concurrent_queues_vl c,
fnd_concurrent_programs_tl d
where b.CONCURRENT_QUEUE_ID = c.CONCURRENT_QUEUE_ID and
b.CONCURRENT_PROCESS_ID = a.controlling_manager
and a.CONCURRENT_PROGRAM_ID=d.CONCURRENT_PROGRAM_ID
--and d.user_concurrent_program_name = 'Workflow Background Process'
and TRUNC(a.ACTUAL_START_DATE) = TO_DATE(UPPER('&&enddt'),'DD-MON-YY')
GROUP BY TO_CHAR (a.actual_completion_date, 'mm/dd/yyyy:HH24')||
case
when to_number(to_char(a.actual_completion_date,'mi')) between 01 and 05 then
':05'
when to_number(to_char(a.actual_completion_date,'mi')) between 06 and 10 then
':10'
when to_number(to_char(a.actual_completion_date,'mi')) between 11 and 15 then
':15'
when to_number(to_char(a.actual_completion_date,'mi')) between 16 and 20 then
':20'
when to_number(to_char(a.actual_completion_date,'mi')) between 21 and 25 then
':25'
when to_number(to_char(a.actual_completion_date,'mi')) between 26 and 30 then
':30'
when to_number(to_char(a.actual_completion_date,'mi')) between 31 and 35 then
':35'
when to_number(to_char(a.actual_completion_date,'mi')) between 36 and 40 then
':40'
when to_number(to_char(a.actual_completion_date,'mi')) between 41 and 45 then
':45'
when to_number(to_char(a.actual_completion_date,'mi')) between 46 and 50 then
':50'
when to_number(to_char(a.actual_completion_date,'mi')) between 51 and 55 then
':55'
else
':00'
end
Order by TO_CHAR (a.actual_completion_date, 'mm/dd/yyyy:HH24')||
case
when to_number(to_char(a.actual_completion_date,'mi')) between 01 and 05 then
':05'
when to_number(to_char(a.actual_completion_date,'mi')) between 06 and 10 then
':10'
when to_number(to_char(a.actual_completion_date,'mi')) between 11 and 15 then
':15'
when to_number(to_char(a.actual_completion_date,'mi')) between 16 and 20 then
':20'
when to_number(to_char(a.actual_completion_date,'mi')) between 21 and 25 then
':25'
when to_number(to_char(a.actual_completion_date,'mi')) between 26 and 30 then
':30'
when to_number(to_char(a.actual_completion_date,'mi')) between 31 and 35 then
':35'
when to_number(to_char(a.actual_completion_date,'mi')) between 36 and 40 then
':40'
when to_number(to_char(a.actual_completion_date,'mi')) between 41 and 45 then
':45'
when to_number(to_char(a.actual_completion_date,'mi')) between 46 and 50 then
':50'
when to_number(to_char(a.actual_completion_date,'mi')) between 51 and 55 then
':55'
else
':00'
end ;
Query 4 (Error out ccr Details)
SELECT a.request_id request_id,SUBSTR(a.user_concurrent_program_name,1,50) name, TO_CHAR(a.actual_start_date,'dd-mon-yy:hh24:mi:ss') st_time,
TO_CHAR(a.actual_completion_date,'dd-mon-yy:hh24:mi:ss') end_time,requestor,DECODE(a.phase_code, 'R','Running', 'P','Inactive', 'C','Completed', a.phase_code) phase_code,
DECODE(a.status_code, 'E','Error', 'C','Normal', 'X','Terminated', 'Q','On Hold', 'D','Cancelled', 'G','Warning', 'R','Normal', 'W','Paused', a.status_code) status_code
FROM apps.fnd_conc_req_summary_v a WHERE TRUNC(actual_completion_date) >= TRUNC(SYSDATE -1) AND a.status_code IN ('E','X','D')ORDER BY actual_start_date;
Query 5: CCR run for a given date:
select fcr.request_id,rtrim(fct.user_concurrent_program_name) ccr_name,
fcu.user_name,
to_char(fcr.actual_start_date,'mm/dd/yyyy:HH24:mm:ss') start_date,
to_char(fcr.actual_completion_date,'mm/dd/yyyy:HH24:mm:ss') completion_date,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24, 24 ) ) hrs,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60, 60 ) ) Min,
trunc( mod( (fcr.actual_completion_date - fcr.actual_start_date) * 24 * 60 * 60, 60 ) ) sec
from APPLSYS.fnd_concurrent_requests fcr, APPLSYS.fnd_concurrent_programs fcp,
APPLSYS.fnd_concurrent_programs_tl fct, APPLSYS.fnd_user fcu
where fcp.concurrent_program_id=fcr.CONCURRENT_PROGRAM_ID and
fcp.concurrent_program_id=fct.CONCURRENT_PROGRAM_ID and
fcr.REQUESTED_BY=fcu.user_id and TRUNC(REQUEST_DATE ) = TO_DATE(UPPER('&&enddt'),'DD-MON-YY')
order by 4 asc
No comments:
Post a Comment