Concurrent Manager/Request Tuning(Supply Chain Cost Rollup - Print Report)

Concurrent Manager/Request Tuning(Supply Chain Cost Rollup - Print Report)

Recently I was engaged with a Concurrent Processing performance tuning. Queue for "Cost Rollup Manager" was growing rapidly.
This manager is responsible to run only "Supply Chain Cost Rollup - Print Report" and it should not take more than 30 seconds to complete normally.

Though its underlying code was causing the issue, writing in this blog. Same informations can be used to tune other manager and requests as well.

Find the Requests a Manager would Run

select  ptl.user_concurrent_program_name,qtl.user_concurrent_queue_name,t.request_id
  from Fnd_Concurrent_Requests t,
       FND_CONCURRENT_PROCESSES k,
       Fnd_Concurrent_Queues_TL QTL,
       Fnd_Concurrent_Programs_TL PTL 
  where k.concurrent_process_id = t.controlling_manager
    and QTL.Concurrent_Queue_Id = k.concurrent_queue_id
    and ptl.concurrent_program_id=t.concurrent_program_id
    AND QTL.LANGUAGE='US'
    AND PTL.USER_CONCURRENT_PROGRAM_NAME LIKE '%'
    AND qtl.user_concurrent_queue_name ='Cost Rollup Manager'
ORDER BY ptl.user_concurrent_program_name DESC;

Find the Requests Running More than 30 Minutes

undefine start_date
undefine end_date

set pages 10000
set verify off
column request_id format 99999999 heading 'REQUEST'
column user_name format a17
column phase format a10
column status format a12
column start_date format a5
column completion_date format a5 heading 'END'
column avg_run_time format 9999 heading 'AVG TIME'
column min_run_time format 9999 heading 'MIN TIME'
column max_run_time format 9999 heading 'MAX TIME'
column program_name format a50

select
    p.user_concurrent_program_name program_name,
    count(r.request_id),
    avg((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) avg_run_time,
    min((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) min_run_time,
    max((nvl(r.actual_completion_date,sysdate) - r.actual_start_date) * 24 * 60) max_run_time
from
    apps.fnd_concurrent_requests r,
    apps.fnd_concurrent_processes c,
    apps.fnd_concurrent_queues q,
    apps.fnd_concurrent_programs_vl p
where
    p.concurrent_program_id = r.concurrent_program_id
    and p.application_id = r.program_application_id
    and c.concurrent_process_id = r.controlling_manager
    and q.concurrent_queue_id = c.concurrent_queue_id
    and p.application_id >= &&ApplicationId
    and r.actual_start_date >= sysdate-31
    and r.status_code = 'C'
    and r.phase_code in ('C')
    and (nvl(r.actual_completion_date,r.actual_start_date) - r.actual_start_date) * 24 * 60 > 30
    and p.user_concurrent_program_name not like 'Gather%Statistics%'
    and (
      (nvl(r.actual_completion_date,r.actual_start_date) - r.actual_start_date) * 24 > 16
      or
      (r.actual_start_date-trunc(r.actual_start_date)) * 24 between 9 and 17
      or
      (r.actual_completion_date-trunc(r.actual_completion_date)) * 24 between 9 and 17
    )
group by p.user_concurrent_program_name
/

For a particular Date how many Requests are submitted for a particular concurrent requests and its performance analysis.

SELECT -- /*+ first_rows*/
  fcr.request_id req_id,
  fcp.concurrent_program_name conc_prg,
  PT.USER_CONCURRENT_PROGRAM_NAME USR_CONC_PRG,
  TO_CHAR (FCR.ACTUAL_START_DATE, 'DD-MON-YY HH24:MI:SS') START_DATE,
  ---NVL (TO_CHAR (fcr.actual_completion_date, 'mm-MON-yy HH24:MI:SS'), 'Not complete ') end_date,
  SUBSTR ( DECODE ( TRUNC (actual_completion_date - actual_start_date), 0, NULL, TRUNC (actual_completion_date - actual_start_date)
  || 'D' )
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24, 24 )), 2, 0 )
  || ':'
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24 * 60, 60 )), 2, 0 )
  || ':'
  || LPAD ( TRUNC(MOD ( (actual_completion_date - actual_start_date) * 24 * 60 * 60, 60 )), 2, 0 ), 1, 10 ) TIME,
  flv1.meaning phase,
  flv2.meaning status,
  fcr.argument_text parameters,
  fcr.oracle_process_id
FROM applsys.fnd_concurrent_programs fcp,
  applsys.fnd_concurrent_programs_tl pt,
  applsys.fnd_concurrent_requests fcr,
  fnd_lookup_values flv1,
  fnd_lookup_values flv2
WHERE FCR.CONCURRENT_PROGRAM_ID = FCP.CONCURRENT_PROGRAM_ID
AND TRUNC (fcr.ACTUAL_START_DATE) BETWEEN TO_DATE ('17-JAN-2014', 'DD-MON-YYYY') AND TO_DATE ('26-JAN-2014', 'DD-MON-YYYY')
AND fcr.program_application_id = fcp.application_id
AND fcp.application_id         = pt.application_id
AND fcp.concurrent_program_id  = pt.concurrent_program_id
AND pt.LANGUAGE                = 'US'
AND fcr.phase_code             = flv1.lookup_code
AND flv1.lookup_type           = 'CP_PHASE_CODE'
AND flv1.LANGUAGE              = 'US'
AND flv1.view_application_id   = 0
AND fcr.status_code            = flv2.lookup_code
AND flv2.lookup_type           = 'CP_STATUS_CODE'
AND flv2.LANGUAGE              = 'US'
AND FLV2.VIEW_APPLICATION_ID   = 0
AND PT.USER_CONCURRENT_PROGRAM_NAME LIKE '%Supply Chain Cost Rollup - Print Report%'
--ORDER BY FCR.ACTUAL_START_DATE DESC;
ORDER BY time DESC;

Solutions are outlined in following notes

Supply Chain Cost Rollup - Print Report Run Very Long time (Doc ID 1463306.1)
Supply Chain Cost Rollup - Print Report Performance Issues (Doc ID 1588101.1)