PIPER-Rx - Concurrent Queue Content

PIPER-Rx
Oracle E-Business Suite Resource Centre

Concurrent Queue Content

How many requests do you have in each queue?

                                              Total
  Manager               Sleep  Cache  Running Pending  Pending  On Hold  Scheduled
  -------------------- ------ ------ -------- ------- -------- -------- ----------
  STANDARD(2)              30      1        0      16        3       11          2
  OAMCOLMGR(0)             60      1        0       1        1        0          0
  SLOW_QUEUE(1)            60      2        0       3        1        2          0
  
The Manager is the name of the concurrent manager and the trailing number is the number of running manager processes.

From this simple report you can clearly see the number of concurrent requests in each category.

Note: If the number of running requests equals the number of running processes the managers are “flat out”

The collection and presentation of the results is fairly simple, as is the interpretation of the results, however there are a number of complexities: Pending requests

So what do you do with a scheduled request that is on-hold?
In this case a scheduled request on-hold is counted as an on-hold request.

Running Requests

It is possible for the number of running requests to be greater than the number of manager processes; this may be due to Request Set and Request Set Stages which, once running should not be counted as real requests. As such I have removed both Request Set and Request Set Stages from the running counts.

Pending Error

Pending Error is a pending request that is not associated with a manager or there is no manager available to run the request. I have ignored this pending status for this example.


  SELECT fcwr.concurrent_queue_name ||'('||
         fcq.running_processes||')' manager,
         fcq.sleep_seconds sleep_seconds,
         fcq.cache_size cache,
         sum(decode(fcwr.phase_code, 'R', 1, 0)) running,
         sum(decode(fcwr.phase_code, 'P', 1, 0)) total_pending,
         sum(decode(fcwr.phase_code, 'P', 
               decode(fcwr.hold_flag, 'Y', 0, 
                 decode(sign(fcwr.requested_start_date - sysdate), 1, 0, 1)), 0)) pending_normal,
         sum(decode(fcwr.phase_code, 'P', decode(fcwr.hold_flag, 'Y', 1, 0), 0)) on_hold,
         sum(decode(fcwr.phase_code, 'P', 
               decode(sign(fcwr.requested_start_date - sysdate), 1, 
                 decode(fcwr.hold_flag, 'Y', 0, 1), 0), 0))   scheduled
    FROM apps.fnd_concurrent_worker_requests fcwr,
         applsys.fnd_concurrent_queues fcq
   WHERE fcwr.concurrent_queue_id = fcq.concurrent_queue_id
     and fcwr.concurrent_queue_name != 'FNDCRM'
   GROUP by fcwr.concurrent_queue_name,
            fcq.running_processes,
            fcq.sleep_seconds,
            fcq.cache_size
   ORDER by fcwr.concurrent_queue_name;
  
I have provided two reports (Concurrent Requests Summary and Concurrent Requests Details reports ) to list the Manager content as shown above and to list the details of each job in the Queues. These can be found in in the free reports page.

Want to know more? Concurrent Managers has continued to be such a problem area for so many sites I have now also devoted 3 full PAMtutorials to the subject of Concurrent Managers so I would encourage anyone interested in better managing their Concurrent Managers to check these out as well!

Last update: May 2009