This article will explore tools that are used by experienced administrators to gain insight and improved control over the concurrent management functions. We will explore how the concurrent managers can be configured via the GUI, and also explore scripts and dictionary queries that are used to improve the functionality of concurrent management.
The Master Concurrent Managers
There is a lot of talk about "the" concurrent manager in Oracle Applications. Actually, there are many Concurrent Managers, each governing flow within each Oracle Apps areas. In addition there are "super" Concurrent Managers whose job is to govern the behavior of the slave Concurrent Managers. The Oracle e-Business suite has three important master Concurrent Managers:
- 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 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.
Tuning the Concurrent Manager
All successful Oracle Apps DBAs must understand how to monitor and tune each of the Concurrent Managers. This article will explore some of the important techniques for monitoring and tuning the Oracle Apps Concurrent Manager processes. The topics will include:
- Tuning the Concurrent Manager
- Tuning the Internal Concurrent Manager
- Purging Concurrent Requests
- Troubleshooting Oracle Apps performance problems
- Adjusting the Concurrent Manager Cache Size
- Analyzing the Oracle Apps Dictionary Tables
- Monitoring Pending Requests in the Concurrent Manager
- Changing the dispatching priority within 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.
One important area of Concurrent Manager tuning is monitoring the space usage for the subsets within each concurrent manager. When the space in FND_CONCURRENT_PROCESSES and FND_CONCURRENT_REQUESTS exceed 50K, you can start to experience serious performance problems within your Oracle Applications. When you experience these space problems, a specific request called "Purge Concurrent Requests And/Or Manager Data" should be scheduled to run on a regular basis. This request can be configured to purge the request data from the FND tables as well as the log files and output files on accumulate on disk.
Adjusting the Concurrent Manager Cache Size
Analyzing Oracle Apps Dictionary Tables for High Performance
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.
Figure 1: Troubleshooting Concurrent Manager Performance.
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.
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:
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.
Figure 2: Allocating more processes to the Concurrent Manager.
set status_code='X', phase_code='C'
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.
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:
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.|
|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:
set echo off
set feedback off
set linesize 97
set verify off
col request_id format 9999999999 heading "Request ID"
col exec_time format 999999999 heading "Exec Time|(Minutes)"
col start_date format a10 heading "Start Date"
col conc_prog format a20 heading "Conc Program Name"
col user_conc_prog format a40 trunc heading "User Program Name"
TRUNC(((fcr.actual_completion_date-fcr.actual_start_date)/(1/24))*60) > NVL('&min',45)
fcr.concurrent_program_id = fcp.concurrent_program_id
fcr.program_application_id = fcp.application_id
fcr.concurrent_program_id = fcpt.concurrent_program_id
fcr.program_application_id = fcpt.application_id
fcpt.language = USERENV('Lang')
Note that this script prompts you for the number of minutes. The output from this query with a value of 60 produced the following output on my database. Here we can see important details about currently-running requests, including the request ID, the execution time, the user who submitted the program and the name of the program.
Enter value for min: 60
Request ID (Minutes) Start Date Conc Program Name User Program Name
----------- ---------- ---------- -------------------- --------------------------------------
1445627 218 01-SEP-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
444965 211 03-JUL-01 CSTRBICR5G Cost Rollup - No Report GUI
1418262 208 22-AUG-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
439443 205 28-JUN-01 CSTRBICR5G Cost Rollup - No Report GUI
516074 178 10-AUG-01 CSTRBICR6G Cost Rollup - Print Report GUI
1417551 164 22-AUG-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
1449973 160 03-SEP-02 MWCRMRGA Margin Analysis Report(COGS Breakups)
520648 159 13-AUG-01 CSTRBICR5G Cost Rollup - No Report GUI
446007 122 03-JUL-01 CSTRBICR5G Cost Rollup - No Report GUI
392996 120 01-JUN-01 BMCOIN Bill and Routing Interface
The Oracle Concurrent Managers remain on 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.