Oracle Application Management Tools

---

Due to the tremendous complexity of Oracle Applications and its surrounding environments, monitoring, maintaining, and administering this suite of products can become time consuming and, at times, stressful. This is particularly true if you are unfamiliar with the suite of products and all of the ways that the parts interact. If you have come from a DBA background, you have some comfort level with maintenance of the database layer, but you may not have the slightest idea when you start out what you are facing with the Oracle E-Business Suite.

Oracle has brought to market several products that will help make your life as an Apps administrator somewhat less time intensive and will provide a series of simple, one stop interfaces through which you can gather information that will allow you to later make more informed decisions on administrative tasks.

Other companies have tools as well that can assist you in your Applications Management duties.

Oracle Management Pack for Oracle Applications

GUI tools have existed to help with monitoring of, diagnosing problems with, and capacity planning for Oracle Applications for several releases in Oracle Management Pack for Oracle Applications (an Oracle Enterprise Manager extension geared to Oracle Applications). However, it may not have a full range of abilities that will meet all of the needs of the Apps DBA and sysadmin.

Oracle Management Pack for Oracle Applications brought with it graphical representation of services to be monitored, automated data collection and management services, and central monitoring and administration of remote systems via intelligent agents (services running on the system whose purpose it is to assist with the data monitoring and collection). It extends the OEM console by allowing it to discover Concurrent Managers, monitor their availability, and notify relevant people if they should become unavailable. This OEM extension allows for a broader library of event monitoring and fix-it jobs that can be run to assist in your support of a fully 24/7 system. While the additional libraries that it brings that are Applications specific are slightly limited, it does provide the added flexibility to add your own custom scripts to the library and allow OEM to run these to assist with monitoring. Further, this tool extends the Performance Manager piece and the Capacity Planning piece of OEM to gather information on Concurrent Managers and Forms Servers that will allow you to better tune your system, eliminate resource contention, and plan for future consumption. In this arena, it can be a valuable tool that allows you to proactively plan for times when you will have resource contention and performance problems and head them off before they occur. Finally, it brings the addition of a Concurrent Process Tuning Assistant. This additional tool allows you to gather and examine historical processing information on Concurrent Processing, concurrent requests, and the Concurrent Managers running on your environment.

Performance Manager

The Performance Manager portion of OEM allows you to monitor the performance of your database and the OS environment where it resides in real time. Through it, you can monitor the performance of the database. It also allows you to monitor the performance of your Web servers and Concurrent Managers and their surrounding OS environments. You can record statistics at predefined times (e.g., peak activity times, off peak, or a combination of these) and play back the recorded statistics at a later time. You can display them in tables or a variety of chart formats not just on one target environment, but across several targets at a time.

Not only can you view (numerically or graphically) data on high level performance points on your system, you can drill further to the details on those performance points to see what went into making up the numbers and you can get advice on how to improve those points where you are having problems.

Advice is given through a Java help interface that provides you with a definition of the performance point, the source of the numbers, and where you can make tuning changes to your system to help you to improve the numbers and the performance of the system. Built like most help systems, it allows you to print the advice that the tool provides.

Performance Manager's integration with Capacity Planner means that you can drill to historic views of all of the displayed data and compare current real-time information on the system with its historical averages for similar periods or historical peak load times and glean a better understanding of what both the historical information and the current information means. This allows you to better plan for future peak load times and allocate sufficient resources to those times.

You can create event triggers to monitor node and database data events and alert you when an event hits the triggering point. This way you can be alerted to problems on the system even when you are not actively monitoring the system and before any end users report issues with the application.

A new feature of the 9i version of OEM's Oracle Management Pack for Oracle Applications Performance Manager is a fairly substantial subset of the features that run through a browser interface. While you are limited in the things that you can do, it is a look at where the interface is heading and a view of things to come in the future releases of the product. It also will allow you to view those settings when you are in a place where you do not have access to the management console. This allows you the flexibility of monitoring when you otherwise would not be able to.

