Performance Tuning the Apps Database Layer

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:



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:



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.  


gather schema/table statistics:



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. 


gather auto:



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



Comments:

Gareth,Have you had a chance to check out this article yet?Enabling Native PL/SQL Compilation for Release 11i - http://blogs.oracle.com/schan/2007/02/enabling_native_plsql_compilat.htmlSome readers have commented on their own experiences with native compilation in Apps environments there, too.Regards,Steven 

Posted by Steven Chan on May 17, 2007 at 07:53 AM PDT #

Hi Steven,
I'd be interested to know if any customers have implemented native compilation (full or a subset of PL/SQL packages) for Oracle Applications, I would imagine that plenty of customers would find significant performance gains if PL/SQL packages behind high volume processes were natively compiled - possibly areas such as large open interfaces, GL Posting, payables batch validation - wherever customers have high batch volumes.
Regards,
Gareth

Posted by Gareth Roberts on May 17, 2007 at 09:01 AM PDT #

Hi Steve,

Thanks for the excellent blogs that you keep on posting. Have to admit they have been nothing short of invaluable for us.

Recently we upgraded to 10.1.0.5 from 9.2.0.6. Subsequently we have had enormous perf issues. Particularly with searching in various applications and reports. What we found interesting was that if we change the 'optimizer_features_enable' parameter from '10.1.0.5' to '9.2.0', we found that suddenly performance increased considerably. And our major perf bug bears disappeared (searching for invoices, Help > About, Numerous Reports). Basically using 9i optimization on a 10.1 database works it work better!

We found this incredibly disheartening since we put in considerable effort into going 10g.

Support have provided little feedback on this, have you got any thoughts?

We run Gather Schema Stats 3 times a week.And to get around our search issues for Invoices (thro the 'inquiry > View invoices functionality') we have to run Gather Table Stats daily for 3 tables.

Posted by Nasser Ali on May 17, 2007 at 08:55 PM PDT #

I think that is very important to tell about the System Statistics using CBO.

Most of 98% of databases that I've administrated with CBO, the System Statistics was ignored.

Regards,
Carlos Duarte

Posted by Carlos Duarte on May 18, 2007 at 01:19 AM PDT #

Can you kindly elaborate on the Gather auto option or point me to a metalink note.

Posted by sam on May 18, 2007 at 03:53 AM PDT #

exec dbms_stats.CREATE_STAT_TABLE('SYS','STAT_TABLE','SYSAUX');
exec dbms_stats.GATHER_SYSTEM_STATS(GATHERING_MODE => 'START',stattab => 'STAT_TABLE',statid => 'DAILY');
exec dbms_stats.GATHER_SYSTEM_STATS(GATHERING_MODE => 'STOP',stattab => 'STAT_TABLE',statid => 'DAILY');
exec dbms_stats.IMPORT_SYSTEM_STATS('STAT_TABLE','DAILY');

Regards,
Carlos Duarte

Posted by Carlos Duarte on May 19, 2007 at 12:43 AM PDT #

