Monday, July 9, 2012

Concurrent requests information scripts

The first script will report information about your concurrent requests, such as if it's scheduled, running or completed, which users run them, request IDs, their arguments.
For example, to find all runs of Workflow Background Process program:
    SELECT F.REQUEST_ID,
         A.USER_NAME,
         DECODE (TO_CHAR (F.ACTUAL_START_DATE, 'DD/MM/YYYY HH24:MI:SS'),
                 NULL, 'PENDING',
                 TO_CHAR (F.ACTUAL_START_DATE, 'DD/MM/YYYY HH24:MI:SS'))
            DATE_STARTED,
         DECODE (
            DECODE (
               TRUNC (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE),
               0, NULL,
                  TRUNC (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE)
               || ' Days'
               || ' + ')
            || TO_CHAR (
                  TRUNC (SYSDATE)
                  + NUMTODSINTERVAL (
                       (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE) * 86400,
                       'second'),
                  'HH24:MI:SS'),
            ' Days + ', 'RUNNING',
            DECODE (
               TRUNC (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE),
               0, NULL,
                  TRUNC (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE)
               || ' Days'
               || ' + ')
            || TO_CHAR (
                  TRUNC (SYSDATE)
                  + NUMTODSINTERVAL (
                       (F.ACTUAL_COMPLETION_DATE - F.ACTUAL_START_DATE) * 86400,
                       'second'),
                  'HH24:MI:SS'))
            REQ_DURATION,
         F.COMPLETION_TEXT,
         P.CONCURRENT_PROGRAM_NAME CONC_PROGRAM,
         F.ARGUMENT_TEXT
    FROM APPLSYS.FND_CONCURRENT_REQUESTS F,
         APPLSYS.FND_USER A,
         APPLSYS.FND_CONCURRENT_PROGRAMS P,
         APPLSYS.FND_CONCURRENT_PROGRAMS_TL PT
   WHERE     A.USER_ID(+) = F.REQUESTED_BY
         AND F.CONCURRENT_PROGRAM_ID = P.CONCURRENT_PROGRAM_ID
         AND P.CONCURRENT_PROGRAM_ID = PT.CONCURRENT_PROGRAM_ID
         AND PT.USER_CONCURRENT_PROGRAM_NAME = 'Workflow Background Process'
         AND PT.LANGUAGE = 'US'
ORDER BY F.REQUESTED_START_DATE DESC;

REQUEST_IDUSER_NAMEDATE_STARTEDREQ_DURATIONCOMPLETION_TEXTCONC_PROGRAMARGUMENT_TEXT
39363160COLL_SETUPPENDINGRUNNINGFNDWFBGXXSTRYRE, , , N, Y, Y
39363194COLL_SETUP09/07/2012 12:00:0300:00:09Normal completionFNDWFBGXXSTRYRE, , , Y, N, N
39362136SYSADMIN09/07/2012 11:36:10RUNNINGFNDWFBGOEOL, , , Y, N, N
.....................
38021590SYSADMIN09/05/2012 10:25:034 Days + 23:06:07FNDWFBGOEOL, , , Y, N, N

If DATE_STARTED has a value of PENDING, then this concurrent request has not started yet. Ignore, in this case, the RUNNING value of REQ_DURATION.

You may filter the result by using specific columns in the WHERE clause.

PHASE_CODE and STATUS_CODE of APPLSYS.FND_CONCURRENT_REQUESTS table.
PHASE_CODE
VALUEMEANING
CCompleted
IInactive
PPending
RRunning

STATUS_CODE
VALUEMEANING
AWaiting
BResuming
CNormal
DCancelled
EError
FScheduled
GWarning
HOn hold
INormal
MNo manager
QStandby
RNormal
SSuspended
TTerminating
UDisabled
WPaused
XTerminated
ZWaiting

CONCURRENT_PROGRAM_NAME of APPLSYS.FND_CONCURRENT_PROGRAMS table, if you want to use its short name (FNDWFBG for our case).
ACTUAL_START_DATE of APPLSYS.FND_CONCURRENT_REQUESTS to limit the result by date.
USER_NAME of APPLSYS.FND_USER to check runs of specific users.

The second script will report the running/pending concurrent requests, to which concurrent manager are assigned, the node they are running their process IDs, log and output files, and database session information.
  SELECT
         REQ.REQUEST_ID "REQUEST ID",
         USR.USER_NAME "USER",
         QU.USER_CONCURRENT_QUEUE_NAME "CONC. MANAGER",
         REQ.REQUEST_DESCRIPTION "NAME",
         REQ.CONCURRENT_PROGRAM_NAME "SHORT NAME",
         REQ.ARGUMENT_TEXT "ARGUMENTS",
         DECODE (REQ.PHASE_CODE,
                 'R', 'RUNNING',
                 'P', 'PENDING',
                 'I', 'INCACTIVE')
            "PHASE",
         REQ.REQUEST_DATE "DATE SUBMITTED",
         REQ.REQUESTED_START_DATE "REQUESTED START DATE",
         REQ.ACTUAL_START_DATE "DATE STARTED",
         REQ.LOGFILE_NODE_NAME "NODE",
         REQ.LOGFILE_NAME "LOG",
         REQ.OUTFILE_NAME "OUTPUT",
         REQ.OS_PROCESS_ID "NODE PROCESS",
         REQ.ORACLE_PROCESS_ID "DB PROCESS",
         SS.INST_ID "DB INSTANCE",
         SS.SID "DB SID",
         SS.STATUS "DB SESSION STATUS"
    FROM APPS.FND_CONCURRENT_WORKER_REQUESTS REQ,
         APPLSYS.FND_USER USR,
         GV$SESSION SS,
         APPS.FND_CONCURRENT_QUEUES_VL QU
   WHERE     USR.USER_ID = REQ.REQUESTED_BY
         AND REQ.ORACLE_SESSION_ID = SS.AUDSID(+)
         AND REQ.CONCURRENT_QUEUE_ID = QU.CONCURRENT_QUEUE_ID
ORDER BY REQUEST_ID DESC;

No comments:

Post a Comment