Bookshelf Home | Contents | Index | Search | PDF

Siebel Data Warehouse Installation and Administration Guide > Overview of Siebel Data Warehouse > Siebel Data Warehouse Database Requirements >

Oracle-Specific Database Requirements

---

The following requirements apply to Oracle RDBMS usage:

• Make the appropriate connections using Oracle Net8 Assistant (for Oracle 8 databases) or Oracle Net Service (for Oracle 9 databases).
• Create the user accounts that access the database. They must have SSE_ROLE and be set so that the .dll commands can be run (permissions to create tables).
• Set the tablespace for the Siebel Data Warehouse. Recommended values are shown in the following table.
• Create a login user with the tablespace as the default tablespace. The database objects will be created using this user login.
• Edit the Init.ora file. Use the recommended baseline parameters shown in Table 12.

Table 12.  Recommended Variable Settings for Oracle Databases

Parameter	Recommended Setting	Notes
Always_anti_join	HASH	Not available in Oracle 9i or later.
Cursor_space_for_time	TRUE	Use only if you do not have many concurrent connections.
Db_block_lru_latches	Set to number of CPUs
Db_block_size	32k
Db_file_direct_io_count	64	Not available in Oracle 9i or later.
Db_file_multiblock_read_count	STMAXIO / DB_BLOCK_SIZE
Db_file_multiblock_read_count	32
Default Pctincrease	0
Hash_area_size	20 MB
Hash_multiblock_io_count	0.5 * ( db_file_multiblock_Read_count)	Make sure that you are not using MTS.
Initial Extent	20 MB
Log_buffer	2 MB
Next Extent	20 MB
Optimizer_index_caching	Unset	Unset this to avoid nested loops index joins (as favored by the optimizer).
Optimizer_index_cost_adjust	Set to default	See NOTE following these parameters.
Optimizer_max_permutations	default of 80,000	May be applicable to other schemas in the database. This means longer parsing times for the access paths, but once these are parsed, they should remain in the shared_pool, provided they do not get aged out. See notes for Cursor_space_for_time.
Optimizer_mode	ALL_ROWS	Cost-based for Refresh ETL Rule- based for Full ETL.
Query_rewrite_integrity	USE_STALE
Rollback_segments	For a development instance, no more than 2	Each rollback_segment sized as follows: Initially 10 MB Next 10 MB Minextents 10 MB Maxextents unlimited Optimal 1000 MB
Shared_pool_size	34 MB
Sort_area_retained_size	10 MB
Sort_area_size	10 MB
Sort_multiblock_read_count	4	Not available in Oracle 9i or later.
Star_transformation_enabled	TRUE
Tablespace	At least 50% of Siebel transactional database size	Make sure the temporary tablespace has adequate space.
Number of log file groups	4
Size of each Log file	10MB

Notes on OPTIMIZER_MODE Settings for Oracle

Take note of these OPTIMIZER_MODE settings:

• For OPTIMIZER_MODE settings, see Full Load Optimizer Settings in Oracle for full load and Incremental Load Optimizer Settings in Oracle for incremental refresh.
• For a full load, the optimizer should be set to Rule based in the in the init.ora parameters, thus: OPTIMIZER_MODE RULE.
• For an incremental load, the optimizer should be switched to Cost based in the in the init.ora parameters, after analyzing all the data warehouse (W_*) tables, thus: OPTIMIZER_MODE ALL_ROWS.

Additional Suggestions for Optimizing Oracle Performance

• Siebel eBusiness Applications under Oracle 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.

• Make sure that cost-based optimization is enabled in the Oracle development, test, and production databases and that statistics are kept up to date. Otherwise, the rule-based optimizer may be used.
• Create foreign keys in the Oracle database, but configure Oracle to not enforce the foreign key relationship. The existence of foreign keys will allow Oracle to better optimize certain queries. By turning off enforcement, the database load should not be negatively affected.
  • In addition, you may also configure the Oracle star-join transformation. This requires non-enforced foreign keys in Oracle and the creation of necessary bitmap indexes. This task is optional. It may not be necessary, as ongoing tuning may reach the desired performance goals.
• Analyze application for occurrences of highly skewed data that is indexed. Create histogram statistics for these indexes to enable the optimizer to better
• To increase data throughput between Siebel Analytics Server and Oracle, 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=8192)(TDU=8192)

     ORACLE_HOME = /.....)

     SID_NAME = SOLAP)

  )

  )

• 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=8192)(TDU=8192)

  ADDRESS = (PROTOCOL = TCP)(HOST=myhost)(PORT=1521))

  CONNECT_DATA=(SID=ORCL))

  )

Bookshelf Home | Contents | Index | Search | PDF