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.

System statistics for EBS

Until very recently, the note with DocId 368252.1 ‘FAQ – Collecting Statistics in Oracle EBS 11i and R12’ in MOS (My Oracle Support) was recommending to use the following command to ‘gather system statistics’ for EBS instances running 11i/R12.X:

execute DBMS_STATS.GATHER_SYSTEM_STATS (gathering_mode => ‘NOWORKLOAD’);

And that is correct, if you are not using Exadata. On Exadata, to ‘gather system statistics’  you should use:

execute DBMS_STATS.GATHER_SYSTEM_STATS (‘EXADATA’);

The main reason for that is that the last command will set MBRC =128 (actually, it sets this value based on the parameter db_file_multiblock_read_count, which defaults to 128). The idea is that a higher value make full table scans more attractive to the optimizer, which can be a lot faster on Exadata due to Smart Scan offloading.

You can see how it currently set running:

SQL> select pname, pval1 from sys.aux_stats$;

PNAME                                      PVAL1
——————— ———-
STATUS
DSTART
DSTOP
FLAGS                                          1
CPUSPEEDNW                          2768
IOSEEKTIM                                10
IOTFRSPEED                             4096
SREADTIM
MREADTIM
CPUSPEED
MBRC                                          128
MAXTHR
SLAVETHR

Kerry Osborne has a good discussion about this here.

Thanks to my good friend Olcay Sarioglu, the note MOS 368252.1 was corrected.

First post.. “apalyzer”!

As my first post I would like to share a new tool that I have been developing in my “free time” after joining the Accenture Enkitec Group.

So far, I have been calling it “apalyzer”, or “apache access_log analyzer”. As the name implies, it is a tool to analyze the apache access_log file, and give a quick overview of number of requests, requests that took long time to process, bytes returned, number of accesses per IP/Address, etc. Having a strong background in the Oracle E-Business Suite (EBS) Applications, I naturally added a few things for the EBS shops, like number of forms request x framework requests and login/logout information.

It is a shell script that requires no installation. Just unzip the file, unzip it where you have the logs and run:

ea3.sh <access_log file name>  

It will generate a zip file that you can bring to your computer, unzip  and open with a browser the *START_HERE* file.

If you want to give it try, you can download it here.

If you want to see a sample output first, download it here.

Best of all, it is free!