Carlos and Sam
Gather auto
Oracle monitor the dml activity for objects and record it in SGA. The monitored information is periodically flushed to disk and is exposed in *_tab_modifications view. This information is used for determining objects with stale statistics. An object is considered stale when more than 10% of the rows are changed (total # of inserts, deletes, updates) in the table. Oracle collect stats for only objects with no stats or with stale stats in AUTO mode (options => 'GATHER AUTO' or in automatic statistics collection job)
The modification information is flushed to disk periodically every 15 minutes. You may not see the modifications done in last 15 minutes in *tab_modifications view. But before collecting stats we force flushing the information to disk. User also can do it forcefully by executing dbms_stats.flush_database_monitoring_info. 
 
From the Oracle 10g database version, we can generate statistics on fixed tables, dictionary and gather the system statistics.
Refer the Oracle documentation for further details
First we start the collectionRun the workloadStop the collection
 
EXECUTE dbms_stats.gather_system_stats('Start');
run the workload
EXECUTE dbms_stats.gather_system_stats('Stop');
But this is not tested with Oracle ebusiness suite.

Posted by Avanish Srivatsav on May 21, 2007 at 02:56 AM PDT #

Nasser, keeping the parameter optimizer_features_enable to older versions is not recommended, please go through the note 244040.1 and apply all the database related patches where ever applicable and log a tar with support. This can be worked on

Posted by Avanish Srivatsav on May 21, 2007 at 04:25 PM PDT #

Hi Avanish,

I want to add one point to GATHER AUTO option.

Before changing Gather Option to Gather Auto, it will good if we keep schema tables in monitoring by running
exec fnd_stats.enable_schema_monitoring(<schema name>) ;

Posted by Phani K on May 24, 2007 at 05:03 AM PDT #

We could not use PL/SQL native compilation because during our upgrade to 11.5.10.2 and 10.2.0.2, the recompilation of invalid objects took forever! after 4 hours, less than 20% of the invalid objects were compiled. This happened on boxes where the 11.5.10.2 patch (3480000) only took us 5 hours.

Posted by Tianhua Wu on May 24, 2007 at 09:11 AM PDT #

Tianhua,Thanks for the feedback on your experiences with PL/SQL native compilation.  We've heard similar comments from others, so it's clear that everyone who's considering this approach should carefully evaluate the costs and additional overhead vs. the benefits.   This can be useful for some, but it's clearly not for everyone.Regards,Steven

Posted by Steven Chan on May 25, 2007 at 07:27 AM PDT #

phani, gather auto will automatically does that
 
 
-- Check if there are any tables in the schema which does not have
-- monitoring enabled. If yes, gather stats for them using 10% and
-- enable monitoring for such tables so that we have data for them
-- in dba_tab_modifications for next time.
 
 

Posted by Avanish Srivatsav on May 25, 2007 at 12:13 PM PDT #

Hi Steven, how are you?

I would like to know your opnion about System Statistics in E-Business Suite environment.

I didn't found any document in metalink about this case.

Just this docID: How To Gather and Display The Workload System Statistics? [ID 1148577.1]

But here after upgrade in our SO, like CPU clock, memory and add new node for RAC the performance is very worst.

Three yars ago I had the same problem but in 9iRDBMS and 11.5.9

Here we have 10g and 12.0.6 in RAC.

Thank you

Regards

Tiago Doné

Posted by Tiago Doné on December 02, 2010 at 03:59 AM PST #

Hi, Tiago,

It's a good idea to gather statistics on a regular basis to keep your environment well-tuned. I'd also recommend this article:

Maintaining Your EBS Environment for Maximum Performance
http://blogs.oracle.com/stevenChan/2010/12/ebs_performance_patches.html

If you're seeing odd performance degradation in your environment, your best bet would be to log a formal Service Request via My Oracle Support (formerly Metalink) to get one of our performance specialists engaged.

Please feel free to forward your Service Request number to me if it gets stuck in the support process for some reason.

Regards,
Steven

Posted by Steven Chan on December 03, 2010 at 05:42 AM PST #

Thank you Steven!

Posted by Tiago Doné on December 20, 2010 at 03:33 AM PST #

Most places I read suggest running GSS with Gather Auto Option. What is the recommendation for the Estimate Percent in EBS? Would you suggest using the Auto Sample Size (0) or setting a percentage? Also is this applicable to 10g and 11g databases?

Posted by guest on May 03, 2012 at 05:55 AM PDT #

Guest,

Thanks for the inquiry. Oracle E-Business Suite guidelines for performance tuning and optimization are as follows :

- Use the GATHER_AUTO option to gather statistics incrementally
- For Oracle Database 11g only, AUTO_SAMPLE_SIZE can be set to zero
- Analyze all schemas at 10 percent
- Analyze specific objects at "x" percent.
NOTE: Due to data skew, some tables benefit from higher sampling. This is environment specific.

NOTE: The above guidelines are true for Database 10g and 11g unless otherwise noted.

Regards,
Elke

Posted by guest on May 11, 2012 at 12:50 PM PDT #

Hi Steve,

Is gathering system statistics on EBS supported by oracle.

we have 7 Node APPS
3 Node database.
NET APP File system.

Posted by guest on August 14, 2013 at 02:20 PM PDT #

Guest,

Thanks for the inquiry.

Whether or not you gather SYS schema statisics for E-Business Suite is dependent upon your database version. Please review recommendations posted in the following My Oracle Support Knowledge Document:
EBPERF FAQ - Collecting Statistics in Oracle EBS 11i and R12 , Note 368252.1

Thanks.
Elke

Posted by Elke Phelps (Oracle Development) on August 14, 2013 at 02:54 PM 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
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today