cm heavy load

SQL TO FIND OUT CONCURRENT REQUESTS CURRENTLY RUNNING:
**********************************************************
set lines 180
set pages 1000
set verify off
undef spid
column req_id format 99999999999
column OPID format a10
column PPID format a8
column SPID format a8
column ST_CD format a1
column ph_cd format a1
column CNAME format a30
column event format a15
column user_name format a10
column program format a8
column serial# format 999999
column sid format 9999
column username format a8
select a.request_id "REQ_ID",a.oracle_process_id "OPID",a.os_process_id
"PPID",
e.user_concurrent_program_name "CNAME",
f.user_name,a.status_code "ST_CD",a.phase_code "PH_CD", b.username,b.sid,
b.serial#,b.program,g.event,
to_char(a.ACTUAL_START_DATE,'MON-DD-HH-MI-SS') START_DATE,
to_char(a.ACTUAL_COMPLETION_DATE,'MON-DD-HH-MI-SS') COMPL_DATE
from apps.fnd_concurrent_requests a,(select c.username,c.sid,c.serial#,
                        c.program,d.spid from v$session c, v$process d
                        where c.paddr=d.addr) b,
                        apps.fnd_concurrent_programs_tl e,
                        apps.fnd_user f,
                        v$session_wait g
                        where a.oracle_process_id=b.spid
                        and a.concurrent_program_id=e.concurrent_program_id
                        and e.language='US'
                        and a.requested_by=f.user_id
                        and b.sid=g.sid
            and a.status_code='R'
            and a.phase_code='R';

SEE THE CURRENT STATUS OF ALL SUBMITTED REQUEST.

 

SELECT fu.user_name                           "User ID",

 

       frt.responsibility_name                "Responsibility Used",

 

       fcr.request_id                         "Request ID",

 

       fcpt.user_concurrent_program_name      "Concurrent Program Name",

 

       DECODE(fcr.phase_code,

 

              'C',  'Completed',

 

              'P',  'Pending',

 

              'R',  'Running',

 

              'I',  'Inactive',

 

              fcr.phase_code)                 "Phase",

 

       DECODE(fcr.status_code,

 

              'A',  'Waiting',

 

              'B',  'Resuming',

 

              'C',  'Normal',

 

              'D',  'Cancelled',

 

              'E',  'Error',

 

              'F',  'Scheduled',

 

              'G',  'Warning',

 

              'H',  'On Hold',

 

              'I',  'Normal',

 

              'M',  'No Manager',

 

              'Q',  'Standby',

 

              'R',  'Normal',

 

              'S',  'Suspended',

 

              'T',  'Terminating',

 

              'U',  'Disabled',

 

              'W',  'Paused',

 

              'X',  'Terminated',

 

              'Z',  'Waiting',

 

              fcr.status_code)                "Status",

 

       fcr.request_date                       "Request Date",

 

       fcr.requested_start_date               "Request Start Date",

 

       fcr.hold_flag                          "Hold Flag",

 

       fcr.printer                            "Printer Name",

 

       fcr.parent_request_id                  "Parent Request ID"

 

       -- fcr.number_of_arguments,

 

       -- fcr.argument_text,

 

       -- fcr.logfile_name,

 

       -- fcr.outfile_name

 

  FROM fnd_user                    fu,

 

       fnd_responsibility_tl       frt,

 

       fnd_concurrent_requests     fcr,

 

       fnd_concurrent_programs_tl  fcpt

 

 WHERE fu.user_id                 =  fcr.requested_by

 

   AND fcr.concurrent_program_id  =  fcpt.concurrent_program_id

 

   AND fcr.responsibility_id      =  frt.responsibility_id

 

   AND frt.LANGUAGE               =  USERENV('LANG')

 

   AND fcpt.LANGUAGE              =  USERENV('LANG')

 

   -- AND fcr.request_id = 7137350  -- <change it>

 

 -- AND fcpt.user_concurrent_program_name = 'Autoinvoice Import Program'  --< change it>

 

 ORDER BY fcr.request_date DESC;

 

FIND THE PENDING STATUS  OF Concurrent request.