Capacity Planner

Capacity Planner provides a facility to collect different kinds of performance statistics, records that data in the repository database tables, and maintains the information in those tables in a historical format. You can utilize this data through the Capacity Planning interface to analyze the historical data and use that data and its trends to plan for future growth in all aspects of the server and the database. Capacity Planner relies heavily on the Intelligent Agent, a data gathering service that collects data continuously on the server.

The Intelligent Agent collects the performance data for the Capacity Planner at an interval that you specify and stores that information in a binary file format that can then be loaded at an interval that you specify into the Capacity Planner's historical database. The agent will aggregate the data to the time intervals that you define and purge the data from the historical database on a user-defined schedule while allowing for the continual viewing of all current period data.

This data gathering allows you to utilize the information to track the increases in the amount of used space on disk overtime and the increase in total I/O for your server node over time. It estimates what the I/O rate will grow to in the future and assists you in determining what thresholds to set in the Performance Manager. You can publish this historical data to a Web site where administrators can view the data and allow anyone who uses the system to view the growth of the system over time, assisting in the justification for additional resources.

You can utilize the trend analysis to project future values based on the known present and historical values. You can project this analysis out to a point in time (e.g., the end of the quarter, the end of the year) or to a specific value (e.g., when we hit 500 GB or when we hit a 95 percent CPU utilization).

Concurrent Processing Tuning Assistant

The Tuning Assistant provides you a mechanism through which you can report on and examine the historical processing information stored in the database on Concurrent Processing and Concurrent Managers. Through this information, you can determine how better to schedule existing Concurrent Managers and where to judiciously apply new and specialized managers. To assist in this, the Tuning Assistant provides a predefined set of reports that identify known problem areas in Concurrent Processing. It will generate these reports directly against the Application Object Library tables or from data stored in the Concurrent Processing Tuning Assistant Repository.

You can use the information gathered from the Tuning Assistant to find periods with greatest wait times and determine by examining the reports running during these times those that can be run during other periods of time when you have excess capacity. This will reduce wait time for the concurrent jobs and minimize the bottlenecks.

You can run the defined reports and save the results as a comma-separated file (CSV format) that can be read into a spreadsheet or loaded into a table for further analysis or saved as HTML format and published to an intranet page.

The tuning advisor feature of the Concurrent Manager Tuning assistant can help you gather information about situations that may be standing in the way of the performance of your Concurrent Managers. Once you have discovered the problem areas, you can determine if increasing the capacity of the system, resource balancing and request rescheduling, or adding additional managers will benefit the situation. The report in this area will graphically show you how and where you are currently experiencing the bottlenecks and gives you the ability to drill out to further information. Clicking a button from the report allows you to look at the tool's suggestions for how to assist in the tuning and balancing.

But the Tuning Assistant will also point out places where the manager was underutilized, yet requests were left in a waiting state. Because the wait state is not dependent on the manager being overactive and the request having to wait for that resource, and the possibilities being so broad for why it was waiting, the tool is unable to make suggestions on tuning; it merely graphically shows you where you may have other problems in the system.

While it is not a silver bullet, it will give you places to start when examining tuning your Concurrent Managers.

Advanced Event Tests

OEM's Oracle Management Pack for Applications provides a set of Advanced Event Tests that are geared to events that can occur in Oracle E-Business Suite applications. This facility provides for the monitoring of these events during the off hours when no administrator is available to monitor them manually.

While it contains all of the same tests that are packaged as common OEM functionality (like database up and down or space management issues), the Management Pack for Applications adds to this library of events that will help you in your job.

Following is a list of event types, tests available for those tests, and a description of what those tests do for you:

