In my previous article, I talked about what to look for once you have a performance issue with Apps 11i.  In this article, I’ll discuss four maintenance activities that you can do proactively to reduce the chances of encountering certain types of performance issues:

  1. Check performance against a baseline
  2. Follow a regular purging schedule
  3. Gather schema statistics regularly
  4. Follow a systematic pinning strategy

mzPerfIssues:


1.  Check Performance Against a Baseline

  • Create a baseline so you can monitor performance in response to changes or over time. For example, you may create 6-10 repeatable short transactions of at least 10 seconds each, which represent common functions and/or areas of particular concern.
  • Always execute this baseline test from the same PC and the same location in order to get consistent results.
  • Rerun the test as part of your normal User Acceptance Testing for any system changes.
  • Review and update your performance baseline as part of any upgrade project.

2.  Follow a Regular Purging Schedule

You need to ensure that any data that needs to be purged is scheduled on a regular basis. The data that can (or should) be purged will vary between different products, so confirm the recommendations for the specific products you are using.  All customers will generally need to schedule purging activities for the FND and Workflow products.


Concurrent Jobs to Purge Data

Most customers will need to schedule these Concurrent Purge processes:

  • Purge Obsolete Workflow Runtime Data (FND)
  • Purge Debug Log and System Alerts (FND)
  • Purge Signon Audit data (FND)
  • Workflow Control Queue Cleanup (FND)
  • Delete Data from Temporary Tables (ICX)
Naturally, your business requirements may be unique; review our purging documentation for suitability and establish your own list of jobs to run.


Workflow Specific Purging Tips

Review the following documents for Workflow-specific tips on purging:
Also keep track of number of rows in underlying Workflow tables to ensure they are not continually increasing, to ensure the data really is being purged

3.  Gather Schema Statistics Regularly



In general, running this monthly to bi-weekly should be sufficient with 10%, unless there is any known data skew. As with any generic suggestion, this would need to be proven for suitability on your own environment. For example, it is more important to run this when your data distribution changes, rather than when the amount of data changes.

If your environment is a 24×7 system, you should pick “N” for “Invalidate Dependent Cursors” to prevent fragmentation of the shared pool

For more details about gathering statistics, see: 4. Follow a Systematic Pinning Strategy

Despite new 10g features making ORA-4031 errors a rare occurrence, it is still recommended to have a pinning strategy, even with Apps 11.5.10 running on 10gR2 databases.

General guidance


  • Monitor X$KSMLRU for candidates to pin (> 4100 bytes)
  • Do not pin more than 20% of the Shared Pool
  • Review your Pinning Strategy for changing business cycle, such as month end or overnight batch runs
  • No need to pin objects used only for batch jobs
  • Also have an “un-pinning” strategy
For more information about pinning in Apps environments, see:

Conclusion

Running regular maintenance tasks and ongoing monitoring are essential activities to ensure that your system is performing to the best of its ability. This article highlights a few of the areas that are sometimes overlooked in Apps DBA schedules.

Related