Once upon a time I wrote about the Internal Workflow of an E-Business Suite Concurrent Manager Process. Many things have changed since that blog post, from which the most obvious change was the release of Oracle e-Business Suite R12.2. So, I decided to check if the way how the concurrent manager queues were processed by concurrent manager processes were still the same. My main goal was to see if the manager processes still don’t attempt any way of coordination to distribute the requests among them.

This is how I did the testing:

I used the VM templates provided by Oracle to build my R12.2.4 test environment. By the way, I didn’t expect that the process of getting the environment up would be so simple! Downloading the media files from (edelivery.oracle.com) was the most time-consuming step, once done - it took me just 1 hour to un-compress everything, import the Virtual Assembly file and bring up the R12.2.4 environment on my laptop.

3 Standard managers are defined by default

“Sleep seconds” were left as is = 30 seconds

“Cache size” was increased from 1 to 5.

Identified the 3 DB processes that belong to the Standard managers: select sid, serial# from v$session where module='e:FND:cp:STANDARD';

I enabled tracing with binds and waits for each of them like this: exec dbms_monitor.session_trace_enable(sid,serial#,true,true);

Once that was done I submitted one concurrent program - “Active users” and waited for it to complete.

I disabled the tracing like this: exec dbms_monitor.session_trace_disable(sid,serial#);.

Collected the trace files

I found 2 of the trace files to be very interesting. To make things more simple, the manager process “A” will be the one that executed the concurrent request, and process “B” will be the one that didn’t.

Before the “Active Users” request was submitted

No other requests were running at the time I did the testing, so I clearly observed how both Managers A and B queried the FND_CONCURRENT_REQUESTS table BOTH of the trace files displayed the same method of how requests are picked up from the queue. Note, I’m showing only the lines relevant to display the main query only, and I have formatted the query text to make it more readable

All manager’s processes still compete for the same requests. If the query is executed at the same time - the same list of concurrent requests will be retrieved by all processes.

The constants literals used in lines 30-32 mean that the query for checking the queue is still built when the concurrent manager process starts up. These constants are mainly used to implement the specializations rules in the query.

Only rowid for the pending requests’ rows in FND_CONCURRENT_REQUESTS are fetched.

The sleep time is clearly visible on lines 41,42 and 48,50

After the “Active Users” request was submitted - Starting the concurrent request.

The manager process A was the first to pick up the submitted requests and it could be observed by the r=1 (1 row fetched) in the FETCH call for the query we just reviewed: FETCH #139643743645920:c=0,e=437,p=0,cr=113,cu=0,mis=0,r=1,dep=0,og=1,plh=3984653669,tim=1460211519844640

Immediately after that, the manager process A locked the row in FND_CONCURRENT_REQUESTS table, this way, the request got assigned to this process. Notice the similar where predicates used in this query, these are actually required to make sure that the request is still not picked up by another manager process. However the main thing here is the fact that the request row is accessed by the rowid retrieved earlier (row 45, the value of the bind variable :reqname is AAAjnSAA/AAAyn1AAH in this case). Locking of the row is done by the FOR UPDATE OF R.status_code NoWait clause on line 49

The behavior of the manager process B was a little bit more interesting. It too managed to fetch the same rowid from FND_CONCURRENT_PROCESSES table belonging to the submitted “Active Users” processes. However, when it tried to lock the row in FND_CONCURRENT_REQUESTS (By using exactly the same query), this happened:

The query failed with ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.
This is how the access to pending concurrent requests is serialized to make sure only one of the manager processes can run it. And, I think, relying on the well-tuned and highly efficient locking mechanism of Oracle Database is a very very smart idea.

Conclusions

the coordination between manager processes is still not happening to distribute the requests, but the managers all query the queue the same way and then compete between themselves to lock the requests’ entries on the table 1st. The process that gets the lock also gets to execute the concurrent request.

The cache size variable couldn’t be observed in the trace files, but as far as I remember from my previous research the process would only fetch “cache size”-number of rowids using the 1st query in this post. This could be tested by submitting larger volume of requests simultaneously.

The “sleep seconds” kicks in only when the manager process didn’t fetch any rowids from the queue. After all the cached requests are attempted/executed by the manager process, the queue is checked again immediately without waiting for the “sleep seconds” (Not explained in detail in this post, but it’s revealed in the trace files)

The DMLs used to query the FND_CONCURRENT_REQUESTS and to lock the row are very very similar to Pre-R12.2 releases of e-Business Suite (Another sign that the process hasn’t changed). (One change that I see is the addition of where clause predicates for Checking the Editions)