Database parameters for EBS

There are 2 main things that should be done in the database to achieve good performance in an EBS instance:

  • Make sure that the initialization parameters are set properly
  • Make sure that statistics are current

Today I am going to write about the first one. Setting the database parameters is the first thing that should be done in an EBS instance.

For EBS, the databases initialization parameters should be set according to the following notes, depending on the version:

Database Initialization Parameters for Oracle E-Business Suite Release 12 (Doc ID 396009.1)

Database Initialization Parameters for Oracle Applications Release 11i (Doc ID 216205.1)

The first note includes the settings for 12.1 and 12.2 (technically, 12.0 as well, but likely that the number of installations running that version is very low).

The best tool to see discrepancies is the script bde_chk_cbo.sql provided in the note

bde_chk_cbo.sql – EBS initialization parameters – Healthcheck (Doc ID 174605.1)

The output of the bde_chk_cbo.sql is an HTML file that shows side by side what is current set versus the recommended values. This output is divided into sections:

  • Common parameters, which lists the parameters that don’t depend on the application version
  • Release-specific parameters, which will depend on the application version
  • Removal list, which specifies the parameters that should not be set
  • Additional parameters, listing parameters that don’t fit in the previous categories

For example, the Figure below shows the Release-specific parameters for EBS 12.1

bde_chk_cbo_report

Notice that besides the parameter name, the current value, and the recommended value, there also 3 additional columns at the end:

  • CBO indicates if the parameter affects the Cost-Base Optimizer
  • MP indicates if it is a mandatory parameter
  • SZ indicates if the parameters are ‘sizeable’

It is also important to realize that the ‘sizeable’ parameters have a ‘required value’ listed, like the SGA_TARGET in the range of 1-14G, but this really depends on the environment. Keep in mind that the recommended sizing was done a while ago and may not reflect the current reality.

Provided that your system is sized correctly and has enough resources, having the database initialization set properly is the first step to get a system with good performance and stability.

Advertisements

One thought on “Database parameters for EBS

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s