Oracle-Specific Database Guidelines for Oracle Business Analytics Warehouse

These guidelines will help you optimize Oracle database performance and configure Business Analytics Data Warehouse on Oracle databases.

Topics:

• General Guidelines for Oracle Databases

• Guidelines for Using Oracle Template Files

General Guidelines for Oracle Databases

These additional suggestions will help you to optimize performance for Oracle databases used for Oracle Business Analytics Warehouse.

• Oracle BI Applications on Oracle databases support only binary sorting. If you are running an Oracle client, do one of the following:

  • Set the NLS_SORT parameter to BINARY.

  • Choose a NLS_LANG setting that includes binary.

  These settings are required for adequate performance from the dedicated Web client.

• It is recommended that you gather workload system statistics.

• To increase data throughput between Oracle BI Server and the Oracle database, change SDU and TDU settings in listener.ora. The default is 2 KB and can be increased to 8 KB.

• On the server side, edit the listener.ora file. Under the particular SID_LIST entry, modify SID_DESC as follows:

  SID_LIST_LISTENER =
     SID_LIST =
        SID_DESC = (SDU=16384)(TDU=16384) 
           ORACLE_HOME = /.....)
           SID_NAME = SOLAP) 
     )
  )
  

• Set the number of log file groups to 4.

• On the client side, edit the tnsnames.ora file. Modify the TNS alias by adding SDU= and TDU= as follows:

  myhost_orcl.world=
     DESCRIPTION=(SDU=16384)(TDU=16384)
     ADDRESS = (PROTOCOL = TCP)(HOST=myhost)(PORT=1521))
  CONNECT_DATA=(SID=ORCL))
  

Guidelines for Using Oracle Template Files

To configure the Business Analytics Data Warehouse on Oracle databases more easily, refer to the parameter template file init11gR2_template.ora file or init11gR2_Exadata_template.ora, which are stored in \ORACLE_HOME\biapps\etl.

The parameter template file provides parameter guidelines based on the cost-based optimizer for Oracle 11gR2. Use these guidelines as a starting point. You will need to make changes based on your specific database sizes, data shape, server size (CPU and memory), and type of storage. The database administrator should make changes to the settings based on performance monitoring and tuning.

Copy the appropriate template file into your ORACLE_HOME/dbs directory. Then, review the recommendations in the template file, and make the changes based on your specific database configuration. The database administrator should make changes to the settings based on performance monitoring and tuning considerations.

Note:

The NLS_LENGTH_SEMANTICS parameter enables you to define byte- or character-length semantics. Oracle BI Applications supports BYTE and CHAR values for this parameter. If you are using MLS characters, then you can add this parameter to the parameter template file for your database version (that is, the initDB_version.ora file).