Important SQL scripts for Concurrent Request ID Monitoring and Analyzing
1) requests.sql
-- Find all child requests for a request set (OR if no children, just get details on an individual request)
-- REQUIRED VALUE - Enter the Request ID that launched the Request set being investigated
select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
fcr.request_id "Request ID",
substr(DECODE (FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
round((fcr.actual_completion_date - fcr.actual_start_date)*1440,2) "Elapsed Time",
oracle_process_id "Trace File ID" ,
fcr.phase_code "Phase",
fcr.status_code "Status",
to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",
round((fcr.actual_start_date - fcr.request_date)*1440,1) "Delay",
to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",
to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
fcr.argument_text "Parameters"
from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
fcr1.request_id
from fnd_concurrent_requests fcr1
where 1=1
start with fcr1.request_id = &parent_request_id
connect by prior fcr1.request_id = fcr1.parent_request_id) x,
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl
where fcr.request_id = x.request_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcptl.application_id
and fcp.concurrent_program_id = fcptl.concurrent_program_id
and fcptl.language = 'US'
order by 1;
-- end of requests.sql
2) requests_N.sql - find the requests for a Request Program name(s)
-- REQUIRED VALUE - Enter the NAME when prompted -
-- Examples:
-- Suggest using % like Launch%Plan% for finding Launch Supply Chain Plan
-- OR use %Data%Pull% for all requests for Planning Data Pull and Planning Data Pull Workers
-- NOTE: This IS CASE SENSiTiVe
-- this is valuable when you have performance degrades over time for specific requests.
select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
fcr.request_id "Request ID",
substr(DECODE (FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
round((fcr.actual_completion_date - fcr.actual_start_date)*1440,2) "Elapsed Time",
oracle_process_id "Trace File ID" ,
fcr.phase_code "Phase",
fcr.status_code "Status",
to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",
round((fcr.actual_start_date - fcr.request_date)*1440,1) "Delay",
to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",
to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
fcr.argument_text "Parameters"
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcptl.application_id
and fcp.concurrent_program_id = fcptl.concurrent_program_id
and fcptl.language = 'US'
and DECODE (FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')')
like '&Name' -- ENTER THE NAME HERE Suggest using % like Launch%Plan% for finding Launch Supply Chain Plan
order by 1 desc;
-- end of requests_N.sql
3) requests_t.sql - -- Find request run during a specific time period
-- this is good for finding what was running on the system over a period of time when performance degrades
-- Also a good idea to have DBA supply the AWR report for time period when performance was poor
-- REQUIRED VALUES Enter the start_time and end_time when prompted
-- Date format example for start_time 16-DEC-2012 04:00:00
-- Date format example for end_time 16-DEC-20012 11:00:00
-- The example above will find all requests that launched between 4 am and 11 am on 16-DEC-2012
select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
fcr.request_id "Request ID",
substr(DECODE (FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80)"Program Name",
fcr.phase_code "Phase",
fcr.status_code "Status",
round((fcr.actual_completion_date - fcr.actual_start_date)*1440,2) "Elapsed Time",
oracle_process_id "Trace File ID" ,
to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",
round((fcr.actual_start_date - fcr.request_date)*1440,1) "Delay",
to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",
to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
fcr.argument_text "Parameters"
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcptl.application_id
and fcp.concurrent_program_id = fcptl.concurrent_program_id
and fcptl.language = 'US'
and fcr.actual_start_date
between
to_date('01-AUG-2018 04:00:00','DD-MON-YYYY HH24:MI:SS') --ENTER FROM DATE/TIME like 16-DEC-2017 04:00:00
and
to_date('31-DEC-2018 04:00:00','DD-MON-YYYY HH24:MI:SS') -- ENTER TO DATE/TIME like 16-DEC-2017 11:00:00
-- AND FCP.APPLICATION_ID in (724,723) -- isolates ASCP (MSC, MSO) programs -- or other as required **
-- AND fcr.requested_by = &user_id -- can be used if you want to isolate by user_id from table fnd_user
order by 1 desc;
-- end of requests_T.sql
4) SQL to find application_id
SELECT P.APPLICATION_ID,
decode(P.STATUS, 'I', 'Yes', 'S', 'Shared', 'N', 'No', P.status) inst_status,
A.APPLICATION_SHORT_NAME
FROM FND_PRODUCT_INSTALLATIONS P, FND_APPLICATION A
WHERE A.APPLICATION_ID=P.APPLICATION_ID;
5) requests_NT.sql - Find request run during a specific Request Name DURING a specific time period - Edit times below
-- this is very valuable when you have performance degrades over time for specific requests.
-- REQUIRED VALUES
-- 1. ENTER THE NAME when prompted - Suggest using % like Launch%Plan% for finding Launch Supply Chain Plan
---- NOTE: This IS CASE SENSiTiVe
-- 2. Enter the start_time and end_time when prompted
-- 2.1 Date format example for start_time 01-JAN-2012 00:00:00
-- 2.2 Date format example for end_time 31-JAN-2012 23:59:00
-- The example above will find all requests for Launch%Plan% that launched for 31 day range 01-JAN-12 to 31-JAN-12
select /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
fcr.request_id "Request ID",
substr(DECODE (FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')'),1,80) "Program Name",
fcr.phase_code "Phase",
fcr.status_code "Status",
round((fcr.actual_completion_date - fcr.actual_start_date)*1440,2) "Elapsed Time",
oracle_process_id "Trace File ID",
to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",
round((fcr.actual_start_date - fcr.request_date)*1440,1) "Delay",
to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",
to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
substr(fcr.argument_text,1,200) "Parameters"
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcptl.application_id
and fcp.concurrent_program_id = fcptl.concurrent_program_id
and fcptl.language = 'US'
and DECODE (FCR.DESCRIPTION, NULL,
FCPTL.USER_CONCURRENT_PROGRAM_NAME,
FCR.DESCRIPTION||' ('||FCPTL.USER_CONCURRENT_PROGRAM_NAME||')')
like '%Data%Pull%%' -- ENTER THE NAME HERE
and fcr.actual_start_date
BETWEEN
to_date('09-MAR-2018 00:00:00','DD-MON-YYYY HH24:MI:SS') --ENTER FROM DATE/TIME like 01-JAN-2017 00:00:00
and
to_date('31-DEC-2018 23:59:00','DD-MON-YYYY HH24:MI:SS') -- ENTER TO DATE/TIME like 31-JAN-2017 23:59:00
order by 1 desc;
-- end of requests_NT.sql
6) Find TMP log files when log is not written by the request process
-- When requests use this TMP file process, then this can help determine cause of failure
-- REQUIRED VALUES
-- Enter request ID that failed to provide a log file to get location of TMP file created while the request is running
select
c.value || '/' || p.plsql_log
from fnd_concurrent_processes P,
fnd_concurrent_requests R,
fnd_env_context C
where R.controlling_manager=P.concurrent_process_id
and P.concurrent_process_id=C.concurrent_process_id
and c.variable_name='APPLPTMP'
and r.request_id = &error_request_id
-- end requests_TMP.sql
7) requests_RAC.sql
-- Includes node information to know which RAC DB Node processed a request - refer to Note 279156.1
-- REQUIRED VALUE - Enter the Request ID that launched the Request set being investigated
SELECT
/*+ ORDERED USE_NL(x fcr fcp fcptl)*/
SUBSTR(fcr.request_id,1,10) "Request ID"
, SUBSTR(parent_request_id,1,10) "Parent ID"
, SUBSTR(fcptl.user_concurrent_program_name,1,40) "Program Name"
, FCPRC.NODE_NAME "DB Node" -- this is node that processed the request
, FCR.OUTFILE_NODE_NAME "Output file Node" -- this is the node where the output files were processed
, fcr.phase_code
, fcr.status_code
, SUBSTR(TO_CHAR(fcr.actual_start_date,'DD-MON HH24:MI:SS'),1,16) "Start Time"
, SUBSTR(TO_CHAR(fcr.actual_completion_date, 'DD-MON HH24:MI:SS'),1,16) "End Time"
, TO_CHAR((fcr.actual_completion_date - fcr.actual_start_date)*1440,'9999.00') "Elapsed"
, SUBSTR(fcr.oracle_process_id,1,10) "Trace ID"
, FCR.COMPLETION_TEXT
, 'cp ' || FCR.LOGFILE_NAME || ' .' LOGFILE
, 'cp ' || FCR.OUTFILE_NAME || ' .' OUTFILE
, 'cp ' || FEC.value || '/' || FCPRC.PLSQL_LOG || ' .' TMPLOG
, FCR.ARGUMENT_TEXT "Parameters"
FROM
(SELECT
/*+ index (fcr1 fnd_concurrent_requests_n3) */
fcr1.request_id
FROM
apps.fnd_concurrent_requests fcr1
WHERE
1 =1
START WITH fcr1.request_id =
(SELECT -- walk up the request family tree to the root
MIN(fcr2.request_id) root
FROM
apps.fnd_concurrent_requests fcr2
CONNECT BY fcr2.request_id = prior fcr2.parent_request_id
start with FCR2.REQUEST_ID = &Request_ID
-- amp child_request_id
) -- decending from the root, select all of the requests in the family
CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id
) x
, apps.fnd_concurrent_requests fcr
, apps.fnd_concurrent_programs fcp
, APPS.FND_CONCURRENT_PROGRAMS_TL FCPTL
, APPS.FND_ENV_CONTEXT FEC
, APPS.FND_CONCURRENT_PROCESSES FCPRC
WHERE
fcr.request_id = x.request_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id = fcptl.application_id
AND fcp.concurrent_program_id = fcptl.concurrent_program_id
and FCPTL.LANGUAGE = 'US'
and FEC.VARIABLE_NAME = 'APPLPTMP'
and FCR.CONTROLLING_MANAGER = FCPRC.CONCURRENT_PROCESS_ID
and FCPRC.CONCURRENT_PROCESS_ID = FEC.CONCURRENT_PROCESS_ID
ORDER BY
1;
-- END requests_RAC.sql
8) requests_HANG.sql
-- To find current running SQL text for a request with performance/hanging issues - refer to Note 186472.1
-- prints all requests currently running for a request set or can be used for any single running request
-- note that if no SQL is returned, then the process may be working in RAM Memory - examples are MRP or ASCP Memory Based Planner process
-- REQUIRED VALUE - Enter the Request ID that launched the Request set being investigated
select
request_id,
to_char(sid) sid
, to_char(serial#) serial#
, vq.sql_id SQLID
, vs.machine
, sql_text
, vs.process
from
apps.fnd_concurrent_requests fcr,
v$session vs,
v$sqltext vq
where
vs.process = fcr.os_process_id
and vs.sql_address = vq.address
and fcr.status_code = 'R'
and fcr.phase_code = 'R'
and request_id in
(select fcr.request_id
from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */
fcr1.request_id
from fnd_concurrent_requests fcr1
where 1=1
start with fcr1.request_id = &request_id
connect by prior fcr1.request_id = fcr1.parent_request_id) x,
fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcptl
where fcr.request_id = x.request_id
and fcr.concurrent_program_id = fcp.concurrent_program_id
and fcr.program_application_id = fcp.application_id
and fcp.application_id = fcptl.application_id
and fcp.concurrent_program_id = fcptl.concurrent_program_id
and fcptl.language = 'US')
order by request_id, serial#, piece;
-- END requests_HANG.sql
9) Longops_check.sql
-- Good for monitoring long running SQL session where single SQL ID is taking time OR finding long running SQL on system
-- OR --
-- SQL process is known to take extensive time and want to monitor and get calculated/potential time to complete
SELECT inst_id,
sid,
serial#,
sql_id,
opname,
target,
sofar,
totalwork,
start_time,
last_update_time,
ROUND(time_remaining /60,2) "REMAIN MINS",
ROUND(elapsed_seconds/60,2) "ELAPSED MINS",
ROUND((time_remaining+elapsed_seconds)/60,2) "TOTAL MINS",
ROUND(SOFAR /TOTALWORK*100,2) "%_COMPLETE",
MESSAGE
FROM gv$session_longops
WHERE OPNAME NOT LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <>TOTALWORK
AND TIME_REMAINING > 0;
-- END Longops_check.sql
10) requests_orig.sql
-- REQUIRED VALUE - Enter the Request ID that launched the Request set being investigated
SELECT /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
fcr.request_id "Request ID",
fcptl.user_concurrent_program_name"Program Name",
fcr.phase_code,
fcr.status_code,
to_char(fcr.actual_start_date,'DD-MON-YYYY HH24:MI:SS') "Start Time",
to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') "End Time",
(fcr.actual_completion_date - fcr.actual_start_date)*1440 "Elapsed",
fcr.oracle_process_id "Trace ID"
FROM (SELECT /*+ index (fcr1 fnd_concurrent_requests_n3) */
fcr1.request_id
FROM apps.fnd_concurrent_requests fcr1
WHERE 1=1
START WITH fcr1.request_id = &parent_request_id
CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,
apps.fnd_concurrent_requests fcr,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_programs_tl fcptl
WHERE fcr.request_id = x.request_id
AND fcr.concurrent_program_id = fcp.concurrent_program_id
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id = fcptl.application_id
AND fcp.concurrent_program_id = fcptl.concurrent_program_id
AND fcptl.language = 'US'
ORDER BY 1;
-- end of requests_orig.sql
Refer
REQUESTS.sql Script for Parent/Child Request IDs and Trace File IDs (Doc ID 280295.1)
Comments
Post a Comment