Tuning Concurrent Manager Processing | Oracle 11i E-Business Suite from the Front Lines

< Day Day Up >

Tuning Concurrent Processing is much the same as tuning any other Oracle process on the system. You can turn on an Event 10046 trace level 8 (or 12) for that process the next time it runs (or during a run if you catch it) and determine what the wait events are for the processes. Chapter 14 — Odds and Ends has more detail on a 10046 trace: how to enable it and how to read the results.

Alternatively, you can turn trace on at the application level and have the Concurrent Program create a trace file every time it runs. This may turn out to be resource intensive if used with a report that runs frequently or that does extensive processing, so use this ability judiciously.

Log in as someone with sysadmin responsibility.
Query up the report and enable tracing by clicking on the trace button within the application window.
Either run the report or, if it is a report that requires certain conditions to occur, allow it to run the next time as it usually would. If you are going to allow it to run on schedule, make a note of what the next scheduled time is for it to run.

In the UDUMP destination directory on your system, look for a trace file (.trc) that has a time stamp near to the time that the Concurrent Program ran. Trace files can get quite large so to get the complete trace file, you will need to set max_dump_file_size at the database level for the periods that you are tuning to unlimited and remember that you will need to remove old trace files from the udump destination directory to keep the file system from filling up. Either of the following commands will provide you with the trace files in the directory and the time stamp on those trace files.

 $ ls -al

 $ ls -lt

You can run tkprof on the resulting trace file to see if you can determine the long running processes. A simple tkprof to run that will give you the explain plan for the processes can be gotten with the following command:

 $tkprof ora_1234567.trc <reportname>.out explain=apps/apps

This will give you a formatted out file that you can either look at and make tuning attempts to or upload to Oracle Support in reference to an iTAR so they can help you with tuning suggestions.

Specializing Managers

Ordinarily, your concurrent requests get placed into the database table and are read by the Concurrent Manager and that manager starts the program running if the manager is defined as able to run that request. Without specialization rules (a set of instructions that associates an action with a particular kind of request), any manager can read any request and run the requested program whenever the program is scheduled.

You can narrow the requests that are applicable to a Concurrent Manager using specialization rules. For example, you can tell a manager that it is only allowed to run a program that belongs to the product General Ledger or to be run by the user jsmith. You can further narrow the scope of the Concurrent Manager by combining rules (making one manager applicable only for those General Ledger jobs that are being requested by jsmith).

To define a new specialization rule, you first need to decide if you are going to define an Include rule (which tells a manager to only read the specified type of request) or an Exclude rule (which defines all of those requests that a manager is not allowed to run, although all others are fair game). The attributes of a requested program on which you can include or exclude are the Oracle ID of the request's Set of Books (if the enterprise is defined as Multi-Org), the program itself, the requested type of the program, the user who submitted the request, or any combination of these rules. Include and Exclude rules are evaluated using Boolean logic. Table 12.10 gives the type of rule, the logic that applies to that type of rule, and what order the rules are evaluated in.

Table 12.10: Include/Exclude Rule, Logical Equivalent, and Precedent
Rule Type	Evaluation Operator	Precedent in Evaluation
Include	Or	Include rules evaluate first.
Exclude	And
Combination of Include and Exclude		Include rules first then Exclude rules in an (Include X or Y) and (Exclude A and B).

Therefore, if you have created rules such that you include program X, include user jsmith, exclude program Y, and exclude user jdoe, the combinations and their ultimate outcome can be found in Table 12.11. One special note, an Exclude rule always overrides an Include rule.

Table 12.11: Includes/Excludes and Their Outcome
Rule	Results
Include program X	Includes program X.
Include user jsmith	Includes user john smith.
Exclude program Y	Excludes program Y.
Exclude user jdoe	Excludes user jane doe.
Include user jsmith and exclude program Y	Run all programs submitted by john smith and never any request for program Y.
Include program X and Include user jsmith and exclude user jdoe	Run all programs submitted by john smith or any program X that gets submitted but never any program (including program X) that is requested by Jane Doe.

You can specialize a Concurrent Manager based on a rule on requested type of program. While you can define many different types based on your company and their business rules, three common types are Quick, Overnight, and Month End Reports.

A Quick type might be assigned to Concurrent Programs that take a relatively short time to run.

Overnight request type could be assigned to Concurrent Programs that take a long time to run, to a program that is extremely resource intensive and does not need to be run during business hours, or to a program that you typically schedule to run during late night or early morning hours. I would assign gathering statistics and purge jobs to this type of program.

Month End Reports would be assigned to all Concurrent Programs that generate the reports that are run routinely every month end. If you have a specific set of reports that are run every month end, you can define this type, assign the type to each of the programs in the set, and define a specialized Concurrent Manager with rules set such that the only time the manager wakes up and runs is during that period of the month defined by your company as month end.

There are three basic steps to specializing a manager by defined request type:

Define a request type through the Concurrent Request Types form.
Assign the request type to each Concurrent Program that you choose to identify as a program of their type. This is accomplished through the Concurrent Programs form.
Select the request type when you specialize your new Concurrent Manager through the Concurrent Manager form.

Multiple Work Shifts

Defining multiple work shifts will help you distribute processing from prime time work hours to evening and weekend processing. While, by definition, the Standard Manager is active and working 24 hours a day, 7 days a week (or as many hours as the system is available), it can become a bottleneck when multiple, long running processes are started or scheduled to start at around the same time. To help remedy this, multiple work shifts can be defined and managers assigned to work those shifts. If, for example, you know that you have a dozen long running, resource intensive jobs that start at right around 10 P.M., you can define a work shift to run from 8 P.M. to 7 A.M. to which you can offload some of the processing from the Standard Manager.

< Day Day Up >