Oracle E-Business Suite Technology

Performance tuning is an art that should be executed systematically.  I assume almost everyone has heard this from others talking about tuning, but it's often repeated because of its fundamental truth.

Whenever there's a response time issue for the E-Business Suite, it is initially treated as a performance problem.  I would go a step further and say that poor performance is not a problem by itself.  It is a result of a root cause which lies somewhere else.

Let's dig into each of the possible places for this root cause in the E-Business Suite's technology stack.  Oracle Applications has the following layers:

• Operating system
• Database
• Techstack components (Concurrent tier, Forms tier and iAS techstack)
• Application code (Forms, Jsp etc)

This article will touch on performance-related issues for the database layer. 

Possible Causes for Database Layer Performance Issues

Possible issues include:

• Core optimizer issues
• Known performance-related database bugs
• Incorrect statistics to the cost-based optimizer (CBO), which is responsible for SQL optimization
• Incorrect System Global Area or Program Global Area sizing

Other areas like locking and latching affect performance, too.  For the limited purposes of this article, we'll focus on these four setup-related areas.

Core Optimizer Issues

Most of the E-Business Suite database's recommended parameters are listed in:

• Database Initialization Parameters and Configuration for Oracle Applications 11i (Metalink Note 216205.1)

Setting the values of memory-related parameters as is not a one-time job.  We recommend that you periodically review your Statspack and AWR reports to find if the your current settings meet your current load requirements.

For recommended database init.ora parameters, refer to Note 216205.1.  Pay attention to mandatory parameters.  Based on our benchmarks, we recommend that your Apps setup adhere to the init.ora parameters listed in the note.
 
Known Performance-Related Database Bugs

For issues with the Oracle optimizer and database, most of the known performance-related bugs and their recommended patches for E-Business Suite environments are listed in:

• Recommended Performance Patches for Oracle E-Business Suite (Metalink Note 244040.1)

Incorrect Statistics to the Cost-Based Optimizer (CBO)

Now comes the most complex -- and paradoxically, the easiest to solve -- area:  ensuring that the cost-based optimizer works with accurate metadata.

Before going into the details, I'll remind you that the rule-based optimizer (RBO) is no longer supported.  Don't use it in your E-Business Suite environments.  If you have an optimizer-related issue that appears in the cost-based optimizer but not the rule-based optimizer, log a Service Request with Oracle Support.

Maximizing the Benefits of Cost-Based Optimization

The cost-based optimizer is not infallible, but it's a DBA's responsibility to ensure that correct metadata is available to the optimizer.  There are two things that system administrators should do:

1. Set all of the optimizer-related parameters, following Note 216205.1
2. Gather statistics for all database objects

Gathering Statistics for Database Objects

There are many different ways of gathering statistics:

• Use the Analyze command
• Use Dbms_stats
• Use Fnd_stats

For Oracle E-Business Suite environments, we recommend using fnd_stats.

A Short Digression:  What is fnd_stats?

Fnd_stats is a wrapper around dbms_stats that suits most of the E-Business Suite's requirements.

We recommend using fnd_stats over dbms_stats for Apps environments because of the former's support for restarts.  Starting with the 10g version of the database, dbms_stats has also this feature.

If the Gather Schema Statistics concurrent program is used, fnd_stats does the bookkeeping for the run.  Should the run fail for any reason, the next run of the program starts from where the previous run was stopped. This saves lot of time.

We also recommend  fnd_stats because of its support for histograms.  Histograms are useful when:

1. A table's column is used in an equality or equi-join predicate AND there are  skews in the column.
2. A table's column is used in a range or like predicate AND there are  skews in the column.

Oracle Applications' data distribution is dependent on the functionality of the specific product modules.  Based on our benchmarks, a number of columns are useful for histograms.  These columns are listed in the FND_HISTOGRAM_COLS table.

When Gather Schema Statistics is executed, it reads FND_HISTOGRAM_COLS and builds the histograms.  

Back to Gathering Database Statistics

When gathering the statistics for the entire applications database, we must use the Gather Schema Statistics concurrent program.  Only the Gather Schema Statistics and Gather Table Statistics should be used. Do not use the Gather Column Statistics program.

When Should You Gather Statistics?

There is again no hard and fast rule for the interval between gathering statistics.  A general rule-of-thumb is is to run the statistics collection after a 10% increase in the database size. 

Having said that, other factors may come into play.  From 11.5.10 onwards, fnd_stats was enhanced to gather only the statistics for those objects, which have undergone a predefined percentage of data increase, or for objects that have no statistics.  The latest versions of fnd_stats identifies STALE and EMPTY statistics and gather the statistics for those objects only.  This saves lot of time and allows sysadmins to set flexible statistics gathering intervals. 

Although there are a number of options that you can set, we recommend selecting  the GATHER AUTO option.

Incorrect System Global Area and Program Global Area Sizing

SGA_TARGET sets the maximum size of the System Global Area (SGA) for your E-Business Suite instance.  Parameters like db_cache_size and shared_pool_size affect the database buffer cache and shared pool for that instance.   PGA_AGGREGATE_TARGET determines the maximum value for the Program Global Area (PGA) for the instance.

Note 216205.1 lists the minimum values for these parameters. These values might need to be varied depending on the actual load on your environment.  In the same way.  If these are values are:

• Set too high:   memory on the server is not utilised to the maximum
• Set too low:  there will be performance issues

To find out whether these parameters are set properly on your system, you can run a Statspack or AWR report.

Interpreting Statspack or AWR Reports

Covering this area comprehensively is beyond the scope of this article, but here's some things to remember about interpreting your Statspack report:

Total Response time = Service time + Wait time

Service Time is generally the amount of "CPU used by this session".  From the 9.2 database version and upwards, this is reported in the Statspack.  You can use this to derive the Wait Time.

For example:

Using a Statspack report with a 9.2 or higher database, examine the figures reported for CPU time.  If it's 70% of the total time, then the wait time is 100-70=30%.

Total Response time = 70% CPU time + 30% wait time

Once we have identified the time taking component, look into the specific component.  For memory-related areas, look at the Advisory Statistics section.  This shows details about the sizing of SGA (cache and shared pool) and PGA (M-pass executes should be as small as possible for a optimal PGA). 

Based on this type of analysis, you can vary your SGA and PGA settings accordingly.

Getting Support for Performance Issues

As you can see, investigating performance issues can be tricky, especially if you find the topics briefly described here to be daunting.  Take heart:  you're not on your own.  We have teams that specialize in these areas, so if you run into any performance-related issues, log a Service Request via Metalink and we'll jump in to help.

In Summary

A quick recap of database setup-related issues and recommendations for tracking down performance-related issues:

• Database Parameters:  Refer to Note 216205.1 for mandatory settings, and then check your Statspack/AWR reports periodically to see whether your memory-related parameters are set correctly.
   
• Apply the Recommend Performance-Related Database Patches:    Refer to note 244040.1
   
• Keep Your Statistics Fresh: Use the Gather Schema/Table Statistics concurrent program regularly.  The GATHER AUTO option can be of great help here.

It's possible to dive much deeper into database or instance tuning topics.  If you'd like me to go into more details, feel free to post a comment here.

Related

• Latest JVM Tuning Recommendations for Apps 11i
• Master Class in Apps 11i Performance Tuning