This blog article will help administrators to get insight of the concurrent manager and also control over its functionality.
Master Concurrent Managers
There are many concurrent managers in Oracle EBS application like Output Post Processor (related to XML publisher reports), Workflow Agent Listener Service (related to workflow notifications), Workflow Mailer Service (related to workflow notification mailer setup), Workflow Document Web Services Service, etc. But among all these concurrent managers are the three super concurrent managers and they are,
Internal Concurrent Manager — The master manager is called the Internal Concurrent Manager (ICM) because it controls the behavior of all of the other managers, and because the ICM is the boss, it must be running before any other managers can be activated. The main functions of the ICM are to start up and shutdown the individual concurrent managers, and reset the other managers after one of them has a failure.
Standard Manager — Another important master Concurrent Manager is called the Standard Manager (SM). The SM functions to run any reports and batch jobs that have not been defined to run in any specific product manager. Examples of specific concurrent managers include the Inventory Manager, CRP Inquiry Manager, and the Receivables Tax Manager.
Conflict Resolution Manager — The Conflict Resolution Manager (CRM) functions to check concurrent program definitions for incompatibility rules. However, the ICM can be configured to take over the CRM's job to resolve incompatibilities.
Enable Tracing for Concurrent Programs
For troubleshooting purposes if you want to enable tracing for a concurrent program then goto System Administrator-->Concurrent-->Program-->Define-->Here tick the checkbox 'Enable Trace' for the specific concurrent program that you want to generate trace for.
Now let's see as an Apps DBA, how to monitor and Tune the concurrent manager;
Tuning the Internal Concurrent Manager (ICM)
The ICM performance is affected by the three important Oracle parameters PMON cycle, queue size, and sleep time.
PMON cycle — This is the number of sleep cycles that the ICM waits between the time it checks for concurrent managers failures, which defaults to 20. You should change the PMON cycle to a number lower than 20 if your concurrent managers are having problems with abnormal terminations.
Queue Size — The queue size is the number of PMON cycles that the ICM waits between checking for disabled or new concurrent managers. The default for queue size of 1 PMON cycle should be used.
Sleep Time — The sleep time parameter indicates the seconds that the ICM should wait between checking for requests that are waiting to run. The default sleep time is 60, but you can lower this number if you see you have a lot of request waiting (Pending/Normal). However, reducing this number to a very low value many cause excessive cpu utilization.
Purging Concurrent Requests
It is recommended to run this program 'Purge concurrent request/manager data' once every week as a meintenance task. Sometimes some report output files doesn't get deleted so it is required to Set the system profile option "Concurrent: Force Local Output File Mode" to "Yes". You need to apply patch 7530490 for R12 (or) 7834670 for 11i to get this profile.
Refer Note.822368.1: Purge Concurrent Request FNDCPPUR Does Not Delete Files From File System or Slow performance
Note:- The profile option "Concurrent: Force Local Output File Mode" is set to "No" by default. After applying the patch, set the profile option to YES will
cause FNDCPPUR to always access files on the local file system, hence FNDCPPUR will remove the OS files faster.To enable this feature, All Concurrent Manager nodes must be able to access the output file location via the local filesystem.
Ensure that the log/out files are removed from the locations shown below as you run "Purge Concurrent Request and/or Manager Data program".
$APPLCSF/$APPLLOG==>P:\oracle\inst\apps\proderp_prodapp\logs\appl\conc\log $APPLCSF/$APPLOUT==>P:\oracle\inst\apps\proderp_prodapp\logs\appl\conc\out
Truncate the reports.log file in log directory.
Refer Note.844976.1 for more details.
Truncation of file "reports.log" is a regular maintenance work of Application DBA. Make sure that reports log file size should not increase to its maximum limit of 2 GB. There is no purge program to truncate file "reports.log". This maintenance needs to be done manually and regularly depending on number of concurrent program which uses "reports.log". You can safely truncate "reports.log". The "reports.log" file can be located under $APPLCSF/$APPLLOG.
Adjusting the Concurrent Manager Cache Size
Concurrent manager performance can also be enhanced by increasing the manager cache size to be at lease twice the number of target processes. The cache size specifies the number of requests that will be cached each time the concurrent manager reads from the FND_CONCURRENT_REQUESTS table. Increasing the cache size will boost the throughput of the managers by attempting to avoid sleep time.
Analyzing Oracle Apps Dictionary Tables for High Performance
It is also very important to run the request Gather Table Statistics on these tables:
FND_CONCURRENT_PROCESSES
FND_CONCURRENT_PROGRAMS
FND_CONCURRENT_REQUESTS
FND_CONCURRENT_QUEUES.
Run the request "Analyze All Index Column Statistics" on the indexes of these tables. Since the APPLSYS user is the owner of these tables, so you can also just run the request Analyze Schema Statistics for APPLSYS.
To troubleshoot performance, a DBA can use three types of trace. A module trace, such as PO or AR, can be set by enabling the module's profile option Debug Trace from within the applications. Second, most concurrent requests can be set to generate a trace file by changing the request parameters. To enable trace for a specific request, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Program -> Define. Query for the request that you want to enable trace. In the first picture given above you can check the box Enable Trace.
Another popular way to troubleshoot the Concurrent Managers is to generate a trace file. This is done by setting the OS environment variable FNDSQLCHK to FULL, and running the request from the command line.
Monitoring Pending Requests in the Concurrent Managers
Occasionally, you may find that requests are stacking up in the concurrent managers with a status of "pending". This can be caused by any of these conditions:
1. The concurrent managers were brought down will a request was running.
2. The database was shutdown before shutting down the concurrent managers.
3. There is a shortage of RAM memory or CPU resources.
When you get a backlog of pending requests, you can first allocate more processes to the manager that is having the problem in order to allow most of the requests to process, and then make a list of the requests that will not complete so they can be resubmitted, and cancel them.
To allocate more processes to a manager, log in as a user with the System Administrator responsibility. Navigate to Concurrent -> Manager -> Define. Increase the number in the Processes column (check second picture on this page).
Also, you may not need all the concurrent managers that Oracle supplies with an Oracle Applications install, so you can save resources by identifying the unneeded managers and disabling them.
However, you can still have problems. If the request remains in a phase of RUNNING and a status of TERMINATING after allocating more processes to the manager, then shutdown the concurrent managers, kill any processes from the operating system that won't terminate, and execute the following sqlplus statement as the APPLSYS user to reset the managers in the FND_CONCURRENT_REQUESTS table:
update fnd_concurrent_requests set status_code='X', phase_code='C'where status_code='T';
Changing Dispatching Priority within the Concurrent Manager
(Check first picture on this page) If there are requests that have a higher priority to run over other requests, you can navigate to Concurrent --> Program --> Define to change the priority of a request. If a priority is not set for a request, it will have the same priority as all other requests, or it will be set to the value specified in the user's profile option Concurrent:Priority.
Also, you can specify that a request run using an SQL optimizer mode of FIRST_ROWS, ALL_ROWS, RULE, or CHOOSE, and this can radically effect the performance of the SQL inside the Concurrent request. If several long running requests are submitted together, they can cause fast running requests to have to wait unnecessarily. If this is occurring, try to schedule as many long running requests to run after peak business hours. Additionally, a concurrent manager can be created to run only fast running requests.
Using data Dictionary Scripts with the Concurrent Manager
Few Oracle Applications DBAs understand that sophisticated data dictionary queries can be run to reveal details about the workings within each Concurrent Manager. Oracle provides several internal tables that can be queried from SQL*Plus to see the status of the concurrent requests, and the most important are FND_CONCURRENT_PROGRAMS and FND_CONCURRENT_REQUESTS.
Oracle supplies several useful scripts, (located in $FND_TOP/sql directory), for monitoring the concurrent managers:
afcmstat.sql Displays all the defined managers, their maximum capacity, pids, and their status.
afimchk.sql Displays the status of ICM and PMON method in effect, the ICM's log file, and determines if the concurrent manger monitor is running.
afcmcreq.sql Displays the concurrent manager and the name of its log file that processed a request.
afrqwait.sql Displays the requests that are pending, held, and scheduled.
afrqstat.sql Displays of summary of concurrent request execution time and status since a particular date.
afqpmrid.sql Displays the operating system process id of the FNDLIBR process based on a concurrent request id. The process id can then be used with the ORADEBUG utility.
afimlock.sql Displays the process id, terminal, and process id that may be causing locks that the ICM and CRM are waiting to get. You should run this script if there are long delays when submitting jobs, or if you suspect the ICM is in a gridlock with another oracle process.
In addition to these canned scripts you can skill write custom Concurrent Manager scripts. For example, the following query can be executed to identify requests based on the number of minutes the request ran:
SELECT
fcr.request_id request_id,
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) exec_time,
fcr.actual_start_date start_date,
fcp.concurrent_program_name conc_prog,
fcpt.user_concurrent_program_name user_conc_prog
FROM
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt,
fnd_concurrent_requests fcr
WHERE
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
and
fcr.concurrent_program_id = fcp.concurrent_program_id
and
fcr.program_application_id = fcp.application_id
and
fcr.concurrent_program_id = fcpt.concurrent_program_id
and
fcr.program_application_id = fcpt.application_id
and
fcpt.language = USERENV('Lang')
ORDER BY
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) desc;
Note that this script prompts you for the number of minutes.
Conclusion
The Oracle Concurrent Managers remain one of Oracle most important components for the Oracle Applications eBusiness suite and they perform an important TP monitor function. It is only by properly understand the functions and tuning of the Concurrent Managers that the Oracle Apps DBA can be successful in keep their sophisticated Applications optimizer for high-performance.
References: Oracle Metalink and sir Burlesons site.
If you have any question please don't hesitate to email me at samiora@gmail.com
