IBM BPM, V8.0.1, All platforms > Tuning > Advanced tuning > Business Process Choreographer tuning > Tuning BPEL processes > Tuning long-running processes > Tuning the Business Process Choreographer database

Tuning the Oracle database for Business Process Choreographer

You can improve the performance of your BPEL processes by tuning your database.

The performance and scalability of an Oracle database is enhanced primarily by optimizing the layout of database files, allocating sufficient memory to the buffer cache to enable efficient caching, and tuning database parameters.

Verify that you have deleted or archived runtime objects that are no longer needed, by performing Remove unnecessary objects from the Business Process Choreographer database.

The suggestions here only apply to Oracle. Refer to your database management system documentation for information about monitoring the performance of the database, identifying and eliminating problems, and fine-tuning its performance.


Procedure

  1. Allocate enough space to the buffer caches.

    Use in-memory caching to produce low-latency response times for database accesses. This means that the buffer caches must be sufficiently large. Set the buffer cache size to at least 700 MB, then monitor cache usage and increase the cache size if necessary.

  2. You can reduce the frequency of log file switches by increasing the size of the log files.

    The transaction log of an Oracle instance resides in several files used in a round-robin fashion. The active log files are switched when one becomes full, allowing the last active log to be archived. Because switching log files is an expensive operation, size the log files so that these switches occur infrequently; 750 MB is a good starting value. Then, monitor the transaction rate and the average log size, and adjust this value as needed.

  3. Tune the following database parameters.

    UNDO_TABLESPACE

    Check the size limit of this table space. Verify that not more than 70 percent of it is used.

    OPEN_CURSORS

    The default value for this parameter is 50. However, this is often insufficient: the value of the OPEN_CURSORS parameter must be higher than the statement cache size of the Business Process Choreographer data source (BPEDB), which is set to 128 by default. The highest value that you can use for this parameter depends on your operating system. Values up to 1000 are supported on most operating systems.

    MAX_SHARED_SERVERS

    Specifies the maximum number of shared server processes that can run simultaneously. Use this parameter to reserve process slots for other processes, such as dedicated servers. If a value for the MAX_SHARED_SERVERS parameter is specified, then it should be greater than, or equal to, the value of the SHARED_SERVERS parameter, and less than the value of the PROCESSES parameter.

    For example, if there are 150 concurrent users, a good starting value for this parameter is MAX_SHARED_SERVERS=70

  4. Review the Automatic Database Diagnostic Monitor (ADDM) analysis. By default, ADDM runs every hour.

    1. If a diagnostic summary is displayed on the home page of the database, it shows the number of ADDM findings as a link. Click the link to display the ADDM page, which contains a database activity chart and a performance analysis. The findings are listed under the performance analysis.

    2. Select any finding to display more details. If you click Top SQL by DB Time, a list of recommendations is displayed. For each recommendation, you can review the affected SQL statement and click recommended actions such as running the SQL Tuning Advisor or implementing an increase in the cache size. Make sure that you review any recommendations carefully before applying them.
  5. By default, JDBC batching for Oracle is enabled, which can improve performance. If you want to disable JDBC batching for Oracle, set the Business Flow Manager custom property DatabaseBatchUpdate to the value false.

    If the property is not set, the default is that JDBC batching for Oracle databases is enabled.


Results

Your long-running processes are running as fast as possible under the current environment and loading conditions.

: Tuning the Business Process Choreographer database


Related tasks:

Remove unnecessary objects from the Business Process Choreographer database


Related information:

Setup, configuration, and maintenance of the Business Process Choreographer database
Oracle 11g Database Performance Tuning Guide