The following table summarizes the recommended choices:
Parameters | Oracle |
---|---|
db_block_size | 8KB |
processes | Must be greater than the number of connections needed by the (a) application servers, (b) the agents/monitors and (c) operational management tools. |
compatible | 10.2.0.1 (or the appropriate Oracle Rel 2 level) 11.2.0.1 (or the appropriate Oracle Rel 1 level) 12c |
sga_max_size, sga_target, pga_aggregate_target |
1GB to 4GB depending on the amount of physical memory on your
database node. Note: If the memory_target parameter is set, do not
set the sga_max_size, sga_target, and pga_aggregate_target parameters.
|
cursor_sharing | FORCE |
timed_statistics | True |
optimizer_mode | ALL_ROWS |
open_cursors | Default (higher if prepared statement caching used) |
memory_target | 4GB minimum. It can be increased depending on the amount of physical memory on your database node. |
HP-UX Only | |
disk_asynch_io | True |
This parameter sets the limit on the number of database connections. You have to pick a reasonably high enough number so that the combined connection requirements from the application servers, agents, and so forth do not exceed the connection limit during peak processing periods. If you do, you must restart the Oracle instance to increase this limit.
Fortunately, with the use of connection pooling in application servers, the number of database connections is less than the number of users logged in to Sterling Selling and Fulfillment Foundation. Depending on your anticipated peak workload traffic, this parameter could range from a small number like 25 to a large number in the thousands.
You must regularly monitor the number of concurrent connections in production (and especially during peak periods) to ensure that it does not reach the maximum. When the maximum session is reached, Oracle refuses to establish new connection requests.
See WebLogic connection pooling discussions in WebLogic connection pool.
See WebSphere® connection pooling discussions in WebSphere connection pool.
Guidelines for Estimating Number of Connections.
You can roughly estimate the number of concurrent users required by Sterling Selling and Fulfillment Foundation with the following formula:
where:
The Sterling Selling and Fulfillment Foundation agents and monitors are long- running Java™ applications that open and use one Oracle connection per thread.
Example: Lets assume that you plan to configure a system with the following characteristics:
Lets further assume that each transaction in the application server only requires one database connection. Specifically, user exits do not open their own database connection. As a result, for the example above, you need:
As a result, you should plan for at least 150 + 12 + 16 + 10 or 188 database connections.
As always, we strongly recommend that you benchmark your system to validate these assumptions and estimates prior to a production implementation. During the test, you should monitor the connection pool usage levels in each of the WebLogic Server instances, the number of agents that you need to run in order to meet your processing and service levels and the actual Oracle connections established.
You should set the compatible parameter to the four level release number that your Oracle software is running at in order to take advantage of the latest optimizer features. An example of the release number is 10.2.0.3 or 11.2.0.1.
In Oracle, setting sga_target allows Automatic Storage Memory Management to manage the memory inside the System Global Area (SGA). You can dynamically change the sga_target up to the value specified by sga_max_size.
As a result, you could either set sga_target to be equal to or less than the value of sga_max_size.
With cursor_sharing enabled, Oracle converts dynamic (non-reusable) SQL into reusable SQL by changing literal values into bind variables. Enabling cursor sharing significantly reduces shared pool and library cache contention.
Setting cursor_sharing to FORCE will also enable adaptive cursor sharing in Oracle, which allows the optimizer to peek at bind variables and choose optimal execution plans for queries that are bind sensitive.
For optimal performance, you must set cursor_sharing=FORCE.
Starting in Oracle10g, the optimizer mode of CHOOSE has been deprecated. You should set the optimizer_mode to the default of ALL_ROWS.
This parameter limits the number of cursors an Oracle session can keep open at any time. Generally, the default is sufficient unless you set a high prepared statement cache size (see Prepared Statement Cache Size in WebLogic connection pool: define data source in Sterling Selling and Fulfillment Foundation).
To find out the number of cursors opened by sessions, issue the following query:
select a.value, s.username, s.sid, s.serial# from v$sesstat a, v$statname b,
v$session s where a.statistic# = b.statistic# and s.sid=a.sid and b.name =
'opened cursors current';
For additional details regarding monitoring open database cursors, please refer to metalink note ID 753605.1.
Asynchronous I/O is very important to performance especially on high transaction volume processing environments. In summary, processes that issue synchronous read() or write() I/O calls must wait for the I/O to complete before it can continue. In contrast, processes can issue multiple asynchronous (non-blocking) aio_read() or aio_write() I/O calls in parallel without waiting.
HP-UX does not enable asynchronous I/O by default. HP-UX also only supports asynchronous I/O on files that reside on raw devices and not on filesystems. If you don't enable asynchronous I/O, you will have to run multiple DBWRs (up to 20) to get a limited amount of I/O parallelism.
Please see:
After enabling asynch I/O in HP-UX, you need to set the Oracle parameter, disk_asynch_io, to true in spfile.
In Oracle 11g, the memory_target and memory_max_target parameters specify the amount of memory that the Automatic Memory Management can dynamically allocate to the PGA and SGA. The AMM can reduce or increase the SGA and PGA, up to the memory_target, as needed. You can only change the memory_target value to the value specified in memory_max_target. As a result, you can set the memory_target to be equal or less than the value of memory_max_target.
If the sga_target and pga_aggregate_target is also set, the AMM uses these values as the minimum size for their respective areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.