About the Oracle Parameter File

The System Global Area (SGA) is defined by three settings in the init<SID>.ora file:

• DB_BLOCK_BUFFERS
• SHARED_POOL_SIZE
• LOG_BUFFERS

The SGA should be configured to use no more than 40% of the available RAM on a server that is dedicated to the ENOVIA Live Collaboration/Oracle instance.The first setting is the most important for ENOVIA Live Collaboration.

The DB_BUFFER area, defined by the product of DB_BLOCK_SIZE and DB_BLOCK_BUFFERS, is the key memory buffering cache for database transaction activity. If you specify too small a value, it will impact performance. The Oracle default of 200 is inadequate. A larger cache reduces the number of disk writes of modified data.

DB_BLOCK_SIZE should be set to match the system's OS block size value. Considerable performance improvements have been noted when, for instance, Solaris machines used 8k instead of 2k, and AIX used a DB_BLOCK_SIZE size of 4k to match the OS default of 4k.

Note: Oracle should never use swap, otherwise performance will degrade. Too large a DB_BLOCK_BUFFER value could induce memory paging or swapping.

SHARED_POOL_SIZE is the data dictionary library cache. A value of 9000000 is suggested, but larger databases may want to use 15m.

So, for example, if a server for a large database had 256k of RAM, you could configure it as follows:

db_block_buffers = 20000 (20000 x 4k = 80mb. Note that this is assuming a block size of 4k at database creation.)

shared_pool_size = 9000000

log_buffers = 16384000

If this server will also run the ENOVIA Live Collaboration Server, use a smaller SGA, reducing the size of DB_BLOCK_BUFFERS only.

All new rollback segments should be identified with the rollback_segments setting. For example:

rollback_segments = r01, r02, r03, r04 (and so on...)

Rollback segments must first be created with the "Storage Manager" or SQL*Plus applications.

You may also need to specify the maximum transactions per rollback segment. For Oracle Version 10.x, use the following parameter:

transactions_per_rollback_segment = 32 (at a maximum)

Adjustments should be made to the number of processes, open cursors, and dml locks based on the number of concurrent users. Use these formulas as a guide for configuring:

processes= should be 5 times the number of concurrent users.

open_cursors sets the number of open cursors per process. 200 is suggested, but may be scaled up as necessary.

dml_locks = should be 10 times the number of concurrent users.

The number of threads in the ENOVIA Live Collaboration Server should be included in the "concurrent user" count for all settings.

Note: Increasing the number of processes may require more semaphores to be created on some UNIX servers. Refer to the Oracle installation guides for system configuration requirements.

Note: If Adaplets will be used, the open_cursors setting should be 12 times the number of concurrent users.

When using an adaplet with a ENOVIA Live Collaboration Server, the Oracle instance needs to be able to support more sessions than the connection pool size. The following Oracle parameters should be set in init<SID>.ora as follows:

sessions=200
processes=250 

These values should be considered guidelines, but both should be set greater than the value of MX_CONNECTION_POOL_SIZE, which is set in the enovia.ini file for the ENOVIA Live Collaboration Server. The default value for MX_CONNECTION_POOL_SIZE is determined by the Java heap size selected during installation of the ENOVIA Live Collaboration Server (on UNIX, that is small = 10, medium = 20, large = 30).

Also note that when adaplets will be used, the open_cursors setting should be 12 times the number of concurrent users. Refer to the ENOVIA Studio Federation Toolkit Adaplet Programming Guide for details.

LOG_CHECKPOINT_INTERVAL is defined as:

So, for a 10m redo log, an OS block size of 2k, and 2 checkpoints per redo log, we have:

Given that the redo is really used for recovery, Oracle increments a Sequence Change Number (SCN) with each checkpoint write. So, if you need to recover a database, having multiple SCNs per redo log permits recovery to any SCN.

For dataload or upgrade conditions, consider setting the LOG_CHECKPOINT_INTERVAL to a large number (such as 4096000), essentially disabling the SCN write and tuning the database for performance.

The log buffers writes to the redo every 3 seconds or at 33 percent fill of the log buffer. It is essential that the sum of the sizes of the redo logs exceeds the log buffer size. This means that a 16mb log buffer demands redo logs larger than the Oracle defaults of 500k /1m.

A redo log switch rate of 20 to 30 minutes is recommended for a production system.

Although not documented in our sample init<SID>.ora file (which is shown for creation purposes) archive logging is highly recommended for production mode. The following settings may be added to the initialization file:

log_archive_start = true 
log_archive_dest = directory path for Archive logs 
log_archive_format = arch%S.arc(for example)

Refer to the Oracle Server Administrator's Guide for discussion of these and other settings. For example, to enhance archiving performance, consider setting:

log_archive_buffers = 2

However, for writes to disk or fast tape, try a larger value. Oracle documentation provides many more details.

If you plan to work with ENOVIA Live Collaboration in a case-insensitive environment, the Oracle database must be configured to use a function-based index by adding the following to init<SID>.ora:

QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
optimizer_mode = choose

If you have done some Oracle tuning, the optimizer may be in "Choose" mode. This is acceptable as long as you calculate statistics regularly, since the system will use cost-based optimizing if statistics are available. Refer to Oracle documentation for more information on optimizer modes. Refer to the MQL Guide for more information on the ENOVIA Live Collaboration case-sensitive setting and for recalculating statistics.