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.
About this task
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
- 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.
- 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 that are 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.
- Tune the following database parameters.
- UNDO_TABLESPACE
- Check the size limit of this table space. Make sure 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
- Review the Automatic Database Diagnostic Monitor (ADDM)
analysis. By default, ADDM runs every hour.
- 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.
- 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.
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.
