Top Five Errors Customers Make When Maintaining E-Business Suite 12 {6}

This is the last in a series of articles that list a few of the technical myths or misunderstandings about the E-Business Suite. Previous articles have covered installations, cloning, patching, migrations, and upgrades. This article discusses general maintenance.

Each article is absolutely not definitive and I’d be interested in comments from readers who think there are other misconceptions about the day to day maintenance and upgrade of a typical E-Business Suite environment.


COMMON ERROR #1: Manually editing files that are maintained by AutoConfig. 

ORACLE’S RECOMMENDATION: When you run AutoConfig, in most circumstances any manual changes to AutoConfig controlled files will be overwritten. There is a process for adding custom entries to AutoConfig maintained files; you should use this process if you need to make manual changes to files maintained by AutoConfig. Refer to Section 4 of the AutoConfig Note for information on how to customise AutoConfig. 


COMMON ERROR #2: Maintaining local file systems for multiple application tier servers instead of shared file systems. 

ORACLE’S RECOMMENDATION: An environment with, for example, four standalone web/forms nodes on Linux and four standalone Concurrent Processing / RAC nodes on Solaris each using a local file system is a very inefficient architecture that you will very quickly get bored of patching.  Each E-Business Suite patch will need to be applied eight times. This can be reduced to one patch session if Concurrent Processing is placed on the same node(s) as web/forms and a shared application tier file system is introduced. If you cannot achieve acceptable performance from your shared disk subsystem then you should investigate the performance issue on your disk subsystem rather than compromising with an E-Business Suite architecture that has such expensive maintenance requirements. 


COMMON ERROR #3:  Sizing everything in the init.ora as large as possible. 

ORACLE’S RECOMMENDATION: Database tuning is a delicate exercise. Indiscriminately increasing values in the hopes that doing so will automatically improve performance is a dangerous approach. Use the performance tools in Oracle Enterprise Manager (OEM) and other tools to establish performance bottlenecks. Do not change init.ora parameters indiscriminately; all changes should be supported by your own empirical evidence that candidate changes result in performance improvements. Thoroughly stress-test all changes before implementing in production.


COMMON ERROR #4:  Ignoring invalid objects for products you do not use. 

ORACLE’S RECOMMENDATION:  All products are installed in an E-Business Suite installation in the database.  All products must be patched and maintained. Invalid objects, even in products you do not use, indicate an underlying problem in your system that should always be investigated and resolved. 


COMMON ERROR #5:  Performing a complete database export/import to improve performance. 

ORACLE’S RECOMMENDATION: Well possibly, but there is certainly a lot of unnecessary exporting and importing of databases in an attempt to improve performance.  This brings us nicely back to the introduction ofour first article in this series. A significant amount of the data in your database is static. Tables may become fragmented but this is not automatically a performance problem. If this was the case, many databases would have performance problems.

An Oracle database is built to cope with a reasonable amount of fragmentation and will not suffer because of it. Analyse performance problems before assuming the issue will be solved by an export/import. Most performance problems are due to poor indexing of tables and inefficient SQL. Few are caused by table fragmentation.

Debugging General Performance Issues with Oracle Apps

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

 

 

 

mzPerfIssues:

 

Types of Performance Issues

 


"Simpler" Problems

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:



 

The first key decision you need to make is selecting a product code for your Service Request (SR): 



  1. 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 
  2. 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:



 

Additional Tips for Troubleshooting for Apache / JVM problems



 

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

 

Conclusion

 

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.

 

Related



References

 

Related Articles

posted @ 2012-08-28 14:53  dbblog  阅读(261)  评论(0)  编辑  收藏  举报