Monday, March 10, 2008

Boost productivity, reduce user frustration: Speed startup of Oracle Applications concurrent requests

Apps Users: Do you recognize this key sequence?
alt-v r enter alt-r alt-r alt-r alt-r alt-r ...

That's me waiting for concurrent requests to complete ... or worse ... to start!

Are you, your Developers or Test Team constantly clicking the "Refresh" button on the View Concurrent Requests screen to no avail?

If this is you then read on ... or forward to your DBA ... it could be time to decrease the sleep time of your standard concurrent manager!

Does the following query return 30, 60 or higher for the "Standard" concurrent manager?

select fcq.concurrent_queue_id
,      fcq.concurrent_queue_name
,      fcqt.user_concurrent_queue_name
,      fcqs.sleep_seconds sleep_sec
,      fcqs.min_processes min_proc
from   fnd_concurrent_queues fcq
,      fnd_concurrent_queues_tl fcqt
,      fnd_concurrent_queue_size fcqs 
,      fnd_concurrent_processors fcpr
where  fcq.concurrent_queue_id = fcqs.concurrent_queue_id
and    fcq.concurrent_queue_id = fcqt.concurrent_queue_id
and    fcq.concurrent_processor_id = fcpr.concurrent_processor_id
and    fcqt.language = 'US'
and    fcpr.concurrent_processor_name = 'FNDLIBR'
order by decode(fcq.concurrent_queue_name, 'STANDARD',0,1)
,        fcqt.user_concurrent_queue_name;

Is your average wait (AVG_WAIT) time for concurrent requests starting above a second or two?

Caveat: the following query assumes the majority of requests are not scheduled, any wait over 2minutes is excluded, and request history is retained for at least 2 weeks.

PROMPT Concurrent Requests wait stats for the prior week
select trunc(sysdate-8) date_from
,      trunc(sysdate-1) date_to
,      count(1) num_requests
,      round(sum(actual_start_date - request_date),2) tot_wait
,      round(sum(actual_start_date - request_date) / count(1),2) avg_wait
from   fnd_concurrent_requests
where  actual_start_date is not null
and    actual_start_date - request_date < 120
and    request_date >= trunc(sysdate-8)
and    request_date <  trunc(sysdate-1);

Review your Workshift for your Standard (or similar) manager.

  • System Administrator, Concurrent, Manager, Define
  • Query up the Standard manager (or similar)
  • Click on Workshifts
  • Review what the Sleep Seconds and Processes are set to.

Set the sleep time low enough, say 10 seconds, and check that there are adequate processes.

Note of course that reducing sleep time or increasing processes will put more load on your hardware, but if you have the capacity try it and see how it goes!



Rohit Kokate said...
This comment has been removed by the author.
Gareth said...

Hi Rohit,
Sorry, not sure. In your dev or test system, try update the backend table and restart concurrent managers.