Recommended Oracle parameters

Processes

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™ Order Management System Software. 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 Order Management System Software with the following formula:

where:

• A = Maximum number of agents transaction threads that run concurrently (see Agent thread levels).
• B = Maximum application server connection pool size times the number of application server instances. See Maximum Capacity.
• C = Any additional connections that are opened by customized code or user exits that do not go through the application server connection pool. This connection requirement is specific to your implementation.
• D = Number of asynchronous adapters (Service Definition Framework) times the number of connections per adapter.

The Sterling Order Management System Software 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:

• Six application server instances where each application server instance can run up to a maximum of twenty-five (25) transactions concurrently.
• Twelve agent threads
• Four asynchronous adapters where each could have up to four threads
• Maximum ten connections for operational tools such as Oracle OEM or Quest SpotLight

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:

• In the worst case, 25 x 6 or 150 database connections from the application servers during the peak period if there is a possibility that all application server threads become active. This of course would not be a desirable state - if there ever is such a possibility, you should configure more application server instances.
• 12 database connections for the agents/monitors
• 4 x 4 or 16 database connections from the asynchronous adapters
• 10 database connections from your operational monitors

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.

Compatible

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.

Sga_max_size, sga_target, pga_aggregate_target

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.

Cursor_sharing

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.

Optimizer_mode

Starting in Oracle10g, the optimizer mode of CHOOSE has been deprecated. You should set the optimizer_mode to the default of ALL_ROWS.

Open_cursors

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 Order Management System Software).

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.

Max_async_ports, disk_asynch_io

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.

Memory_target

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.