Debugging General Performance Issues with Oracle Apps
By mzshaw on May 18, 2007
Identifying performance bottlenecks can sometimes be a black art with any distributed computing system. It is sometimes difficult to know where to start. This article gives some high-level guidance on the sort of information you may need to gather in order for Oracle Support to assist you with this task for the E-Business Suite.
Performance issues can potentially occur across any or several different areas of the Technology Stack, or may be restricted within Functional Code. For example (but not restricted to)
- Architecture issue (e.g. high latency WAN, firewall)
- Operating System problem or resource constraint
- SQL or general RDBMS configuration issue
- Database Deadlock
- Apache Listener
Types of Performance Issues
These issues will hopefully be relatively straightforward to define and investigate. For example: -
Single report consistently slow, SQL trace discovers one or more SQL statement(s) taking the majority of the elapsed time
More complex issues
These can take more work to be able to confidently define the real issue and often involve complex investigations involving different parties and much data gathering and analysis
For example :-
Any intermittent issue, System wide issues, Issues where SQL trace time represents only a small proportion of the elapsed time
Gathering Data for Performance Issues
It's helpful to follow a systematic process for investigating performance issues. First steps include identify the nature, scope and extent of the problem.
1. Identify the Extent of the Problem
Determine whether the extent of the problem is:
- System Wide
- Confined to one Technology Are. For example does it only happen in one of the Self Service, Forms or Reports areas
- For a specific Product Area(s) For example, were it to only occur in HR, GL or iStore
- Single Report/Form/Page
- Which Instance or instances is the problem observed. Can it be reproduced in UAT/TEST instances
2. Narrow the Scope Further
Determine whether the problem occurs for:
- All users
- Only one geographic location
- Only for users with certain Browser/type of PC
- Only during peak periods
3. Qualify the Nature of the Problem
- Is the problem intermittent or reproducible?
- Is the problem due to slow performance or a process hanging or spinning?
- When was the last time the process was completed without experiencing poor performance? Document any changes since then.
- Is there a workaround available? For example, restarting Browser or restarting Apache.
- What is the frequency of occurrence?
4. Capture Additional Clues
- Document and differentiate factual information from user perceptions. Example - users may say "it's always slow" but reality may be that it takes 10 seconds most of the time, but 40 seconds between 10am and 11am (peak load).
- How long does it take for the process to complete when running slowly? How long did it previously take (before having the performance issue)? What is the expected performance for this process?
- What is load on server(s) when issue occurs (CPU/Memory/Network)? Is there any unusual activity when problems occur? For example, memory used suddenly growing or CPU at 100%.
- Is the problem reproducible with other browsers, such as Firefox, as well as with Internet Explorer?
5. Identify likely causes and eliminate other possible causes
- Are there any customizations? If so, can they be eliminated for testing purposes?
- If the problem only occured since patches have been applied or any configuration changes have been made, can these be reverted?
- Do you have any Resource Limits enabled on the database that may be effecting the Applications users runtime or Concurrent Manager?
- Does the number or frequency of certain Concurrent Requests correlate to performance issues occurring?
- Have the database parameters been configured as per the current recommendations in Database Initialization Parameters and Configuration for Oracle Applications 11i (Metalink Note 216205.1)?
- Does AWR or Statspack show anything unusual for Top waits, Top buffer gets, etc?
- Have you searched Metalink generally, but specifically reviewed Recommended Performance Patches for Oracle E-Business Suite (Metalink Note 244040.1) for any known performance issues, tuning guidelines and/or patches?
Getting Help from Oracle Support
Once you understand the issue and reach the point where you need Oracle Support involved, it may be useful for you to review the "Performance Tuning" section of:
Working effectively with Oracle Support (Metalink Note 166650.1).
The first key decision you need to make is selecting a product code for your Service Request (SR):
- If the issue is with an individual Form/Report/Page or only in one product area then log the SR for that particular product support team
- If issue seems to be with the Technology Area or System wide then log the SR with the AOL team (Oracle Application Object Library)
It is very important to provide a good problem definition (nature, scope, extent) so be as verbose as needed to give a good description of the issue.
It is also very important to reproduce the issue on a non-Production instance. If you are able to reproduce the problem outside of your Production instance, then any recomended patches or changes can be quickly assessed for their impact and any detailed debug or tracing required to identify the issue can be easily implemented.
A Quick Aside: One issue per Service Request
You may need several issues investigated simultaneously and may be reluctant to raise different SRs or have different people investigating. Unfortunately, even similar-looking issues can have different root causes, which means they will need to go to different support teams or have different SR statuses. This is why it is important to ensure each issue is raised as a separate SR.
Performance Issues with Specific Forms, Reports, or Pages
For these "simpler" types of issues, we can generally track down the issue with the following information:
- Form, Report or Page name, version and navigation path
- Full versions of Forms, Reports or Framework as well as the iAS and Database versions, including rollup or interop patches applied
- Relevant Family Packs or Maintenance Packs applied
- Description of symptoms, what have you tried, your investigations, conclusions and/or thoughts/ideas
- Does it reproduce constantly, in other environments as well?
- When did you last run "Gather Schema Statistics" and at what percentage?
- When did you last run any relevant purging processes?
- SQL trace with binds and waits (raw file and TKPROF output)
- Wall clock time elapsed from user perspective
- What are the target and acceptable times for this process?
Performance Issues for Other Areas
These are the more complex issues and will normally require additional information (and patience) to resolve. We will need the same information as listed above, but also:
- Description of your System Architecture and network diagram
- Technology Stack configuration files (Apache, Forms, Reports and Database, as relevant to the problem)
- List of any Metalink notes or product documentation you have reviewed already, and what steps you implemented from this documentation
- List of any patches applied specifically to try to address this issue
- Details of profile options or configuration settings you have tried changing, explaining why you tried these settings and what effect they had (if any)
- AWR or Statspack output for "good" and "bad" performing time periods
- Details about your pinning, purging and gather schema statistics strategy
- Relevant Log files
- Debug and trace files
Good starting points for collating all this information are:
Project Documentation Summary for Tuning Oracle Applications (Metalink Note 165300.1)
FAQ and Troubleshooting for Oracle Configuration Manager (OCM) (Metalink Note 369619.1) as this provides an automated way to provide Oracle Support with much of this information
Additional Tips for Troubleshooting for Apache / JVM problems
Add "%T" to the access_log for timing information as discussed in Proactive Monitoring on an Apps 11i Web Tier Server.
Use Oracle Application Manager to monitor for any "Potentially Leaked" JDBC connections and other information in the "JServ Usage" link
Review Latest JVM Tuning Recommendations for Apps 11i to confirm you have the recommended number of OACoreGroup JVM processes
Additional Tips for OA Framework-related issues
- It is often useful to enable "STATEMENT" level logging (for ONE user only!) using the FND:Debug% profile options if you have a reproducible test case
Performance issues can sometimes be tricky to isolate, particularly those having more than one root cause. This article has presented some ideas about the approach to take, in addition to the sort of information Oracle Support would likely be asking for if you need to log a Service Request.
If there is sufficient demand, I can write further articles in future, expanding on the topics introduced here.
- Performance Tuning the Apps Database Layer
- Master Class in Apps 11i Performance Tuning
- A Holistic Approach to Performance Tuning Oracle Apps (Metalink Note 69565.1)
- Troubleshooting Oracle Apps Performance Issues (Metalink Note 169935.1)
- Tips and Queries for Troubleshooting Advanced Topologies (Metalink Note 364439.1)