-- Failed / Error concurrent requests.. remove the comments if we want the time frame and concurrent program wise
-- Concurrent requests running for more than an hour as per sysdate
SELECT
fcr.request_id request_id,fcr.parent_request_id,
NVL (fu.description, fu.user_name) requested_by,
DECODE (fcp.concurrent_program_name,'FNDRSSUB', 'Request Set - ' || fcr.description, fcpt.user_concurrent_program_name) concurrent_program,
DECODE (fcr.phase_code,
'I', 'Inactive',
'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', 'Pending'),
'R', 'Running',
'C', 'Complete',
fcr.phase_code)
phase,
DECODE (
fcr.status_code,
'U', 'Disabled',
'W', 'Paused',
'X', 'Terminated',
'Z', 'Waiting',
'M', 'No Manager',
'Q', 'Standby',
'R', 'Normal',
'S', 'Suspended',
'T', 'Terminating',
'D', 'Cancelled',
'E', 'Error',
'F', 'Scheduled',
'G', 'Warning',
'H', 'On Hold',
'I', CASE
WHEN fcr.request_date < fcr.requested_start_date
THEN
'Scheduled'
ELSE
'Normal'
END,
'A', 'Waiting',
'B', 'Resuming',
'C', 'Normal',
fcr.status_code)
status,
fcr.argument_text,
TO_CHAR (fcr.request_date, 'DD-Mon-RRRR HH24:MI:SS') date_requested,
TO_CHAR (fcr.requested_start_date, 'DD-Mon-RRRR HH24:MI:SS')
requested_start_date,
TO_CHAR (fcr.actual_start_date, 'DD-Mon-RRRR HH24:MI:SS')
date_started,
TO_CHAR (fcr.actual_completion_date, 'DD-Mon-RRRR HH24:MI:SS')
date_completed,
(fcr.actual_completion_date-fcr.actual_start_date)*24*60*60 AS actual_Run_Time_Secs,
(fcr.actual_completion_date-fcr.request_date)*24*60*60 AS end_to_end_Secs,
(fcr.actual_start_date-fcr.request_date)*24*60*60 AS lag_time_secs,
fcr.oracle_process_id,
fcr.os_process_id,
fcr.pp_start_date AS OPP_Process_start_time,
fcr.pp_end_date AS OPP_Process_end_time,
fcr.logfile_node_name || ': ' || fcr.logfile_name logfile_name,
fcr.outfile_node_name || ': ' || fcr.outfile_name output_name,
fcr.completion_text
FROM fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_user fu
WHERE 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.last_update_date) >= trunc(SYSDATE-3)
--AND trunc(fcr.last_update_date) = '01-Jan-2023'
--AND fcpt.user_concurrent_program_name like '%<Concurrent Program Name>%' /* Given the concurrent program name, get the program run times as per sysdate */
--AND (fcr.actual_completion_date-fcr.actual_start_date)*24*60*60 > 3600 /* Concurrent requests running for more than an hour as per sysdate */
--AND (fcr.pp_end_date - fcr.pp_start_date)*24*60*60 > 1800 /* Concurrent requests spending more than an hour in OPP */
--AND fcr.status_code = 'E' /* Failed / Error concurrent requests as per sysdate */
--AND fcr.request_id='<request_id>'
--AND NVL (fu.description, fu.user_name)='XXXXXXX'
ORDER BY 1 desc
---------------------------------------------------------------------------------------------
To check Scheduled Concurrent Programs and Request Sets details
SELECT fcr.request_id,
DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) CONC_PROG_NAME,
argument_text PARAMETERS,
NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')) PROG_SCHEDULE_TYPE,
DECODE(NVL2(fcr.resubmit_interval,
'PERIODICALLY',
NVL2(fcr.release_class_id, 'ON SPECIFIC DAYS', 'ONCE')),
'PERIODICALLY',
'EVERY ' || fcr.resubmit_interval || ' ' ||
fcr.resubmit_interval_unit_code || ' FROM ' ||
fcr.resubmit_interval_type_code || ' OF PREV RUN',
'ONCE',
'AT :' ||
TO_CHAR(fcr.requested_start_date, 'DD-MON-RR HH24:MI'),
'EVERY: ' || fcrc.class_info) PROG_SCHEDULE,
fu.user_name USER_NAME,
requested_start_date START_DATE
FROM apps.fnd_concurrent_programs_tl fcpt,
apps.fnd_concurrent_requests fcr,
apps.fnd_user fu,
apps.fnd_conc_release_classes fcrc
WHERE fcpt.application_id = fcr.program_application_id
AND fcpt.concurrent_program_id = fcr.concurrent_program_id
AND fcr.requested_by = fu.user_id
AND fcr.phase_code = 'P'
AND fcr.requested_start_date > SYSDATE
AND fcpt.LANGUAGE = 'US'
AND fcrc.release_class_id(+) = fcr.release_class_id
AND fcrc.application_id(+) = fcr.release_class_app_id
and DECODE(fcpt.user_concurrent_program_name,
'Report Set',
'Report Set:' || fcr.description,
fcpt.user_concurrent_program_name) like '%XXXXXXXX%'
----------------------------------------------------------------------------------------------
To check the duplicated schedules of the same program with the same arguments
SELECT request_id, NAME, argument_text, user_name
FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name,
'Report Set', 'Report Set:' || cr.description,
cp.user_concurrent_program_name
) NAME, argument_text, fu.user_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu
WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id
AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US'
AND fu.user_name NOT LIKE 'PPG%') t1
WHERE EXISTS ( SELECT 1 FROM (SELECT cr.request_id,
DECODE (cp.user_concurrent_program_name, 'Report Set', 'Report Set:'
|| cr.description, cp.user_concurrent_program_name ) NAME, argument_text,
fu.user_name FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_requests cr,
apps.fnd_user fu WHERE cp.application_id = cr.program_application_id
AND cp.concurrent_program_id = cr.concurrent_program_id
AND cr.requested_by = fu.user_id AND cr.phase_code = 'P'
AND cr.requested_start_date > SYSDATE
AND cp.LANGUAGE = 'US' AND fu.user_name NOT LIKE 'PPG%') t2
WHERE t1.NAME = t2.NAME AND t1.argument_text = t2.argument_text
AND t1.user_name = t2.user_name
GROUP BY NAME, argument_text, user_name
HAVING COUNT (*) > 1)
ORDER BY user_name, NAME
----------------------------------------------------------------------------------------------
To check average pending time per request:
Please Note: Average pending time for a request is calculated like below:
("Highest of Requested_start_date or Date_submitted" - Actual_start_date ) /
Total requests
A Request can be in Pending state for variety of reasons like conflict with other
requests, improperly tuned managers (sleep seconds / cache size / number of
managers etc)
We can schedule this script to gather data regularly for historical analysis as we
normally purge the concurrent requests regularly.
SELECT TO_CHAR (actual_start_date, 'DD-MON-YYYY') DAY,
concurrent_queue_name,
(SUM ( ( actual_start_date
- (CASE
WHEN requested_start_date > request_date
THEN requested_start_date
ELSE request_date
END))* 24* 60* 60))
/ COUNT (*) "Wait_Time_per_Req_in_Secs"
FROM apps.fnd_concurrent_requests cr,
apps.fnd_concurrent_processes fcp,
apps.fnd_concurrent_queues fcq
WHERE cr.phase_code = 'C'
AND cr.actual_start_date IS NOT NULL
AND cr.requested_start_date IS NOT NULL
AND cr.controlling_manager = fcp.concurrent_process_id
AND fcp.queue_application_id = fcq.application_id
AND fcp.concurrent_queue_id = fcq.concurrent_queue_id
GROUP BY TO_CHAR (actual_start_date, 'DD-MON-YYYY'),
concurrent_queue_name
ORDER BY 2
----------------------------------------------------------------------------------------------
To check which manager is going to execute a program:
SELECT user_concurrent_program_name, user_concurrent_queue_name
FROM apps.fnd_concurrent_programs_tl cp,
apps.fnd_concurrent_queue_content cqc,
apps.fnd_concurrent_queues_tl cq
WHERE cqc.type_application_id(+) = cp.application_id
AND cqc.type_id(+) = cp.concurrent_program_id
AND cqc.type_code(+) = 'P'
AND cqc.include_flag(+) = 'I'
AND cp.LANGUAGE = 'US'
AND cp.user_concurrent_program_name =
'&USER_CONCURRENT_PROGRAM_NAME' AND NVL (cqc.concurrent_queue_id, 0)
= cq.concurrent_queue_id
AND NVL (cqc.queue_application_id, 0) = cq.application_id
AND cq.LANGUAGE = 'US'
----------------------------------------------------------------------------------------------
To see all the pending / Running requests per each manager wise :
SELECT request_id, phase_code, status_code, user_name,
user_concurrent_queue_name
FROM apps.fnd_concurrent_worker_requests cwr,
apps.fnd_concurrent_queues_tl cq,
apps.fnd_user fu
WHERE (cwr.phase_code = 'P' OR cwr.phase_code = 'R')
AND cwr.hold_flag != 'Y'
AND cwr.requested_start_date <= SYSDATE
AND cwr.concurrent_queue_id = cq.concurrent_queue_id
AND cwr.queue_application_id = cq.application_id
AND cq.LANGUAGE = 'US'
AND cwr.requested_by = fu.user_id
ORDER BY 5
----------------------------------------------------------------------------------------------
To check the incompatibilities between the programs:
SELECT a2.application_name, a1.user_concurrent_program_name,
DECODE (running_type,
'P', 'Program', 'S', 'Request set', ‘UNKNOWN’) "Type",
b2.application_name "Incompatible App",
b1.user_concurrent_program_name "Incompatible_Prog",
DECODE (to_run_type, 'P', 'Program', 'S', 'Request set', ‘UNKNOWN’)
incompatible_type FROM apps.fnd_concurrent_program_serial cps,
apps.fnd_concurrent_programs_tl a1, apps.fnd_concurrent_programs_tl b1,
apps.fnd_application_tl a2, apps.fnd_application_tl b2
WHERE a1.application_id = cps.running_application_id
AND a1.concurrent_program_id = cps.running_concurrent_program_id
AND a2.application_id = cps.running_application_id
AND b1.application_id = cps.to_run_application_id
AND b1.concurrent_program_id = cps.to_run_concurrent_program_id
AND b2.application_id = cps.to_run_application_id
AND a1.language = 'US' AND a2.language = 'US'
AND b1.language = 'US' AND b2.language = 'US'
------------------------------------------------------------------------------------------------
To Find The Concurrent Request Based On SQLID
SELECT fcr.request_id,
fcp.user_concurrent_program_name,
vsq.sql_text,
vsq.SQL_ID
FROM fnd_concurrent_requests fcr,
v$process vp,
v$session vs,
v$sql vsq,
fnd_concurrent_programs_vl fcp
WHERE 1=1
AND vsq.SQL_ID = '&sqlid'
AND fcr.oracle_process_id = vp.spid
AND vs.sql_address = vsq.address(+)
AND vs.paddr = vp.addr
AND fcr.concurrent_program_id = fcp.concurrent_program_id;
------------------------------------------------------------------------------------------------
To find All The Programs Assigned to the Manager Specialization Rules
select fcqv.user_concurrent_queue_name,
decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",
'Program' "Rule Type",
tab.user_concurrent_program_name "Entity"
from fnd_concurrent_queues_vl fcqv,
fnd_concurrent_queue_content fcqc,
fnd_concurrent_programs_vl tab
where fcqc.type_code = 'P'
and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id
and fcqc.type_id=tab.concurrent_program_id
and fcqc.type_application_id = tab.application_id
UNION
select fcqv.user_concurrent_queue_name,
decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",
'Combined Rule' "Rule Type",
tab.complex_rule_name "Entity"
from fnd_concurrent_queues_vl fcqv,
fnd_concurrent_queue_content fcqc,
fnd_concurrent_complex_rules tab
where fcqc.type_code = 'C'
and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id
and fcqc.type_id=tab.complex_rule_id
and fcqc.type_application_id = tab.application_id
UNION
select fcqv.user_concurrent_queue_name,
decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",
'Oracle ID' "Rule Type",
tab.oracle_username "Entity"
from fnd_concurrent_queues_vl fcqv,
fnd_concurrent_queue_content fcqc,
fnd_oracle_userid tab
where fcqc.type_code = 'O'
and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id
and fcqc.type_id=tab.oracle_id
UNION
select fcqv.user_concurrent_queue_name,
decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",
'User' "Rule Type",
tab.user_name "Entity"
from fnd_concurrent_queues_vl fcqv,
fnd_concurrent_queue_content fcqc,
fnd_user tab
where fcqc.type_code = 'U'
and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id
and fcqc.type_id=tab.user_id
UNION
select fcqv.user_concurrent_queue_name,
decode(fcqc.include_flag, 'I','Include','E','Exclude','X') "Include_Flag",
'Request Type' "Rule Type",
tab.request_class_name "Entity"
from fnd_concurrent_queues_vl fcqv,
fnd_concurrent_queue_content fcqc,
FND_CONCURRENT_REQUEST_CLASS tab
where fcqc.type_code = 'R'
and fcqv.concurrent_queue_id=fcqc.concurrent_queue_id
and fcqc.type_id=tab.request_class_id
and fcqc.type_application_id = tab.application_id;
--------------------------------------------------------------------------------------------------
-- Concurrent program Reponsibility group
SELECT cpt.user_concurrent_program_name "Concurrent Program Name",
DECODE(rgu.request_unit_type,
'P', 'Program',
'S', 'Set',
rgu.request_unit_type) "Unit Type",
cp.concurrent_program_name "Concurrent Program Short Name",
rg.application_id "Application ID",
rg.request_group_name "Request Group Name",
fat.application_name "Application Name",
fa.application_short_name "Application Short Name",
fa.basepath "Basepath",
rg.*
FROM apps.fnd_request_groups rg,
apps.fnd_request_group_units rgu,
apps.fnd_concurrent_programs cp,
apps.fnd_concurrent_programs_tl cpt,
apps.fnd_application fa,
apps.fnd_application_tl fat
WHERE rg.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = cp.concurrent_program_id
AND cp.concurrent_program_id = cpt.concurrent_program_id
AND rg.application_id = fat.application_id
AND fa.application_id = fat.application_id
--and fa.application_id =20083
AND cpt.language = USERENV('LANG')
AND fat.language = USERENV('LANG')
AND cpt.user_concurrent_program_name like '%Diag%Apps%'
--and rg.request_group_name='XXXXXXX'
-------------------------------------------------------------------------------------------------
-- Find the concurrent program name from the short name or vice versa
SELECT fcpl.user_concurrent_program_name "Concurrent Program Name",
fcp.concurrent_program_name "Short Name",
fa.application_short_name "Module",
fe.executable_name "Executable",
fe.execution_file_name "Executable Short Name",
DECODE (fcp.execution_method_code,
'X', 'FlexRpt',
'F', 'FlexSql',
'H', 'Host',
'S', 'Immediate',
'K', 'Java Concurrent Program',
'J', 'Java Stored Procedure',
'M', 'Multi Language Function',
'P', 'Oracle Reports',
'I', 'PL/SQL Stored Procedure',
'E', 'Perl Concurrent Program',
'B', 'Request Set Stage Function',
'L', 'SQL*Loader',
'Q', 'SQL*Plus',
'R', 'SQL*Report',
'Z', 'Shutdown Callback',
'A', 'Spawned') "Execution Method",
fcpl.description "Program Description",
fdfcuv.column_seq_num "Column Seq Number",
fdfcuv.end_user_column_name "Parameter Name",
fdfcuv.form_left_prompt "Prompt",
fdfcuv.enabled_flag " Enabled Flag",
fdfcuv.required_flag "Required Flag",
fdfcuv.display_flag "Display Flag",
ffvs.flex_value_set_name "Value Set Name"
FROM fnd_executables fe,
fnd_concurrent_programs_tl fcpl,
fnd_application fa,
fnd_concurrent_programs fcp,
fnd_descr_flex_col_usage_vl fdfcuv,
fnd_flex_value_sets ffvs,
fnd_lookup_values flv
WHERE 1 = 1
AND fe.executable_id = fcp.executable_id
AND fcpl.application_id = fa.application_id
AND fcpl.concurrent_program_id = fcp.concurrent_program_id -- -- change Concurrent program name here..
--AND user_concurrent_program_name = 'XXXXXX' -- change Concurrent program name here..
AND fcp.concurrent_program_name='XXXXXXX'
AND fdfcuv.descriptive_flexfield_name =
'$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = fdfcuv.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = fdfcuv.default_type
AND fcpl.LANGUAGE = 'US'
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY fdfcuv.column_seq_num;
No comments:
Post a Comment