Concurrent Manager and Program Frequently Using Queries

Long Running Query

    SELECT fcr.request_id, fcr.parent_request_id, ftp.user_concurrent_program_name,
       fcu.user_name, fcu.description User_Detail,
       fcr.phase_code phase,
       floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) || ' Hrs ' ||
       floor((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) -
       floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600) / 60) || ' Mins ' ||
       round((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) -
       floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600 -
       (floor((((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) -
       floor(((SYSDATE - FCR.ACTUAL_START_DATE) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60))) || ' Secs ' "RUNNING_SINCE",
       to_char(FCR.requested_start_date, ' DAY DD-MON-YY HH:MI AM') REQUESTED_START_DATE,
       to_char(FCR.actual_start_date, ' DAY DD-MON-YY HH:MI AM') ACTUAL_START_DATE,
       floor(((FCR.ACTUAL_START_DATE - FCR.request_date) * 24 * 60 * 60) / 3600) || ' Hrs ' ||
       floor((((FCR.ACTUAL_START_DATE - FCR.request_date) * 24 * 60 * 60) -
       floor(((FCR.ACTUAL_START_DATE - FCR.request_date) * 24 * 60 * 60) / 3600) * 3600) / 60) || ' Mins ' ||
       round((((FCR.ACTUAL_START_DATE - FCR.request_date) * 24 * 60 * 60) -
       floor(((FCR.ACTUAL_START_DATE - FCR.request_date) * 24 * 60 * 60) / 3600) * 3600 -
       (floor((((FCR.ACTUAL_START_DATE - FCR.request_date) * 24 * 60 * 60) -
       floor(((FCR.ACTUAL_START_DATE - FCR.request_date) * 24 * 60 * 60) / 3600) * 3600) / 60) * 60))) || ' Secs ' "DELAYED_START",
       fcr.argument_text, ftp.description Program_Detail
FROM apps.fnd_concurrent_requests fcr,
     apps.fnd_concurrent_programs_tl ftp,
     apps.fnd_user fcu
WHERE fcr.status_code = 'R'
  AND fcr.phase_code = 'R'
  AND fcu.user_id = fcr.requested_by
  AND fcr.concurrent_program_id = ftp.concurrent_program_id
  AND ftp.language = 'US'
ORDER BY 3;
    

  

Pending standby

 

   SELECT V.PROGRAM, COUNT(1)
FROM FND_CONC_REQ_SUMMARY_V V, FND_CONCURRENT_REQUESTS C
-- SELECT V.REQUESTOR, COUNT(1) FROM FND_CONC_REQ_SUMMARY_V V, FND_CONCURRENT_REQUESTS C

WHERE V.REQUEST_ID = C.REQUEST_ID
-- AND V.PROGRAM = 'WSH Packing Slip Report(PDF Output)-APL'
-- AND V.PROGRAM LIKE 'WSH%'
AND C.STATUS_CODE = 'Q'
-- GROUP BY V.REQUESTOR
-- AND V.REQUESTOR='9876543'

GROUP BY V.PROGRAM
ORDER BY COUNT(1) DESC;
    

Find the trace enabled concurrent programs

COL User_Program_Name FOR A40
COL Last_Updated_By FOR A30
COL DESCRIPTION FOR A30

SELECT A.CONCURRENT_PROGRAM_NAME AS "Program_Name",
       SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME, 1, 40) AS "User_Program_Name",
       SUBSTR(B.USER_NAME, 1, 15) AS "Last_Updated_By",
       SUBSTR(B.DESCRIPTION, 1, 25) AS DESCRIPTION
FROM APPS.FND_CONCURRENT_PROGRAMS_VL A
JOIN APPLSYS.FND_USER B ON A.LAST_UPDATED_BY = B.USER_ID
WHERE A.ENABLE_TRACE = 'Y';
    

Find child request

SELECT /*+ ORDERED USE_NL(x fcr fcp fcptl) */
       fcr.request_id AS "Request ID",
       fcptl.user_concurrent_program_name AS "Program Name",
       fcr.phase_code,
       fcr.status_code,
       to_char(fcr.actual_start_date, 'DD-MON-YYYY HH24:MI:SS') AS "Start Time",
       to_char(fcr.actual_completion_date, 'DD-MON-YYYY HH24:MI:SS') AS "End Time",
       (fcr.actual_completion_date - fcr.actual_start_date) * 1440 AS "Elapsed",
       fcr.oracle_process_id AS "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;
    

Gather Stats status Check

SELECT
    request_id,
    phase_code,
    status_code,
    to_char(requested_start_date, 'DD-MM-YY hh24:mi:ss') AS "Requested start",
    to_char(actual_start_date, 'DD-MM-YY hh24:mi:ss') AS "Actually started",
    to_char(actual_completion_date, 'DD-MM-YY hh24:mi:ss') AS "Actually completed",
    cr.argument_text,
    trunc(mod((nvl(cr.actual_completion_date, sysdate) - cr.actual_start_date) * 24, 24)) AS "Hr",
    trunc(mod((nvl(cr.actual_completion_date, sysdate) - cr.actual_start_date) * 24 * 60, 60)) AS "Mi",
    trunc(mod((nvl(cr.actual_completion_date, sysdate) - cr.actual_start_date) * 24 * 60 * 60, 60)) AS "Sec"
FROM
    fnd_concurrent_requests cr
WHERE
    concurrent_program_id IN (
        SELECT
            concurrent_program_id
        FROM
            apps.fnd_concurrent_programs
        WHERE
            concurrent_program_name = 'FNDGTST'
    )
    AND actual_start_date > sysdate - 70
ORDER BY
    7 ASC;
    

Parameters

FNDGACST Gather All Column Statistics

FNDGCLST Gather Column Statistics

FNDGSCST Gather Schema Statistics

FNDGTST Gather Table Statistics

 

select owner,TABLE_NAME,LAST_ANALYZED,NUM_ROWS from dba_tab_statistics where owner='MSC' and table_name='MSC_SUPPLIES';