(转)Inside the Oracle Concurrent Manager

原文地址：http://www.dba-oracle.com/art_dbazine_conc_mgr.htm

by Terry Oakes & Donald K. Burleson

  The concurrent   managers in the Oracle e-Business suite serve several important administrative   functions. Foremost, the concurrent managers ensure that the applications are   not overwhelmed with requests, and the second areas of functions are the   management of batch processing and report generation.   

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.

Now that we   understand the functions of the master Concurrent Managers, let's take a quick   look at techniques that are used by Oracle Apps DBAs to monitor the tune the   behavior of the Concurrent Managers.

   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

Let's start by   looking at tuning the ICM, and drill-down into more detail.

   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.

All of the   concurrent managers, with the exception of the ICM and CRM, can be configured   to run as many processes as needed, as well as the time and days a manager can   process requests. However, the number of processes needed is dependent on each   organization's environment. An Applications DBA must monitor the concurrent   processing in order to decide how to configure each manager. For a fresh   install of the applications, initially configure the standard manager to run   with five processes, and all the other managers with two processes. After the   applications have been in operation for a while, the concurrent managers   should be monitored to determine is more operating system process should be   allocated.  

Purging   Concurrent Requests

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  

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. At the bottom right of the screen you can check the box   Enable Trace. (Figure 1)

  

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.

 

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. 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.

  

Figure 2: Allocating more processes to the Concurrent Manager.

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

   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:

 

conc_stat.sql

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"

spool long_running_cr.lst
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;
           
spool off

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

            Exec Time 
         
 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

Conclusion

   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.