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 2 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 "Program_Name",

SUBSTR(A.USER_CONCURRENT_PROGRAM_NAME,1,40) "User_Program_Name",

SUBSTR(B.USER_NAME,1,15) "Last_Updated_By",

SUBSTR(B.DESCRIPTION,1,25) DESCRIPTION

FROM APPS.FND_CONCURRENT_PROGRAMS_VL A, APPLSYS.FND_USER B

WHERE A.ENABLE_TRACE='Y'

AND A.LAST_UPDATED_BY=B.USER_ID;

 

Find child request

 

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.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted",

--     (fcr.actual_start_date - fcr.request_date)*1440 "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.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;

 

Gather Stats status Check

SELECT

    request_id,

    phase_code,

    status_code,

    to_char(requested_start_date, 'DD-MM-YY hh24:mi:ss') "Requested start",

    to_char(actual_start_date, 'DD-MM-YY hh24:mi:ss') "Actually started",

    to_char(actual_completion_date, 'DD-MM-YY hh24:mi:ss') "Actually completed",

    cr.argument_text,

    trunc(mod((nvl(cr.actual_completion_date, sysdate) - cr.actual_start_date) * 24, 24)) "Hr",

    trunc(mod((nvl(cr.actual_completion_date, sysdate) - cr.actual_start_date) * 24 * 60, 60)) "Mi",

    trunc(mod((nvl(cr.actual_completion_date, sysdate) - cr.actual_start_date) * 24 * 60 * 60, 60)) "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';