|
Inside the Oracle Concurrent Manager
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.
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.
-
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:
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.
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.
|