• Fault

  • Concurrent Manager Up/Down (available in OEM AM 8.1.6): Monitors the state of the ICM and alerts you if it goes down.

  • Conflict Resolution Manager waiting on lock: While a wait on a lock is not necessarily an Alert worthy event, this monitors the CRM for excessively long waits and if it reaches a defined point, you will be alerted. This test is not available in OEM AM 8.1.6.

  • Internal Concurrent Manager waiting on lock (available in OEMAM 8.1.6): Again, waiting on a lock may not be an Alert condition ordinarily; however, if the ICM waits too long for a lock and that wait reaches a defined point, you will be alerted.

  • Request Error Rate (available in OEM AM 8.1.6): Monitors the rate of errors for all concurrent requests and when those errors reach your threshold, you will receive a warning or a critical Alert depending on the error rate received.

  • Request Warning Rate (available in OEM AM 8.1.6): Monitors the rate of warnings for concurrent requests and when the warnings reach your threshold, you will receive a warning or a critical Alert depending on the number of warnings received.

  • Unresponsive Concurrent Manager: If any of the Concurrent Managers defined for the current period are down at the beginning of the test and are still down at the end of the test's time period, you will receive an Alert telling you that the manager in question is not responding. Managers not defined for a work shift will not be tested. You will not receive warnings if the manager is caught during its sleep cycle. This test is not available in OEM AM 8.1.6.

• Performance

  • Inactive Request Pending (available in OEM AM 8.1.6): This event checks the state of the requests that are submitted to any or all of the Concurrent Managers and if any of those requests are found to be in an inactive state, it will generate an Alert.

  • Pending Concurrent Request Backlog (available in OEM AM 8.1.6): This test checks for the concurrent requests that have been in a pending state for an extended period of time. If this period exceeds your set threshold, it will generate an Alert.

  • Request Pending Time (available in OEM AM 8.1.6): This test will generate an Alert if a request has been found in a pending state for a period exceeding your set threshold.

  • Run Alone Request Submitted (available in OEM AM 8.1.6): This event test checks the state of all submitted requests. If a run alone request is submitted, it will generate an Alert. This test and resulting warning should be an indication that, if Oracle determines that it is an alertable event, maybe requests should not be submitted in this manner unless the circumstances surrounding the request truly indicate that they should be run in this manner. So far, the only request that I have found that truly qualifies as a job to run without allowing other jobs to run at the same time is the rebuild indexes job.

• Space

  • Concurrent Manager Disk Free: This test checks the growth rate of the log file, output file, and other directory and mount points used by the concurrent requests (or any other directory/mount point determined by you to be one that you want to monitor) and alerts you to space issues. This test requires access to the APPSORA.env file that is within the $APPL_TOP directory. This test is not available in OEM AM 8.1.6.

There are other events tests that you might want to examine further. Primarily, there are node events that may become of interest to you in your capacity as an Apps DBA. Many of these event tests come packaged with the application, either OEM or the Applications Manager add-on. A complete list of all node events can be found in the Oracle Enterprise Manager Event Test Reference Manual.

Available Job Scheduling Jobs

The OEM console allows you to automate many repetitive tasks. The tasks that are typically scheduled in this manner in OEM on a regular system range from the execution of simple SQL scripts to kicking off OS commands. Typically, through the OEM console, you can automate the running of jobs on the database, on the OS node housing the database, on the listener, or on the Apache HTTP server. The jobs include shell scripts, SQL scripts that a DBA may run in a routine manner, or the shutdown or startup of the database, the listener, or the Apache service. The Job Scheduler further provides the ability to configure jobs that, based on the outcome of an event, could automatically be called as fix-it jobs to alleviate the triggering condition. The Scheduler Manager allows you to automatically submit these jobs on a set schedule so that they can be run during off hours or can assist in monitoring the database in off hours so that you, the administrator, can rest easier.

The Management Pack for Oracle Applications includes even more jobs that you can configure to make your Applications centered jobs more flexible and schedule tests against the system so that you can monitor and proactively correct problems on the system before they really become problems. Some of the routine tasks that come prewritten into the Management Pack for Applications are outlined in the following list. Remember that this is just a starting point for the tasks that you could submit through the OEM and Management Pack for Applications interface.

