How to troubleshoot Concurrent Managers!!

Find the Concurrent Request and Number and Logfile Details

SELECT
/*+ ORDERED */
req
.request_id, req.program_application_id,
req
.concurrent_program_id, req.requested_by, fu.user_name,
prg
.user_concurrent_program_name, req.oracle_process_id, ses.SID,
ses
.serial#, ses.program, req.logfile_name LOG, req.outfile_name OUT
FROM v$process pid,
v$session ses
,
apps
.fnd_concurrent_requests req,
fnd_concurrent_programs_tl prg
,
fnd_user fu
WHERE req.request_id IN (SELECT request_id
FROM fnd_conc_req_summary_v
WHERE phase_code IN (‘R’)
AND status_code IN (‘R’))
AND req.oracle_process_id = pid.spid(+)
AND pid.addr = ses.paddr(+)
AND req.concurrent_program_id = prg.concurrent_program_id
AND req.requested_by = fu.user_id
ORDER
BY SID ;

** List the ccmgr jobs running longer than 10 minutes **/*

Formatted on 25/09/2009 09:27 (Formatter Plus v4.8.8) */
SELECT Q.CONCURRENT_QUEUE_NAME
|| ‘ – ‘
|| TARGET_NODE QNAME
, A.REQUEST_ID “Req Id”
, A.PHASE_CODE
, A.STATUS_CODE
, ( NVL( ACTUAL_COMPLETION_DATE, SYSDATE )
– ACTUAL_START_DATE )
* 1440 “Time”
, C.CONCURRENT_PROGRAM_NAME
|| ‘ (‘
|| TO_CHAR( C.CONCURRENT_PROGRAM_ID )
|| ‘)’ INTPROG
, CTL.USER_CONCURRENT_PROGRAM_NAME “program”
FROM APPLSYS.FND_CONCURRENT_REQUESTS A
, APPLSYS.FND_CONCURRENT_PROCESSES B
, APPLSYS.FND_CONCURRENT_QUEUES Q
, APPLSYS.FND_CONCURRENT_PROGRAMS C
, APPLSYS.FND_CONCURRENT_PROGRAMS_TL CTL
WHERE A.CONTROLLING_MANAGER = B.CONCURRENT_PROCESS_ID
AND A.CONCURRENT_PROGRAM_ID = C.CONCURRENT_PROGRAM_ID
AND A.PROGRAM_APPLICATION_ID = C.APPLICATION_ID
AND A.PHASE_CODE IN( ‘I’, ‘P’, ‘R’, ‘T’ )
AND B.QUEUE_APPLICATION_ID = Q.APPLICATION_ID
AND B.CONCURRENT_QUEUE_ID = Q.CONCURRENT_QUEUE_ID
AND CTL.CONCURRENT_PROGRAM_ID = C.CONCURRENT_PROGRAM_ID
AND CTL.LANGUAGE = ‘US’
AND ( NVL( ACTUAL_COMPLETION_DATE, SYSDATE )
– ACTUAL_START_DATE )
* 1440 > 10
AND ( NVL( ACTUAL_COMPLETION_DATE, SYSDATE )
– ACTUAL_START_DATE )
* 1440 >
( SELECT AVG( NVL( A2.ACTUAL_COMPLETION_DATE
– A2.ACTUAL_START_DATE
, 0 ))
* 1440
FROM APPLSYS.FND_CONCURRENT_REQUESTS A2
, APPLSYS.FND_CONCURRENT_PROGRAMS C2
WHERE C2.CONCURRENT_PROGRAM_ID = C.CONCURRENT_PROGRAM_ID
AND A2.CONCURRENT_PROGRAM_ID = C2.CONCURRENT_PROGRAM_ID
AND A2.PROGRAM_APPLICATION_ID = C2.APPLICATION_ID
AND A2.PHASE_CODE
|| ” = ‘C’ )
ORDER BY 5 DESC;

Advertisements
  1. December 29, 2009 at 10:01 am

    Hi,

    Excellent posting. Thanks a lot for sharing.

    Like

  2. Raj
    April 29, 2010 at 8:23 pm

    Hello Sir,

    Can you upload few things related to CM, How to know when one of the thread is not running in Standard Manager. Or say what key things we need to take care of CM up-end Running.

    Thanks –

    RAJ–

    Like

    • ursvenkat
      April 29, 2010 at 8:37 pm

      If you query on FND_CONCURRENT_REQUEST table with REQUEST_ID you will get the serial# and SID by using that you can find wheather the thread is running or not.

      Like

  3. Raj
    April 29, 2010 at 8:41 pm

    Hello Sir,

    Thanks for your Quick Update, Appreciate your Time & Effort !

    Thanks-
    RAJ–

    Like

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: