Concurrent Manager internals (Part 1)

It has never been absolutely clear for me how concurrent managers decide which request should be executed next and what conditions drive the distribution of requests among all the different concurrent managers and processes of each concurrent manager. I decided to take a closer look, if you’re interested in what I found - read on!

There are different types of concurrent managers in Oracle Applications, Take a look at the “Type” field for the Standard concurrent manager - it’s of type “Concurrent Manager”

Define Concurrent Managers Form

But there are also other types around, like Transaction manager, Internal Monitor, etc. The investigation was carried out on Standard Manager, but the results should be also valid for other “concurrent manager” type concurrent managers (sounds strange) with Program Library “FNDLIBR”. (Further in this post I’ll use just “Concurrent manager”, meaning CM of type “concurrent manager”).

The trace of concurrent manager DB session shows a query that is being periodically run in order to get a list of concurrent requests that have to be executed next:

Select R.Rowid
  From Fnd_Concurrent_Requests R
 Where R.Hold_Flag = 'N'
   And R.Status_Code = 'I'
   And R.Requested_Start_Date <= Sysdate
   AND EXISTS
 (Select Null
          From Fnd_Concurrent_Programs P
         Where P.Enabled_Flag = 'Y'
           And R.Program_Application_Id = P.Application_Id
           And R.Concurrent_Program_Id = P.Concurrent_Program_Id
           AND EXISTS
         (Select Null
                  From Fnd_Oracle_Userid O
                 Where R.Oracle_Id = O.Oracle_Id
                   AND EXISTS (Select Null
                          From Fnd_Conflicts_Domain C
                         Where P.Run_Alone_Flag = C.RunAlone_Flag
                           And R.CD_Id = C.CD_Id))
           And (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) NOT IN
               ((510, 40112), (510, 40113), (510, 41497), (510, 41498),
                (530, 41859), (530, 41860), (535, 41492), (535, 41493),
                (535, 41494))))
 ORDER BY NVL(R.priority, 999999999), R.Priority_Request_ID, R.Request_ID

I’ll try to break down it into logical parts and give a short exlanation for each of it. I’ll show also where the hardcoaded values come from:

It’s querying all the requests that are not on hold (R.Hold_Flag = ‘N’), are in status “Normal” (R.Status_Code = ‘I’), with enabled concurrent program (P.Enabled_Flag = ‘Y’) and with requested start date less then now (And R.Requested_Start_Date <= Sysdate). It was little bit confusing why it queries request with status “Normal”, as I’ve seen that after completion request goes into phase=Completed and status=Normal, but it turned out, that there are 3 different status codes for “Normal”and status_code=”I” is used only for requests in Pending phase.
```
Select R.Rowid
  From Fnd_Concurrent_Requests R
 Where R.Hold_Flag = 'N'
   And R.Status_Code = 'I'
   And R.Requested_Start_Date <= Sysdate
   AND EXISTS
 (Select Null
          From Fnd_Concurrent_Programs P
         Where P.Enabled_Flag = 'Y'
           And R.Program_Application_Id = P.Application_Id
           And R.Concurrent_Program_Id = P.Concurrent_Program_Id
```
Here it selects only those requests for which the DB user that is supposed to run the requests has been registered in OA. I’m not exactly sure why this check is included, as I’ve only seen values of 900 (APPS) for oracle_id in fnd_concurrent_requests table.
```
           AND EXISTS
         (Select Null
                  From Fnd_Oracle_Userid O
                 Where R.Oracle_Id = O.Oracle_Id
```

There is a “Run Alone” setting for Concurrent Programs, if it is enabled, the concurrent program is allwed to execute only if no other requests are running. This part of query makes sure there is no “Run alone” program executing at the moment.

                   AND EXISTS (Select Null
                          From Fnd_Conflicts_Domain C
                         Where P.Run_Alone_Flag = C.RunAlone_Flag
                           And R.CD_Id = C.CD_Id))

This part of query allows only the concurrent programs with execution method other then “Immediate” or if the method is “Immediate” only concurrent programs listed are allowed. Execution method “Immediate” means the executable is implemented as subroutine of the controlling manager, therefore it is important that the request is executed only by the manager which has that subroutine

           And (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)))

The list of values in this part of query is obtained at the startup of the concurrent manager by issuing this query:

Select 'I', 'P',
       PP.Program_Application_ID,
       PP.Concurrent_Program_ID,
       P.Concurrent_Program_Name,
       A.Application_Short_Name
  from Fnd_Application             A,
       Fnd_Conc_Processor_Programs PP,
       Fnd_Concurrent_Programs     P,
       Fnd_Concurrent_Queues       Q
 where A.Application_ID = P.Application_ID
   and P.Execution_Method_Code = 'S'
   and (PP.Program_Application_ID = P.Application_ID and
       PP.Concurrent_Program_ID = P.Concurrent_Program_ID)
   and (PP.Processor_Application_ID = Q.Processor_Application_ID and
       PP.Concurrent_Processor_ID = Q.Concurrent_Processor_ID)
   and (Q.Application_ID = :b0 and Q.Concurrent_Queue_ID = :b1)

This is the part that queries only those concurrent requests that have to be executed with the particular concurrent manager. The list is compiled at the startup of the concurrent manager based on Specialization Rules. The rules are obtained using this query:

           AND ((R.PROGRAM_APPLICATION_ID, R.CONCURRENT_PROGRAM_ID) NOT IN
               ((510, 40112), (510, 40113), (510, 41497), (510, 41498),
                (530, 41859), (530, 41860), (535, 41492), (535, 41493),
                (535, 41494))))

Specialization Rules

If EXCLUDE rule is defined, NOT IN clause will be constructed, if INCLUDE defined - IN clause will be constructed. Specialization rules can define Exclusion or Inclusion of particular concurrent programs, request types, oracle IDs, Users, or combined rules.

Select include_flag,
       Decode(type_code, 'P', Decode(type_id, NULL, 'A', 'P'), type_code),
       type_application_id,
       Decode(type_code, 'P', NVL(type_id, type_application_id), type_id)
  from fnd_concurrent_queue_content
 where queue_application_id = :b0
   and concurrent_queue_id = :b1
 order by include_flag, type_code, type_application_id, type_id

And finally the list is sorted by execution priority.

 ORDER BY NVL(R.priority, 999999999), R.Priority_Request_ID, R.Request_ID

Main conclusions at this moment are:

There is no special queue that handles tasks that have to be done by each manager.;
All the processes of one Concurrent Manager (e.g. Standard manager) will get the same list of requests for execution;
Several settings (“Immediate” type program list and specialization rules) are read only at the time the concurrent manager is started, so after changing these values all the affected managers have to be restarted.

…to be continued…