Oracle init parameter descriptions
The Oracle init parameters have mandatory and recommended settings for the performance of Sterling B2B Integrator with an Oracle database.
| Parameter | Description |
|---|---|
| Number of processes | Maximum number of processes that the Oracle server can create. Each inbound
connection from a client requires an available process on the Oracle server and internal processes
that run in the Oracle server itself. This setting needs to be set high enough to accommodate the expected peak connections from Sterling B2B Integrator as well as additional processes for internal server processes plus the possible usage of buffer connections from the Sterling B2B Integrator pools. Note that the expected peak connections from Sterling B2B Integrator are per node, and this number should be multiplied by the number of nodes in a cluster. Important: Exceeding the allocated processes can destabilize the entire Oracle server and
cause unpredictable issues.
Normally, the Sterling B2B Integrator logs a report if this has occurred, by logging an “ORA-00020 maximum number of processes exceeded” error. |
| open_cursors | Number of cursors that the Oracle server can create. Each process that is
servicing a connection will normally spawn two or more cursors, plus additional cursors that are
needed for internal server processes. As with the processes, this needs to be set high enough to accommodate the expected peak connections, multiplied by two, plus an allocation for possible buffer connections. This should also be multiplied by the number of nodes in the cluster. A simple rule of thumb is to set this to four times the number of processes, four times the number of nodes of Sterling B2B Integrator, plus an additional 10%. If the number of cursors is exceeded, Sterling B2B Integrator logs “ORA-01000: maximum open cursors exceeded.” |
| Character Set | Controls the storage of character data in the Oracle database. The UTF8 set
most closely matches the default character set for Java™ and
will prevent any conversion of character data. AL32UTF8 is the preferred setting for Oracle database. |
| cursor_sharing | Controls whether SQL sent from a client is reparsed each time, or if the
server can reuse (“share”) the complied plan. Sterling B2B Integrator requires the setting to be “exact” for both performance and data integrity. |
| sga_target | Determines the target value that the Oracle server uses for the automatic
allocation of SGA pool resources. Note: The suggested best practices and values for SGA (System
Global Access) Target differ depending upon which version of Oracle you use:
Oracle 11g and higher If you use Automatic Memory Management (AMM), the SGA Target and PGA Aggregate Target parameters set the minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, ensure that these parameters are set to 0. Suggested value is 0. |
| sga_max_size | Maximum size of the memory allocated to the System Global Area (SGA). This
controls the maximum memory that can be allocated to the system area of the Oracle instance. The
size of the SGA should never exceed the size of the actual memory (RAM) installed on the server. A rule of thumb is that the SGA maximum size should be allotted as much memory as possible, but should not exceed 80% of the total memory of the server. |
| sga_max_target | Target value that Oracle uses for automatic allocation of SGA pool resources.
Recommended setting is equal to the sga_max_size. The exception is for extremely large databases, where this may need to be adjusted manually. |
| pga_aggregate_target | Specifies the target aggregate Program Global Area memory available to all
server processes attached to the Oracle instance. These are normally internal Oracle processes, and
are not used by clients connecting to the instance. Oracle 11g and higher If you use Automatic Memory Management (AMM), the SGA Target and PGA Aggregate Target parameters set the minimum size settings for their respective memory areas. To allow Oracle to take full control of the memory management, ensure that these parameters are set to 0. Suggested value is 0. |
| memory_target |
Memory Target is the system-wide usable memory available to the Oracle database. The database self-tunes memory by reducing or enlarging the SGA and PGA as needed. Calculate the optimal values for memory_target and memory_max_target using the following formula:
To calculate maximum PGA allocated, use the query:
Memory Max Target is the maximum value to which the database administrator can set memory_target. For the memory_max_target initialization parameter, decide on the maximum amount of memory you want to allocate to the database for the foreseeable future. That is, determine the maximum value for the sum of the SGA and instance PGA sizes. This number must be larger than or equal to the memory_target value. |
| memory_max_target | |
| timed_statistics | Controls whether database statistics for particular times are logged by
Oracle. This information may be useful to monitor system or application performance. Setting timed_statistics to TRUE may incur a slight overall load increase on the Oracle instance. However, the information it creates is valuable for diagnosing issues with performance of the instance. |
| optimizer_mode | Controls the mode that the optimizer uses to select the proper execution path
for fulfilling query results, among other functions. Setting this to an incorrect mode can dramatically affect the overall performance of the server. Recommended setting for Sterling B2B Integrator: all_rows |