• Concurrent Manager Shutdown: This task shuts down the ICM in the mode that you specify (abort or stop) and does not require the Intelligent Agent to be at any particular release number. If you instruct the job to shutdown the manager in abort mode, the job will shutdown the Internal Manager regardless of what state the queues are in at the time. If you instruct the job to shutdown the Internal Manager in stop mode, it will allow the current concurrent requests to complete their processing before shutting down the Internal Manager elegantly.

• Concurrent Manager Startup: The CM startup job starts the ICM and can be set as a fix-it job that you can associate with the Concurrent Manager Up Down job from the Events Tests to restart the Internal Manager, if it should happen to go down. While neither the event nor the fix-it job take into account what may be the underlying cause of the down manager condition nor attempt to address the underlying cause, it will make the attempt to restart the manager if for some reason it just decides to stop. This job can be triggered through the use of any version of the agent.

• Kill Locking Session: The purpose of this job task is to be called as a fix-it job for either the ICM Waiting on a Lock Advanced Event Test or the CRM Waiting on a Lock Advanced Event Test. Because it is highly unusual for a Concurrent Program to lock out either the Internal Manager or the CRM for an extended period, the Waiting on a Lock event will ordinarily be triggered from a Forms session that has become out of control. If you create this as an event-triggered job, it will go out and delete the job that is preventing the ICM or CRM from continuing its job. This job can be triggered through the use of any version of the agent.

• Load Data into Concurrent Process Tuning Assistant: Recall that the Concurrent Processing Tuning Assistant is a utility that allows you to examine both current and historic information about the Concurrent Processing requests and the Concurrent Managers. Its ability to report on information is limited by the amount of information that it can find still residing in the FND tables that drive the Concurrent Processing. If you are running the purge jobs on a regular basis, the amount of information that is maintained within these tables could be severely limited (or you are expending resources on maintaining the information in the tables for longer than necessary and, as a result, potentially slowing down the access of the Concurrent Managers to the systems). To report on historic information, it is necessary to maintain this information in a repository schema. The Load Data into Concurrent Processing Tuning Assistant job will upload the data from the FND tables into the repository schema, aggregate this data into these tables, which allows for the historical reporting and allows the reports to process faster and easier than it might otherwise.

It is not necessary that this information be maintained in the Apps database; it can reside in virtually any Oracle database. Due to the potential for maintaining a massive amount of data, it might be more prudent to not store it in the Apps database and not store it in the same schema and tables as the OEM repository. The first run of this job will load nearly 20 percent of the data from the FND tables into the tables in the repository tables and every subsequent run of the job will load all of those rows that have been added to the table since the previous run. The size of the repository will continue to grow in proportion to the amount of Concurrent Processing that is occurring on your system, so resource planning for the repository information should become easier as you examine the trends in growth that accompany several cycles of loads with this job.

Drawbacks to OEM Tool

One of the drawbacks of the OEM tool is that it can be resource intensive on a client machine and can be extremely slow to update if there are contention issues on your servers. The Java-based interface is very robust and provides you with quick answers to many questions. However, while it is refreshing, you cannot access anything in many cases and it becomes a tremendous resource drain at times.

Further, you have to be at an OEM console to interact fully with the system. This means that any computer that you may have to be at either has to have the interface loaded onto it or you will have to have it on a central server. This causes the added potential of network contention as the console may be accessing all middle tiers and the database tier, while serving this information through the server where the OEM product is running and further to the workstation where you are located.

Also significant, while a limited subset of the features is available through a browser-based interface, this is still in the process of growing to be a viable interface that can provide you the features that will come to make it a well used tool. One way to provide similar information without all of the Oracle Management Pack for Oracle Applications features in a less resource intensive interface is through Oracle Application Manager.

---