Preventing Apps 11i Performance Issues in Four Steps

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 24x7 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

Comments:

Steven,

It's funny, but with the new 10g features, I'm running across some newer Apps DBAs who really don't understand the fundamentals of pinning. After allowing you a moment to reflect on how incredible a statement that would have been a few years ago, may I suggest an idea for a future article that reviews basic pinning concepts?

Posted by Floyd Teter on May 21, 2007 at 02:48 PM PDT #

nice articel, but i have a qustion dose the Gather Schema Statistics works with custom schema.

fadi

Posted by Fadi Hasweh on May 22, 2007 at 02:00 AM PDT #

Hi Floyd,

I agree that a few years back most all dbas (apps or otherwise) would have known about pinning. But back then we also didn't have gigs of ram to devote to the shared pool and so on. Right now, I'm a little sceptical about the wisdom of pinning (at least too many objects , say more than 1-25 or so) vs the appropriate sizing of the shared pool. The proof as always will be in the metrics, what is the average response time for your key transactions in a system with pinned objects as opposed to one where we just let the dbms get on with it.

Niall

Posted by Niall Litchfield on May 22, 2007 at 02:53 AM PDT #

Niall,

I can't say that I disagree with your comments on pinning versus sizing the shared pool. Pinning is just another item in a DBA's bag of tricks...and one that is used less frequently these days. My only assertion is that it's difficult to determine when, if ever, to use pinning without understanding the fundamentals.

Posted by Floyd Teter on May 22, 2007 at 04:01 AM PDT #

I think that Floyd did a great comment. Actually with all these Oracle technology integrations you need to know about everything to be a Apps DBA. How all these tools work together.

Do not know the fundamentals of pinning a object is bad in my opinion. Maybe this guys must invest more on Oracle Education or focus in some other specific module such as AP, AR or System Administration.

Regards

Posted by Carlos Duarte on May 22, 2007 at 07:37 AM PDT #

Niall, Floyd,I suspect that some Apps sysadmins came into their current responsibilities sideways, as opposed to rising up to this level from a generic DBA.  Lack of exposure to pinning would be understandable in these situations.  I also agree that pinning is one of those technologies that can be useful but potentially misused, and that a good understanding of the basic principles is needed to use this tool effectively.  Thanks for the comments -- we're looking into putting together a short article about basic pinning concepts now.Regards,Steven 

Posted by Steven Chan on May 23, 2007 at 02:02 AM PDT #

Niall, Floyd,Thanks to your suggestion, Avanish has just posted an excellent primer on pinning here:Pinning Objects to Improve Apps PerformanceRegards,Steven 

Posted by Steven Chan on May 29, 2007 at 08:38 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed
About

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today