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
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.