Managing the Concurrent Manager (Or “Never
a Dull Moment”)
Barbara Matthews
OnCallDBA.com
Introduction
If you’re the Applications System Administrator or Database
Administrator for Oracle Financials, then this is the paper for you! This paper will help you deal with all the
users, vice presidents and consultants who are lining up to tell you how to do
your job better (“If you would simply give THIS user a higher priority than all
the rest, then everything would run better…” “My job that takes 5 hours to run
is NOT poorly written, your database is poorly tuned”). This paper will describe scripts that I’ve
presented before, along with new ones for both the DBA and Applications System
Administrator. And I’ll share tips and
techniques that you can use to proactively head off performance problems.
Contents
The Concurrent Manager
Setup Questions
How
Many Concurrent Managers Should We Run?
How
Many Target Processes Should We Run?
How
Many Workshifts Should We Define?
How
Do Backups Work With The Concurrent Manager?
The Tools
lockhist
hoghist
top
3rd Party Tools
Concurrent Request Performance History
Concurrent Request History
Concurrent Requests That Errored or Terminated
RMINC_high_requests.sql
RMINC_manager_down.sql
trace
Functionality Issues
The
Internal Manager is Down
Running/Terminating
Requests
Files
Systems Are Full
An
Oracle Error Causes the Internal Manager To Fail
Pending/Standby
Requests Stack Up
Making
Request Sets Land in the Right Queue
Creating
Your Own Concurrent Programs
The Concurrent
Manager
Before heading into the details of how to manage the concurrent
manager, the following are definitions of key terms from the Oracle
Applications System Administration Reference Manual, Release 10, Glossary-1:
“Concurrent Manager: A mechanism that runs concurrent programs.
A manager operates during
the time and days defined by a work shift.
A manager can run any concurrent program, or be specialized to run any
concurrent program, or be specialized to run only certain kinds of programs.
Concurrent Program: A program that runs concurrently (at the
same time) as other programs. Concurrent programs run as background processes,
while you continue to work at your terminal.”
Before you delve into this paper, you may want to read some other
papers that go into more detail about how the concurrent manager works. Take a look at previous OAUG proceedings –
Anthony Pennington’s Administering Oracle
Financials 101, for example,
provides an excellent overview of the roles and responsibilities of the
Applications DBA/System Administrator.
Setup Issues
How Many Concurrent Managers Should We
Run?
Start by leaving the concurrent manager set up the way Oracle
provided, and then make adjustments as you get a feeling for workload.
Keep in mind the following rules of thumb:
1.
In the print screen below of the Administer Concurrent Managers screen, notice that some of the
concurrent managers have an Actual and Target, but are blank for the Running or
Pending columns. These are the
transaction managers. These managers
run immediate concurrent programs.
Don’t adjust the transaction managers.
Since they never show how many requests they are processing, how would
you know if increasing or decreasing target processes helped or hurt
performance?
2.
Consider creating a Fast Requests Manager. With the help of a report called the Concurrent Request Performance History
Report (RMINC), you can assign concurrent requests that always run fast to
the Fast Requests Manager. You can pull
these fast requests out of the Standard queue and allow them to push through
quickly without bottlenecking when you are having performance problems. Note that if, over time, a request stops
running consistently fast, then you should move it back out of the Fast
Requests Manager.
3.
Try to have at least 2 target processes assigned for each of the
concurrent managers. If you have only
one target process and a long-running program ties up a manager, the rest will
have to wait for it to complete.
4.
Remember that there is a balance between available computer
resources for online users and batch processes. If you allow too many requests to run at once, your online users
will suffer, and even your concurrent requests will take longer than normal. Additionally, if you allow too many batch processes
to run at the same time, you may use up Unix resources, causing requests to
fail with odd error messages (like Signal 11s).
How Many Target Processes Should We Run?
It depends. If you allow too many concurrent requests to run at
the same time, online users call in and complain about performance. My goal is
to run at least two processes on each concurrent manager during business hours,
and to consolidate the number of concurrent managers as we improve the
performance of the reports that run within them. The reason I always have at
least two processes running is that if we have only one target process and the
concurrent request turns out to be a long-running one, then all the other jobs
queue up behind it. With two processes assigned to each queue, the probability
that jobs will work through the queue in spite of the problem report improves
dramatically.
The notification software described in this paper also helps move
requests through the concurrent manager. One of the programs, RMINC_manager_down.sql, notifies the DBA
and Applications System Administrator if the internal manager shuts down and
stops processing requests. Another, RMINC_high_requests1.sql,
notifies the Applications System Administrator if any of the concurrent manager
queues has more than 20 requests pending. These programs help us spot problem
programs quickly. Because users are not
particularly predictable in their day-to-day usage of the machine, I find it
difficult to pick a magic number for running concurrent requests. My goal is to
get the requests done as quickly as possible without impeding online
performance. If your server makes quick
work of concurrent requests during business hours and your online users don’t
complain about performance, you can adjust the number of jobs that can run in
each queue accordingly.
How Many Workshifts Should We Define?
The default Standard workshift runs anything at any time. You may find that creating workshifts to
adjust the number of requests that can run on a concurrent manager during
certain time periods is useful. You
might define a Business Hours and Evening workshift, for example:
1.
Business Hours: a workshift defined for 8:00 a.m. - 6:00 p.m. that
minimizes the number of concurrent requests that can run in favor of online
users.
2.
Evening: At 6:00 pm,
switch to the Evening workshift and allow an increased number of concurrent
requests to run. After 6:00 pm, most users go home, so the concurrent requests
get priority. Remember to encourage
users to run their long-running requests during the Evening workshift.
How Do Backups Work With The Concurrent
Manager?
In my old age, I’m finding that I no longer have a sense of humor
about the importance of backups and archiving.
If you aren’t archiving with either cold or hot backups, please don’t
ever approach me looking for a sympathetic ear. The sites I support run nightly hot backups with archiving. In certain situations we’ll take a cold
backup. Some companies like to run a
cold backup once a week. If you have
flexibility in your availability, have at it.
If you do use cold backups, keep in mind that there are some
concurrent requests that don’t take well to being rudely interrupted. You’ll know you’ve hit one of these if the
request restarts but fails repeatedly.
Generally getting them to run correctly again requires manual changes to
the underlying tables that the request is referencing. I suspect that this has to do with how the
programs are written and when they are committing information.
When I plan to shutdown the database for any reason, I also plan
to deactivate the concurrent manager rather than terminate it. Deactivating allows whatever is running to
finish running. Once the requests are
finished, the concurrent manager shuts down.
Deactivating the concurrent manager requires extra attention and
coordination by your Applications System Administrator, but given the possible
problems that occur if you terminate certain requests, the effort is
worthwhile.
General Recommendations
1.
If you add any new managers, name them so it’s obvious that they
weren’t part of Oracle’s seeded managers.
I usually put the company’s name in parenthesis after new managers (ie.
Fast Requests Manager (RMINC)).
2.
Don’t knuckle under to folks who think that their jobs should be
assigned to the Fast Requests Manager “in order to make them run faster”. The Fast Requests Manager is for programs
that do run fast, not programs that
you wish ran fast.
3.
Don’t knuckle under to folks who think that their username should
be given higher priority than all other users.
If you do this, then all of their requests, whether important to the
business process or not, will run ahead of others’. You would be better off creating new managers for key business
processes and assigning requests to those managers. You can control how many requests can run at a time, and if you
need to you can give specific requests a higher priority.
4.
Certainly there are a few concurrent requests that are vital to
your business and should have a higher priority than others, but don’t get
carried away.
5.
If a particular concurrent request always runs slow, then it
probably needs to be tuned a lot more than your database does. Use top
to assess whether more CPUs or more memory may improve performance. In general, if you are CPU or memory bound,
additional resources will help, but if you have long running requests, you
should tune the requests to gain the biggest performance improvement.
6.
My overall performance goal for systems that I support is to have
no online user consume more than 5-10 minutes of CPU in a day, and no
concurrent request take more than 30 minutes to run. With the tools described in this paper, you can determine where
to focus your tuning time, money and effort.
7.
Encourage users to schedule requests to run off hours. This
includes long-running requests as well as repeatable but not business-critical
requests.
8.
Report performance problems with forms and reports to Oracle
Support. Often, they have performance
patches that can help.
9.
Aggressively tune all custom code. Particularly during a rollout, functionality is the top concern
for custom code. Once you know a report
is providing the correct information, tune it.
This is one area where you don’t have to wait for help from Oracle
Support. Note that your tuning job will
never be done. Over time, the dynamics
of your system’s performance may change for a number of reasons:
a. A performance patch from Oracle Support or
your own developers might add an index to improve the performance of a problem
report only to have other reports start taking longer because they use this
index instead of one that was faster.
At one site that I support, I noticed that about once a day, a user
would kick off a request that never finished.
With my eye toward improving performance, we implemented a change that
made this query run in seconds… unfortunately the query that users ran hundreds
of times a day suddenly began to run more slowly because it stopped using a
particular index. Often only a very
clever programmer can help you dig your way out of conflicting query
requirements like this.
b. Reports
that used to run quickly when there was very little data in a table might take
longer and longer as more data is added because of a lack of an index, or
because of syntax choices in the code (using where exists versus in,
for example).
10.
Aggressively monitor the parameters that users enter for long
running reports to ensure that they are not inadvertently running open-ended
reports that provide more information than the user intended to use. A common error that new users make is to run
a report that asks for a beginning number and an ending number, and leave out
the ending number. This is particularly
a problem with the Sales Order
Acknowledgements and the Invoice
Print Selected Invoices reports.
Using hoghist, you can monitor
the history of long running requests and spot problem reports like these. Although I normally hesitate to turn Oracle
code into custom code, these two reports, if they are problems in your environment,
might be worth modifying to fill in the ending number if a beginning number is
entered without an ending number.
11.
Using lockhist, you can
monitor for locks on tables to ensure that users aren’t locking each other out
of tables for an extensive period of time. Locks affect both online and batch
requests. If blocked long enough by
locks, the batch request will eventually fail.
12.
Use top to watch for
runaway requests: a request takes a
long time to run, shows CPU clock time increasing, and has a Unix process id of
1 with no other Unix processes referencing it.
These requests are ones where Oracle or Unix has lost track of a
request. In this case, the process may
not be cleaned up automatically by Oracle. Use the Unix kill command to
terminate runaway requests.
13.
Outside of the concurrent manager, watch for long-running online
transactions where one user is clocking considerably more CPU time than the
other users. The Unix program top
easily shows cases where a user or a group of users are experiencing
performance problems. By honing in on
what screens the users is using, you can focus tuning efforts on making just
those forms run faster.
14.
Treat stopping and starting the concurrent manager like any other
system outage – schedule it! I strongly
recommend that you not take the concurrent manager down during business hours
unless absolutely necessary. If the
concurrent manager refuses to come back up, there is nothing quite like the
enormous feeling of panic that you get as you try to fix a problem, frantically
hoping that your users won’t notice.
Choosing the Verify Concurrent
Manager option from the Concurrent/Manager/Administer
screen will solve most issues that you might otherwise try to solve by stopping
and starting the concurrent manager.
The best thing you can do to balance performance of the concurrent
manager queues is to try different approaches and see what works. Assess what
your company considers most important, and grant more or fewer resources
accordingly. Provide a help desk and
funnel all user problems and complaints through it to help spot usage patterns
and issues. Your users are the best
judges of when you are having performance problems. Unfortunately, you will never be able to stop monitoring
performance. New patches from Oracle,
additional customizations, upgrades, increasing historical data and additional
users will force you to track and manage performance.
The Tools
Following are the tools that I use to hone in quickly on
performance problems.
lockhist
Users get into locking battles - one report, if run at 5:00 a.m.,
finishes in 1 hour, but if it is run during business hours with online and
concurrent requests vying for access to data, it might take 8 hours. Our locktracker programs help us identify
contention. Note that with Version 11
all online users will show as owned by the oracle Unix account. All accounts owned by applmgr are concurrent
requests.
==================================================================
tail -f lockhist
Wed Apr 17 08:40:04 MDT 1996 --- Starting
locktracker
********************** No
lockseekers found ........
Wed Apr 17 08:40:07 MDT 1996 --- Finished
locktracker
Wed Apr 17 08:45:07 MDT 1996 --- Starting
locktracker
CONT 6794 applmgr
5429 craige
PO_HEADERS .02
XREF 6794 applmgr
Printed
Purchase Order Report
Wed Apr 17 08:46:12 MDT 1996 --- Finished
locktracker
Wed Apr 17 08:50:08 MDT 1996 --- Starting
locktracker
CONT 6794 applmgr
5429 craige
PO_HEADERS .09
XREF 6794 applmgr
Printed Purchase Order Report
Wed Apr 17 08:50:44 MDT 1996 --- Finished
locktracker
Wed Apr 17 08:55:07 MDT 1996 --- Starting
locktracker
CONT 6794 applmgr 5429 craige
PO_HEADERS .18
XREF 6794 appl Printed
Purchase Order Report
Wed Apr 17 08:55:56 MDT 1996 --- Finished
locktracker
Wed Apr 17 09:00:11 MDT 1996 --- Starting
locktracker
********************** No lockseekers found ........
Wed Apr 17 09:00:17 MDT 1996 --- Finished
locktracker
==================================================================
In this example, craige submitted the Printed Purchase Order
Report before he committed his changes to the PO that he wanted to report
on. This caused a lock to occur against
the PO_HEADERS table that didn’t go away until we called and suggested he
either terminate the request or control-c out of the form.
Your Help Desk or System Administrators should monitor locktracker results all day long and
proactively help users eliminate locking problems. They can also review the locktracker
results looking for concurrent requests (anything owned by applmgr) locking
with other concurrent requests. Make these programs incompatible with each
other to eliminate the possibility of a deadlock. Deadlocks cause the locking concurrent requests to fail with an
error and spawn a .trc file in / apps / bin / oracle / admin / prod / udump.
locktracker stores its results in a file called lockhist. It’s useful to review this file from time to
time to see if there are any recurring locking problems. At one site that I support, for example,
every time users ran a particular request, locks occurred on a table and never
went away. The DBA team was becoming
exasperated because they were being asked to kill locks on this table around
the clock. With the help of lockhist,
we were able to prove to the development team that we had a recurring severe
performance problem that needed to be addressed.
hoghist
A user runs a job and it is taking more than 30 minutes to run.
The concurrent manager queues start filling up. The pager goes off and warns
that there are lots of jobs in a queue.
Before we wrote a set of programs called hogtracker, I had to search for long running requests in the
Concurrent / Requests screen by issuing a query for jobs that were running
(type Running in the Phase field). Then I looked at the Details for each
request to see which request was taking more than 30 minutes. When I found the
problem request, I looked at the parameters and tried to determine if the user
had made a mistake in setting up the request.
With hogtracker, we simply leave a screen up all day that uses
the Unix tail -f command to show changes. A long running report might look like
this:
=====================================================================
tail -f hoghist
Fri Apr 19 12:06:02 MDT 1996 --- Starting
hogtracker
*********************** No
hogs found ........
Fri Apr 19 12:06:05 MDT 1996 --- Finished
hogtracker
Fri Apr 19 12:11:02 MDT 1996 --- Starting
hogtracker
HOGG 551545 Standard 12:00
Indented Bills of Material Cost Report BILLIES .18 NORMAL
HOGP 551545 BOM, 2, 20, 20, , 19-APR-96 08:00, 2, , , 2,
Fri Apr 19 12:11:04 MDT 1996 --- Finished
hogtracker
Fri Apr 19 12:16:02 MDT 1996 --- Starting
hogtracker
HOGG 551545 Standard 12:00
Indented Bills of Material Cost Report BILLIES .27 NORMAL
HOGP 551545 BOM, 2, 20, 20, , 19-APR-96 08:00, 2, , , 2,
Fri Apr 19 12:16:05 MDT 1996 --- Finished
hogtracker
Fri Apr 19 12:21:02 MDT 1996 --- Starting
hogtracker
********************** No
hogs found ........
Fri Apr 19 12:21:03 MDT 1996 --- Finished
hogtracker
=====================================================================
hogtracker tells us the current time, the concurrent
request ID of programs running more than 10 minutes, the queue the request is
assigned to, when it started running, the request name, the user who submitted
it, how long it has been running, the request’s status, and the request’s
parameters. In this example, we can see
that BILLIES is running an Indented Bills of Material Cost Report and that it
has been running for .27 hours. We chose to let the request continue running
because the Unix System Administrator said that the Unix load was light at the
time and the program didn’t appear to be causing much of a performance
degradation. Often you don’t have that luxury.
hogtracker helps catch performance problems much more quickly
than wending through the concurrent manager screens. If a concurrent request is
causing a significant performance problem, the screens will run slow too! hogtracker
also provides all the crucial information about a request to assess its impact
faster. If a report’s parameters don’t
look right, you can call the user and ask him if that’s what he meant to do.
hogtracker saves its results in a file called hoghist. Periodically, you should work your way through
this file looking for performance problem patterns. At one site that I support, we knew that users occasionally ran
the Invoice Print Selected Invoices
with open parameters, causing a tremendous slowdown on the system. It wasn’t until we looked at hoghist
that we could prove to our development team that users did this at least once a
week since the day we went live. This
helped convince the team to make this concurrent program into a custom report
in order to add criteria that would fill in the ending invoice number if a user
accidentally left out the ending invoice number.
top
top is a
Unix shareware utility provided by most vendors. You could also use glance,
for HP, or other operating system tools that show you CPU usage and memory
usage. top will tell you if you
have enough memory to support your users.
If you are running low on memory, you may need to purchase more. top will also show if there are any
users consuming a large amount of CPU.
In particular, the amount of elapsed CPU is of interest.
3rd
Party Tools or Scripts
You can write scripts or use 3rd party tools to match
up Unix process ids with the Oracle code that they are running. I use DBArtisan
because it is graphical and easy to use.
If I spot a user with top who is consuming a lot of CPU, I run DBArtisan and match the Unix PID with
the query the user is running.
Generally, if the Unix user is oracle and the elapsed time for the query
is high, I kill the request. Remember
that oracle Unix users are online users.
I assume that they’ve control-alt-deleted and left a runaway request if
the process has been running for a long time.
I make note of what the query is trying to do, however. If users are unsuccessfully trying to run
the same query over and over, it becomes a candidate for tuning. If the process is owned by applmgr, then I
know it is a concurrent request and I check to see if the parameters that were
entered are reasonable. If they are,
then I add that concurrent program to the list of programs that should be tuned
next.
If you decide to write scripts to gather information about long
running queries,example queries are RMINC_whosrunning.sql and RMINC_whatsrunning.sql.
How the Tools Work Together
Here’s an example of how lockhist,
hoghist, top, RMINC_whosrunning and
RMINC_whatsrunning pull together to hone in on a performance problem.
1.
Periodically, I check locks and hogs to see if there are any
potential performance issues. For this
example, nothing shows up.
2.
Periodically, I check top to see if there are any issues. Check out the top results:
3.
Notice that the load average on the CPUs is pretty low, only
24%. Take a look at free memory –
38940K. This company has 1.5 gigabytes
of memory available for their users, but only 38M free right now. Clearly something is going on. Now take a look at PID 20317 – it’s been
running for 23:53 minutes, which is much more than I like to see, particularly
for an online user. Remember that online
user accounts have a username of oracle (this company uses oraupi) and that
concurrent requests will show up as applmgr.
I’m going to investigate PID 20317 a bit more using RMINC_whosrunning.sql.
By running RMINC_whosrunning.sql, I can see who the user is and what product
they are using:
SID Status O.S. SID Serial # Type DB User Client User
---------
-------- --------- --------- ---------- -------------------------------
---------------
Server Machine Terminal
---------
----------------------------------------------------------------- ----------
Program O.S.
Program
------------------------------------------------
-------------------------------------------------
Command
------------------------
18
ACTIVE 20317 1852
USER SLS BRONALD
DEDICATED
SYSTEMS SYSTEMS
DISUSR31.EXE oracle@upi_db
(TNS V1-V3)
UNKNOWN
By running whatsrunning.sql, I can see the query
this user is running:
SELECT i106400 as E106400, i106404 as
E106404 , ( SUM(i106401))/ COUNT(DISTINCT i106367)
as E_119 , COUNT(DISTINCT i106367) as
E106367
FROM (SELECT “A$CUSTOMER” AS i106363,
“A$CUSTOMER_NUMBER” AS i106364,
“A$ITEM$ITEM” AS i106365, “A$INVOICE_DATE”
AS i106366
WHERE (o106361.i106418 BETWEEN
TO_DATE(‘19991112000000’,’YYYMMDDHH24MISS’) AND SYSDATE)
And (o106361.i106367 IS NOT NULL)
GROUP BY I106400, i106404
4. There’s my culpit – BRONALD is using
Discoverer to run a nasty query. The
bad news about trying to figure out who is having problems is that unless the
user is logged in directly through SQL*PLUS or is using NOETIX or Discoverer,
you can’t tell who the user is. Oracle
Applications masks all online users as Unix user oracle. Generally if it’s a concurrent request, you
can do some more checking at the Unix level by using the ps –ef | grep OSSID
command and figure out which concurrent request is running and track back to
the user through the application screens.
For BRONALD, I’ll contact him and see if he’d like us to come up with a
better way of querying this information, and if so we’ll tune this.
Concurrent Request Performance History
(RMINC_RPTSUMP.sql) shows all concurrent requests that ran
since a specified date. Since it includes the fastest time that the report has
run (MIN. HOURS), the slowest time (MAX. HOURS), and the number of times the
report was executed, I use it not only as a guide in assigning programs to
queues, but also to help developers decide which programs to tune first.
Report Summary by Program
All Times are Elapsed Time - Ordered by Average,App from 06-FEB-00 thru: 08-FEB-00 Page: 1 # TIMES TOTAL AVG. MAX MIN WAITED AVG. APP DESCRIPTION PROGRAM EXECUTED HOURS HOURS HOURS HOURS HOURS WAIT -------- -------------------------------------------------- -------------------- -------- ------- ------ ------ ------ ------- ------- FND Activate Concurrent Manager ACTIVATE 7 FND Deactivate Concurrent Manager DEACTIVATE 9 FND Verify Concurrent Managers VERIFY 1 CUS CUS_EDL_ARCHIVE_LOAD CUS_EDL_ARCHIVE_LOAD 2 9.06 4.53 4.61 4.45 2.44 1.22 CUS Custom Quick Report By Region By Program CUSERBC 5 4.26 .85 1.44 .55 9.01 1.80 CUS Custom Create REVENUE Table CUSREVTABLE 1 .78 .78 .78 .78 .01 .01 OE Backlog Summary Report WSHRDBLS 1 .65 .65 .65 .65 .00 .00 MRP Memory-based Snapshot MRCNSP 2 .92 .46 .55 .37 .03 .01 MRP Snapshot Monitor MRCMON 2 .88 .44 .53 .35 .01 .00 PA PRC: Update Project Summary Amounts PAXACMPT 3 1.10 .37 .44 .32 3.50 1.17 CUS Custom Quick Report By Customer CUSARCDBR 3 1.05 .35 .56 .19 4.53 1.51 CUS Custom Quick Report By Product Line CUSQRBPL 6 1.95 .33 .50 .13 6.92 1.15 CUS Custom Order Extract FTP CUS_ORDEXT_FTP 14 4.52 .32 1.27 .06 .01 .00 CUS Serial Number Generation Script CUS_SHCON_GEN 2 .61 .30 .32 .29 .02 .01 CUS Custom Sales History Details By Customer R CUSRCUSH2 1 .29 .29 .29 .29 .00 .00 CUS Serial No Generate test CUSSRNOINT 2 .55 .28 .29 .26 .03 .02 CUS Custom Pilot Extract CUS_PILOT_EXTRACT 2 .53 .26 .28 .25 .01 .01 CUS Custom Order Summary Extract CUS_MKTG_SUM_EXTRACT 8 1.92 .24 .62 .02 .03 .00 CUS Custom Order Detail Extract CUS_MKTG_EXTRACT 6 1.40 .23 .90 .02 .03 .00 CUS Custom Sales History Details By Customer R CUSRCUSH 9 2.07 .23 .62 .00 .03 .00 CUS Custom Open Order Backlog Report CUSOPENO 1 .23 .23 .23 .23 .00 .00 CUS Custom Inventory Analysis by ABC CUSV05 1 .22 .22 .22 .22 .01 .01 CUS US Post AutoInvoice Program CUS_US_POST_AUTOINVO 1 .22 .22 .22 .22 .00 .00
Ways to run this report:
1.
If you run thousands of concurrent requests each day, you might
want to create special Purge Concurrent Requests for the most frequently run
requests. You can order this report by
number of times executed to see which requests are run most frequently. The Workflow Background Processor, for
example, is run thousands of times a day at one site that I support, so in
addition to deleting all requests older than 7 days, this company also deletes
all completed Workflow Background Processes older than 1 day.
2.
You might want to tune requests that are run frequently, even if
they don’t take hours to run. Ordering
by number of times executed would show you this information.
3.
You might want to tune requests that on average take the longest
to run, or that have taken the longest time to run. You can order by average hours or max hours to find candidates
for tuning. Keep in mind that ordering
by maximum hours may skew your results if users have accidentally picked
parameters that are too broad. The
Invoice Print Selected Invoice, for example, might pop to the top of the list
if a user forgot to put an ending invoice number in.
4.
Use this report to find requests that consistently run fast and
assign them to the Fast Requests Manager.
Concurrent Request History
(RMINC_rpthis.sql) shows a concurrent request’s elapsed time
history to guide users about the best time to run their programs. This report is also useful to prove that a
program that has been tuned really does run faster, since elapsed times should
decrease after tuning. This report may
also help validate if performance is gradually getting worse for a particular
program. And it answers the question
“how long did a particular concurrent request take to run?”
Report History for Concurrent Request: Custom Quick Report By Region By Program 08-FEB-00 Page: 1 This report shows the report history for a given report. By looking at the parameters that have been passed by other users and the time that it took torun the program with those parameters, you may be able to gauge how long a report COULD run. Try to avoid running programs that have taken more than 30 minutes that have similar parameters to the ones that you plan to select during peak business hours (8am-6pm). For STATUS, the following codes apply: E = Error, C = Completed Normal, X = Terminated, W = Warning It is a little difficult to match the parameters that were passed for some reports with the fields that you entered. Generally they are in the same order that you entered them. If you go to the screen where you enter reports you can compare what you were asked for with the translated parameters that were passed and generally get an idea of what is going on. If you run this report and notice a pattern emerging - for example, if every time you ran it a certain way, say for one month of data, it took longer each time, then call it in to the Help Desk - it may be that as we add more and more data, we need to add an index to improve your performance. Without the index, the report SHOULD take longer each time Started Finished REQUEST PROGRAMID User Parameters Status Minutes ------------------ ------------------ ---------- ---------- ---------- ---------------------------------- ------ ---------- 01-feb-00 03:25:48 01-feb-00 04:17:25 4219934 37272 JMURSOM , 01-NOV-1999, 31-JAN-2000, EURREGN, C 51.62 01-feb-00 04:17:56 01-feb-00 05:53:36 4219935 37272 JMURSOM , 01-NOV-1999, 31-JAN-2000, , C 95.67 01-feb-00 09:32:54 01-feb-00 11:32:50 4227714 37272 JMURSOM , 01-NOV-1999, 31-JAN-2000, NAMREGN, C 119.93 01-feb-00 11:33:46 01-feb-00 12:15:51 4227726 37272 JMURSOM , 01-NOV-1999, 31-JAN-2000, LATAMER, C 42.08 01-feb-00 12:16:30 01-feb-00 12:50:53 4227736 37272 JMURSOM , 01-NOV-1999, 31-JAN-2000, ASPREGN, C 34.38 01-feb-00 12:51:33 01-feb-00 16:02:10 4227744 37272 JMURSOM , 01-NOV-1999, 31-JAN-2000, JAPREGN, C 190.62 07-feb-00 07:09:35 07-feb-00 07:42:25 4390192 37272 JMURSOM , 01-FEB-2000, 29-FEB-2000, EURREGN, C 32.83 07-feb-00 07:42:48 07-feb-00 08:17:32 4390193 37272 JMURSOM , 01-FEB-2000, 29-FEB-2000, , C 34.73 07-feb-00 08:18:01 07-feb-00 09:44:19 4390195 37272 JMURSOM , 01-FEB-2000, 29-FEB-2000, NAMREGN, C 86.30 07-feb-00 09:44:49 07-feb-00 10:41:06 4390196 37272 JMURSOM , 01-FEB-2000, 29-FEB-2000, LATAMER, C 56.28 07-feb-00 10:41:28 07-feb-00 11:26:48 4390197 37272 JMURSOM , 01-FEB-2000, 29-FEB-2000, ASPREGN, C 45.33
Concurrent Requests That Errored or
Terminated (RMINC_concmgrerrors.sql) - Users’ reports error
out. This report tells us any
concurrent requests that have errored out or that are running/terminating. If you have a Help Desk, they should run
this report daily and proactively work with users and developers to resolve
problems. Note that the user friendly Error Message is almost always almost
useless. The REAL error message is embedded in the log file.
Requests That Errored Out
Failed At Request Program Name Requested By Parent ID ---------------- ------- ------------------------------------- --------------- --------- Error Message ----------------------------------------------------------------------------------------------- FEB-08-00 12:39 4427943 Custom Sales Order Acknowledge ETARTER -1 Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 4427943. Review your concurrent request log and/or report output file for more detailed information. FEB-08-00 09:45 4424274 Custom Sales History Summary By GOWALWALLA -1 Concurrent Manager encountered an error while running Oracle*Report for your concurrent request 4424274. Review your concurrent request log and/or report output file for more detailed information. FEB-08-00 00:52 4412715 Mass Additions Post (Request Set Stage VTECKER 4412714 The stage completed with the outcome Error. This outcome was computed by the stage function. FEB-07-00 20:19 4407614 Org Items Import (Import Items) NFLITE 4377495 -1422 INVPOPIF: Error in VALIDATE phase; Please check mtl_interface_errors table INVPVALI.validate_item_header3ORA-01422: exact fetch returns more than requested number of rows
RMINC_high_requests1.sql -
lets the DBA and Applications System Administrator know if the number of
pending requests in any queue is more than 20, suggesting that a long-running
concurrent request may be consuming memory and CPU and degrading overall system
performance. It is also possible, however, that a user or users simply kicked
off a large number of requests. In
order entry, a user working on a very large order can release holds on hundreds
of lines, triggering hundreds of event alerts for the user. RMINC_high_requests.sql passes the
number of requests that are pending to give an idea of the severity of the
problem and whether it is getting worse over time, especially if the DBA is
away from the computer and is debating whether to come back and look at the
queues.
RMINC_manager_down.sql -
lets the DBA and the Applications System Administrator know if the Internal
Manager has shut down.
trace
OK, so now that you’ve cleverly determined that you have
performance problems with code, how can you figure out what Oracle thinks is
going on? In this section I’ll describe
how you can document what the problems are.
I’m not going to cover how to tune – that’s a whole separate exercise,
and I’m not particularly good at it.
What I am good at is spotting performance problems, honing in on exactly
where they are occurring, gathering information about what Oracle thinks is
going on, and then passing it on to skilled performance tuners who can come up
with ‘make the computer go faster’ buttons.
Following are three ways to trace performance problems through the
applications:
For Performance Problems With Forms:
An online user says that whenever they use a certain form a
certain way, they have a performance problem.
You can hone in on exactly where the problem occurs by having the user
turn trace on for their session, run the slow process, and then turn trace off
again. Here’s how it works:
1.
Have the user show you exactly what runs slow.
2.
Have the user select Help/Trace right before they do the part of
the transaction that runs slow. Trace
will ask for a password – it’s looking for the apps password, so type that
in. Then have the user do the slow part
of the transaction. Then have the user
select Help/Trace and turn trace off.
3.
Now leave the user, go back to your own computer, and as Unix user
oracle go to the $ORACLE_BASE/admin/dbname/udump directory. Type ls –lt and find the newest trace file
that has a timestamp close to when you and your user were creating the trace.
4.
Now you can run tkprof on the trace file using a command like the
following:
tkprof
ora_5555.trc barb.prf explain=apps/apps
5.
If you’re good at looking at trace files, then have at it. Otherwise, you can give this trace file to a
developer, or if the form is an Oracle-seeded form, you can send it to Oracle
Support and have them look at it.
For Performance Problems With Concurrent
Programs:
You’ve noticed by running the Concurrent Request Performance
History Report and ordering the report by average time to run that a particular
program is taking, on average, two hours to run.
1.
You can turn trace on for this report by logging in with the
System Administrator responsibility, and navigating to
Concurrent/Program/Define.
2.
Query up your report.
Click on the Trace button. Now
every time this report runs, until you turn trace off, it will generate a trace
file. I strongly recommend that you
trace concurrent programs on your test environment rather than your production
environment, because anytime anyone runs this report, it will generate a trace
file.
3.
Now run the report.
4.
As Unix user oracle, go to $ORACLE_BASE/admin/dbname/udump. Type ls –lt and find the newest trace file
that has a timestamp close to when you started running your concurrent request.
5.
When the request finishes, you can run a tkprof on the trace file
using a command like the following:
tkprof ora_5555.trc barb.prf
explain=apps/apps.
6.
Once more, if you’re gifted at tuning, you can look at the output
file, see the explain plan, and come up with ways to tune the report. Note that if the report is an Oracle-seeded
report, you can certainly come up with ways to make it run faster, but
generally all you can do is tell Oracle Support your ideas. If optimizing the report requires
modifications to it, then you’ll become the owner of custom code if Oracle
can’t help you out.
For Performance Problems With Someone’s
SQL:
Once in a while as I’m monitoring one of my customer sites, I’ll
see a request running from top that is consuming lots of CPU and is
owned by the Unix user oracle. When I
look for further information about the process using DBArtisan, I might see the process is owned by a user called
NOETIX. When I look at what the request
is doing, it’s generally a very long-winded conglomeration of a pile of tables
and columns.
1.
In this case, there’s no way to trace the request through the
applications. But you can take the sql
statement and run the following:
2.
alter session set sql_trace =true;
select
yadayadayada from tablename where such = so;
alter
session set sql_trace = false;
3.
When the statement finishes or you break out of it, move to
$ORACLE_BASE/admin/dbname/udump as Unix user oracle. Type ls –lt and find the newest trace file that has a timestamp
close to when I started running this statement.
4.
Run tkprof on the trace file using a command like the following:
tkprof
ora_5555.trc barb.prf explain=apps/apps
5.
Tuning code generated by developers or users is fair game for you
or your development team. Generally it
would be unreasonable to send this code over to Oracle Support and ask them to
tune it.
Concurrent Manager Functionality Issues
The Internal Manager is Down
Symptoms:
If you look at the Internal Manager (as SYSADMIN responsibility,
Concurrent / Manager / Administer), the number under Actual Processes for the
Internal Manager is 0.
NOTE: The other concurrent managers (Inventory, MRP Manager, etc.)
could have a 0 in them, and justifiably so. Some of the concurrent managers
might be set to run only during specific workshifts. The first thing to do if you think the Internal Manager is down
is tab over to the Control field and hit F9 (Quick Pick). Select Verify
Concurrent Manager from the list, then press F10 (Save). If you immediately get
the response: APP-01377 Internal Manager is not active then you definitely have
a problem. You’ll need to quickly
assess if the Internal Manager should be restarted or if you’ll have to do
cleanup work before restarting it.
Following are some of the things that can cause the Internal
Manager to fail.
NOTE: Don’t blindly restart
the Internal Manager - you’ll want to
avoid causing additional requests to error out.
We’ve had the concurrent manager fail for all kinds of reasons.
Following are some of the problems we’ve encountered.
Running / Terminating Requests
Symptoms:
Sometimes if a request has been running for a really long time,
terminating the request through the concurrent manager isn’t enough. The job
may stay in the queue with Phase Running and Status Terminating (see Concurrent
/ Manager / Request). If the job is still
running after a few minutes then it is possible that Oracle or Unix have
somehow lost track of the request.
You may notice that you have this problem if you look at your
concurrent manager and see requests processing, but notice that the Internal Manager
has Actual set to 0 and Target set to 1.
You may be able to resolve this problem without users noticing a
problem.
Another way that you can tell you have this problem is by looking
at the $FND_TOP/logdbname/std.mgr file. Go to the end (hit the ] key twice) and
work your way backward up the file looking for error messages. If you see a message like APP-01089 AFPTPR
cannot terminate concurrent request 43489 with controlling manager ID 4744 and
manager’s operating system process ID 25036:No such process a process no longer
exists according to Unix, but Oracle’s concurrent manager thinks it should
still be running.
To fix this problem:
Log onto SQLPLUS as user APPS.
Enter the following command:
update fnd_concurrent_requests set phase_code=‘C’, status_code=’D’ where request_id=reqid;
commit;
This command sets the phase for your concurrent request to
completed and the status to canceled.
Go back into the concurrent manager and query up running jobs. This job
should no longer be part of the list.
If the internal manager’s Actual is 0, you’ll need to restart the
internal manager. If you cannot restart
the internal manager, then you must have another request whose status is
terminating. In order to get the
internal manager to restart, you must deal with the terminating request. All attempts to restart the internal manager
will fail until you do so.
File Systems Are Full
Symptoms:
1. Users call saying that they are having
difficulty committing changes in the applications screens and that they see the
message Out of record buffers, commit
changes and clear form to continue at the bottom of their screens when they
are doing online work and/or
2. Concurrent requests start erroring out with
the message
^REP-0081: Error during file I/O
operation. scafa 3
From a Unix prompt, type df -k to see if any file systems have
capacity filled to 100%. If any of them do, then you must remove, move or
compress files from them to recapture space.
Immediately deactivate the concurrent manager so additional
reports won’t fail, and then start analyzing the possible problems.
When you setup Oracle Applications, you make decisions about where
you store certain types of files. If
you look in your $APPL_TOP/dbname.env file, it has certain parameter settings. The ones of interest here are:
APPL_TOP
APPLCSF
APPLLOG
APPLOUT
APPLTMP
APPLPTMP
REPORTS25_TMP
FORMS45_OUTPUT
A key to avoiding space problems is to make sure that all of these
variables point to locations with plenty of space. If your parameters are pointing to small disks, or to a default
location like /var/tmp, then you’ll likely have problems as usage increases
with running out of space.
Assuming that you’re pointing to locations with plenty of space,
you also need to do some periodic cleanup of files that land in these
locations. In particular, you need to
run the Oracle-seeded program Purge Concurrent Requests and/or Manager Data
daily. Most companies run this report
and delete all records older than 7 days.
The purge program deletes not only from the fnd tables, but it also
deletes your older log and out files.
Since you run nightly backups (you do, don’t you?), deleting data and
files shouldn’t be a problem – you can always retrieve them from tape if
necessary.
Two things may unexpectedly cause your APPLLOG or APPLOUT files to
fill up:
1.
If a user runs an enormous report with broad parameters, producing
a very large output file or
2.
If certain profile options are turned on, including:
AX: Trace Mode
AX: Debug Mode
MRP: Trace Mode
MRP: Debug Mode
OE: Debug Trace
OE: Debug
OSM: Debug
PA: Debug Mode
PO: Set Debug Workflow ON
RLA: Debug Mode
If you do fill up these directories, act quickly and do the
following:
1.
Deactivate the concurrent manager
2.
Find the largest files and move them or compress them.
3.
Before restarting the concurrent manager, make sure that the LIBR
processes are all gone by running the command ps –ef | grep LIB. This
lets you know that the concurrent manager deactivated. Look for runaway processes and kill
those. Runaway processes will be those
owned by applmgr, since you only need to worry about concurrent manager
jobs.
4.
Once you’re sure that everything related to the concurrent manager
has stopped and that you have enough space to continue processing, restart your
Internal Manager. Be sure to check to
make sure that it really has restarted.
If it fails to restart, you may have some requests running with a status
of Terminating. See the section on
Running / Terminating Requests to deal with this problem.
5.
Run the Concurrent Requests that
Errored or Terminated Report (RMINC_concmgrerrors.sql) and contact users to let them know what
happened.
6.
Once you get the Internal Manager restarted, you may want to run
Oracle’s Purge Concurrent Requests and/or
Manager Data report to free up some additional space. This is a cleaner way
to regain space than going directly to the log files and deleting them
manually.
Cleaning up from this problem is half the battle. You should resolve never to have this happen
again! Not only is it very disruptive,
but when programs fail unexpectedly while running, you can have unanticipated
changes to your data. To avoid problems
like this in the future, follow these steps:
1.
Add code to your crontab that monitors and pages you if any of
these file systems fills to more than 85% (see u01_cap for an example).
2.
Add code to your crontab that deletes .trc files from
/apps/bin/oracle/admin/dbname/udump periodically (we run it nightly and delete
all .trc files older than 8 days). We
also automatically delete files older than 30 days from bdump, cdump, and
adump.
3.
Run the Oracle-seeded report User Profile Options periodically to
catch users who have left debug or trace options turned on.
4.
If your file systems are filling up, then buy more disk and
allocate more space! The default location for the temporary files generated for
forms and reports is /var/tmp. If your /var/tmp isn’t big enough, then modify
$APPL_TOP/dbname.env and change the location for the variables that I’ve
described to a location with more available space (note that to do this, you
must take the concurrent manager down, make the change, and then bring it back
up).
5.
Run Oracle’s Purge
Concurrent Requests and/or Manager Data daily.
An Oracle Error Causes the Concurrent
Manager to Fail
Symptoms:
The Internal Manager has a status of 0 and you notice error
messages in the $FND_TOP / log_dbname / std.mgr file and/or
$ORACLE_BASE/admin/sidname/bdump/dbname_alert.log.
Sometimes a database error makes the Internal Manager fail.
Usually when that happens I’ve found the error both in the std.mgr file and in
the bdump/alertdbname.log that stores all the ups and downs of the database. In
this case you should fix the problem and then try to start the Internal Manager
back up. Oracle database problems that
can cause issues:
1.
One of the tables or indexes used by the concurrent manager, like
fnd_concurrent_requests, cannot allocate another extent because it has reached
its maximum number of extents.
2.
The tablespace APPLSYSD or APPLSYSX, where your concurrent manager
stores the tables it relies upon, runs out of space and cannot allocate another
extent.
The best thing your DBA can do to avoid these problems is monitor
and manage your database objects and the space they use carefully.
Pending/Standby Requests Stack Up
Symptoms:
1.
You notice that the number of pending requests for the Conflict
Manager is getting very large, and that concurrent requests are stacking up in
the Conflict Manager queue with a status of standby.
2.
Someone requests that you move a concurrent program out of a
particular queue and into another one. The request moves over to the queue but
never starts up.
Jobs that “belong” to the Conflict Manager are concurrent requests
that will run under one of the other managers but are currently in standby
mode. Standby requests are requests that will not run until some dependency
goes away. Occasionally we’ve had the Conflict Manager start queuing up large
numbers of standby requests that just don’t go away. While the concurrent
manager doesn’t fail because of this, the jobs simply don’t move from their
standby status.
Before you conclude you have this problem, go to
Concurrent/Program/Define and query up the request in the Name field. Select
Incompatible Programs. Ensure that the problem isn’t simply that your program
is incompatible with another program that is currently running. If it’s just an
incompatibility, then the program will run when the other program completes.
There is, however, a more subtle issue with incompatible programs
that you should be aware of. Say you’ve created a special Off Hours queue to
run incredibly long-winded requests that really shouldn’t run during the day.
And let’s say a user submits a request that is incompatible with another
request that is pending in that queue. The second and additional requests will
start piling up.
Here’s an example of the dilemma: We have a program called
Consolidated Parts Listing. It takes hours to run, so I assigned it to the Off
Hours queue. The Off Hours queue runs requests from 6pm-8am. We have another
program called Parts Listing. Users can run it anytime. It is assigned to the
Standard queue. The Parts Listing and Consolidated Parts Listing reports have
been set up in Concurrent / Programs / Define to be incompatible with each
other to avoid locking problems. If I submit the Consolidated Parts Listing and
then submit a Parts Listing, the Consolidated Parts Listing will show up in the
Concurrent / Requests screen with a Phase of Inactive and a Status of No
Manager. The Parts Listing will have a Phase of Pending and a Status of
Standby. The Parts Listing reports will pile up in the Standard queue. They
will not run until the Consolidated Parts Listing runs in the evening unless we
give the Consolidated Parts Listing a better priority than the Parts Listing.
Worse yet, if you are trying to figure out why the Parts Listings
aren’t running and look at Concurrent / Requests, since the Consolidated Parts
Listing isn’t running, you won’t be able to identify it as the problem. The
Concurrent / Manager / Administer screen, on the other hand, shows the phase of
the Consolidated Parts Listing as Pending (not Inactive) and the status as
Normal (not No Manager).
So when you are trying to track down incompatibilities for
requests that are in standby mode, you need to look at the Concurrent / Manager
/ Administer screen, not the Concurrent / Requests screen. You should look for
incompatible requests that are either running or pending. If they are pending
in a queue whose workshift isn’t running, then your requests will stack up.
Changing the priority of the request that is assigned to the disabled workshift
will correct the current problem (but the same thing can happen another
day!). As an alternative, you could permanently
change the priority of the concurrent program to something lower than the job
that should only run at night. Perhaps
the best solution would be to change the priority of the job that should only
run at night to, say, 51. Then the
request that runs during the day will always have a lower priority, but you
won’t have to worry about it having a higher priority than the normal daily
jobs.
Requests can also stack up if you create a new workshift that
affects a concurrent request that was already pending under an existing
workshift. Also, if you recently reassigned a concurrent program from one queue
to another and the concurrent program was pending under the original queue,
deactivating and activating the Internal Manager causes the program to move out
of standby mode. Verifying the manager should also cause the program to move
out of standby mode, and would be a better option that stopping and starting
the internal manager.
I believe that these problems have something to do with how the
concurrent manager stores certain information in memory. Page 9-17 of the
Oracle Applications System Administration Manual covers this problem for
changes in printer setup: “Printer setup information remains cached in memory
until the concurrent managers are restarted, when the values are erased and new
values are cached (read into memory). You should issue a Restart concurrent
manager command for all currently active managers whenever you edit an existing
Printer Type, Print Style, or Printer Driver”. The Restart concurrent manager
option rarely works for me, so I generally select Verify Concurrent Manager
instead.
Making Request Sets Land in the Right
Queue
Request Sets by default are assigned to the Standard queue. The Concurrent Manager doesn’t distinguish
between request sets (it sees all Request Sets as a program owned by
Application Object Library called Request Sets).
When you set up a request set, click on the Allow Incompatibility box.
This will cause your request set to appear in the list of valid programs if you
look at Concurrent/Program/Define. If you create request set stages, you should
also click on Incompatibilities Allowed
for the stages so that they too will appear in a Concurrent/Program/Define query.
If you don’t click on Incompatibilities
Allowed, the pieces of your request
set will default to the Standard queue.
An additional feature of the Incompatibilities
Allowed box is that if you leave it unchecked, the programs defined in your
request set will ignore the incompatibility settings that they normally adhere
to when run alone. So if you never want
the Purge Concurrent Requests program to run at the same time as another Purge
Concurrent Request program, and you embed the Purge in a request set, if you
didn’t check Incompatiblities Allowed,
and you run two request sets at the same time, your Purges will both run at the
same time.
For example, if I want to set up a request set called Barb’s Report Set, I first choose Concurrent/Set. Now I can set up my request set, being
careful to click on the Incompatibilities
Allowed button on the screen.
Once Barb’s Report Set
is defined, I can add it to one of the managers. I select Concurrent/Manager/Define
and query up the list of managers. I arrow through the different manager names
and pick the one I want to use. Then I click on Specialization Rules, and then add my concurrent program to the Specialization Rules and commit the
change. My request set will be
pre-pended with the words Request Set.
In this example, when I query for the report in the list of valid
programs, I looked for Request Set Barb’s
Report Set. Note, though, that the request set that I defined was called
simply Barb’s Report Set. I was able
to find it and assign it to a manager other than Standard. If I had left Incompatibilities Allowed unchecked, I
wouldn’t find Request Set Barb’s Report
Set in the list of programs to choose from in the Concurrent/Manager/Define screen. If I change Incompatibilities Allowed back, the request set will continue to
show up in the list of programs. I can
also delete the request set Barb’s Report
Set from the Concurrent/Set
screen by pressing Row/Delete, and it
will go away, but I’ll continue to find it on the list of valid programs under Concurrent/Program/Define.
Creating Your Own Concurrent Programs
Performance tuning from my experience is either a fun time or an
all-out war. When it’s been fun, I’ve
been working with companies who either hand me their problems seeking a ‘go
faster button’, no questions asked, or who pull together as a team to
collaborate through possible issues.
Those issues, if evaluated thoroughly, cover the database, the operating
system, the hardware, the setup of the concurrent manager, network performance,
PC performance, and the performance of code.
When it’s a war, the purpose of the exercise is to find someone to
blame for the poor performance problems.
Perhaps it’s your hardware vendor.
Maybe Oracle Corporation promised a little more than the current release
could deliver. Or the DBAs aren’t
working hard enough, or the Applications System Administrators are refusing to
let everyone run all the jobs that need to run simultaneously. Or maybe the programmers whipped out all
that custom code on time, but not tuned well enough.
Either way, I like to make sure that anything that I do is boldly
obvious to whomever is trying to figure out why things aren’t running fast
enough. The easiest way to do that is
to take all those handy scripts that all good Database Administrators tote
around with them and turn them into concurrent programs. As concurrent programs, the time it takes to
run will become part of the history of your batch processing. The same reports that I’ve described that
show which requests take the longest will also highlight if exporting the
database is taking a long time, or if coalescing the database, analyzing the
database or rebuilding indexes is taking a long time. “Confess freely and often”, that’s my motto.
So this section is going to show you how to take a script and
build it into a concurrent request. No
one will ever have to request a peek at your crontab to see if you’re secretly
causing all the performance issues. All
of your programs can show up right along with the rest.
For this example, we’ll take a script that coalesces all
tablespaces. We can argue about how
often you should coalesce your tablespaces, but that’s a decision that the
Database Administrator for your application gets to make. I’m pretty sure that if you brought in three
consultants and asked them how often you should analyze your database, one
would say every night, one would say once a week, and another would suggest
that you only analyze volatile objects.
How you manage your database is up to you.
Here’s the code for coalescing.
The &1 variable is for the concurrent request ID of your
coalesce. By setting this up as a
concurrent request, &1 will automatically be filled in when you run this:
PROMPT RUN_COAL.SQL
PROMPT Copyright 2000, All Rights Reserved, Reed-Matthews, Inc.
PROMPT
set linesize 180
set pagesize 200
clear buffer
set head off
set termout off
set feedback off
spool &1..RMINC_coaltbsp.sql;
select 'whenever sqlerror continue' from dual;
select 'alter tablespace '||tablespace_name||' coalesce;'
from
sys.dba_tablespaces;
select 'PROMPT ALL DONE;' from dual;
spool off;
spool &1
set echo on
set termout on
set feedback on
@&1..RMINC_coaltbsp.sql
host rm &1..RMINC_coaltbsp.sql
exit;
Log into the Applications as a user with the SYSADMIN
responsibility. Select Concurrent/Program/Executable. Set up
your executable that points to your script. Note that you must have a custom code area defined already. Oracle Applications will look for your code
under your custom area following the patch $CUSTOM/sql (in the example below,
the custom area is called UPI_Custom):
Now select Concurrent/Program/Define. Define your script:
Now select Security/Responsibility/Request. You’ll have to decide who should be able to
run this report. If you’re the DBA or
Applications System Administrator, then query up System Administrator Reports.
Add your new program to the list of reports that the SYSADMIN
responsibility can run:
What you’ve accomplished:
1.
You’ll leave a trail wherever you go. Now your concurrent program’s performance will show up in the Concurrent Request Performance History
Report (RMINC).
2.
You can also set notification options for your requests. That means that whenever this report
completes, Oracle Applications will send an email to you, which you can then
parse for the word Error and then send on to your pager.
3. You
can schedule your requests to run as often as you want. You’ll have the confidence of knowing that
your request will run, even if you’ve taken an outage on your system for some
reason (if your report were set up in crontab, and the system was down when it
was supposed to run, your program wouldn’t get run).
One Final Note:
If you purge concurrent request information from your database
using the Purge Concurrent Requests
and/or Manager Data program, then you won’t be able to gauge performance
from a historical perspective. You can,
if you choose, maintain historical information by creating a table with the
same format as the fnd_concurrent_requests table. Create a delete trigger for fnd_concurrent_requests that inserts
records into your historical copy whenever records are deleted (this would
occur nightly when you run the Purge
Concurrent Requests program). You
can then create another program that deletes information after some specified
time from your historical table. You
might not, for example, feel the need to maintain performance information about
your Workflow Background Process
concurrent requests, since they likely run thousands of times a week. I plan to write another paper for an
upcoming OAUG Conference called “Advanced Concurrent Manager Analysis”. This paper will cover advanced topics,
including how to set up tables, triggers and reports to save and report on
historical information about concurrent manager performance. Watch my website at www.oncalldba.com for
this next paper.
Examples of Reports
All of the programs described in this paper, along with the most
recent slide presentation and a copy of this paper, are available at www.oncalldba.com. The scripts are Unix scripts. You may have
to modify them to use them on your environment. I update these programs occasionally when OAUG users suggest
better ways of handling things, or when we write new programs that I think
might be interesting. Several of the programs had to be changed to work with
Oracle Applications Version 11.
Conclusion
I called this paper “Never a Dull Moment” because I’ve found
keeping the concurrent manager under control to be an extraordinarily
challenging task. If you have written programs that help with this task, I’d
love to hear from you. If you know more reasons why the concurrent manager
shuts down, I’d like to hear that too. And if you can think of ways to improve
these scripts, let me know.
About the Author
Barbara Matthews
Principal Consultant
OnCallDBA.com
Last revised: April
24, 2000