Concurrent Manager internals (Part 2)

This is the continuation of Concurrent Manager internals (Part 1), where I described how concurrent manager selects a list of requests that have to be run. It was also discovered, that there is no special queue of requests for each concurrent manager and that there are some settings that are obtained only at the time of startup of a concurrent manager. At the moment it is clear that all the processes of the same concurrent manager (e.g. Standard Manager) use the same select to query the requests for execution, therefore it is possible that more than one concurrent manager process will have the same request_id’s to run. In this post I’ll describe the mechanism that’s used to assign the request to a particular concurrent manager process.

The main idea is simple - after getting the concurrent request rowids using the select shown in Concurrent Manager internals (Part 1) the concurrent manager starts processing the list one by one. Each processing case starts with attempt to lock the status_code of particular request using the select ... for update nowait statement below.

SELECT':)'-- ~150 fields selected here. Skipped them to shorten up the selectFROMfnd_concurrent_requestsR,fnd_concurrent_programsP,fnd_applicationA,fnd_userU,fnd_oracle_useridO,fnd_conflicts_domainC,fnd_concurrent_queuesQ,fnd_applicationA2,fnd_executablesE,fnd_conc_request_argumentsXWHERER.Status_code='I'And((R.OPS_INSTANCEisnull)or(R.OPS_INSTANCE=-1)or(R.OPS_INSTANCE=decode(:dcp_on,1,FND_CONC_GLOBAL.OPS_INST_NUM,R.OPS_INSTANCE)))AndR.Request_ID=X.Request_ID(+)AndR.Program_Application_Id=P.Application_Id(+)AndR.Concurrent_Program_Id=P.Concurrent_Program_Id(+)AndR.Program_Application_Id=A.Application_Id(+)AndP.Executable_Application_Id=E.Application_Id(+)AndP.Executable_Id=E.Executable_Id(+)AndP.Executable_Application_Id=A2.Application_Id(+)AndR.Requested_By=U.User_Id(+)AndR.Cd_Id=C.Cd_Id(+)AndR.Oracle_Id=O.Oracle_Id(+)AndQ.Application_Id=:q_applidAndQ.Concurrent_Queue_Id=:queue_idAndQ.Running_Processes<=Q.Max_ProcessesAnd(P.Enabled_FlagisNULLORP.Enabled_Flag='Y')AndR.Hold_Flag='N'AndR.Requested_Start_Date<=SysdateAnd(R.Enforce_Seriality_Flag='N'OR(C.RunAlone_Flag=P.Run_Alone_FlagAnd(P.Run_Alone_Flag='N'ORNotExists(SelectNullFromFnd_Concurrent_RequestsSrWhereSr.Status_CodeIn('R','T')AndSr.Enforce_Seriality_Flag='Y'AndSr.CD_id=C.CD_Id))))AndR.Rowid=:reqnameAnd((P.Execution_Method_Code!='S'OR(R.PROGRAM_APPLICATION_ID,R.CONCURRENT_PROGRAM_ID)IN((0,98),(0,100),(0,31721),(0,31722),(0,31757)))AND((R.PROGRAM_APPLICATION_ID,R.CONCURRENT_PROGRAM_ID)NOTIN((510,40112),(510,40113),(510,41497),(510,41498),(530,41859),(530,41860),(535,41492),(535,41493),(535,41494))))FORUPDATEOFR.status_codeNoWait

If we take a closer look at the query, it’s very similar to the one we saw before (all the main criteria are repeated into this query to make sure nothing has changed with the request after reading it into concurrent manager’s cache), only this one joins the fnd_concurrent_requests table to other tables to select additional data or add some additional checks. For example, this part of the query:

THis is included to implement the Parallel Concurrent Processing with RAC support feature that allows to assign concurrent requests to be executed on a particular RAC node. (If the feature is set up, one can use “Database Instance” profile option settable at Application or Responsibility level to define which RAC instance has to be used for requests submitted from that Application or Responsibility).

Also a part that implements the “Run Alone” option check for a concurrent program is little bit changed:

here we can see that it checks that P.Run_Alone_Flag = ‘N’ (“Run Alone” is not set for that concurrent program) or there are no other requests in phase/status “Running/Normal” or “Running/Terminating” - so it actually makes sure no other requests are running if “Run Alone” is set.

The execution of the select above normally will end in one of 3 ways:

It executes sucessfuly - returns 1 record and the lock on R.status_code is obtained;

It errors with ORA-00054: resource busy and acquire with NOWAIT specified;

It returns 0 records.

Cases 2 and 3 mean that either another concurrent manager has locked the request already (and another process of Concurrent Manager will be responsible for running this request) or the the request did not satisfy all the query conditions (e.g. it’s status_code is already ‘R’ - running, it was put on hold, etc) meaning that something has happened to this requests after placing it into concurrent managers cache. If manager gets the lock on request’s status_code, it will be running this request and an update statement followed by COMMIT is being executed to update the requests details in fnd_concurrent_requests table:

Cache size - defines a number of requests concurrent manager remembers (fetches) from fnd_concurrent_requests table, so that it would not have to re-query the table after each execution of a concurrent request;

The trace file of concurrent manager revealed that it was requerying the fnd_concurrent_requests table stright after it had processed all the requests it knew of. It was waiting the “Sleep seconds” time only if there were no requests for execution queried.

Now imagine what happens if you have configured many concurrent manager processes (e.g. - 6), a small cache size (e.g. - 3) in a busy environment where there are many short-time executing requests.

Each process will remember 3 requests for execution (meaning: fetch 3 records from fnd_concurrent_requests table), but as we know all the concurrent manager processes use the same statement to query the list of pending requests, so it’s very likely that there will be several processes having same requests in their caches, which leads to high probability that, while particular concurrent process is executing the 1st request in it’s cache, all or most of other cached requests will be executed by other concurrent processes. This in turn will force the concurrent manager process to re-query the fnd_concurrent_requests table after executing just 1 request instead of expected 3, and that is not what we want if we set the cache size to 3.

To minimize this effect I would recommend setting “cache size” to a number of desired cache size multiplied by count of concurrent manager processes. In case described above, it would be 6 processes * cahe size of 3 = 18.