JCA Adapter Basic Tuning Considerations

JCA Adapter Basic Tuning Considerations

Adapter performance is directly related to the number of round-trips to the database, and the network cost of each trip. If performance becomes an issue, and making modifications is appropriate for your deployment, consider tuning the following parameters:

• Use Indexes
  Indexes can improve performance of selects, updates and deletes. Index all queried fields, such as the primary key and the MarkReadField of the LogicalDeletePollingStrategy, when polling. For MarkReadField specify a non-null MarkUnreadValue. Caution: An index on a column containing many nulls may revert to full table scans.
• Disable OptimizeMerge
  The OptimizeMerge parameter allows the detection of XML elements for which no value was specified. The related columns are excluded from inserts and updates. Disabling this parameter generally improves performance, but there is one case where it could have a negative effect. If multiple rows are being passed in as a single XML, and each row has different columns set (user entered with many optional fields), there is no benefit from batch writing, as each insert or update is different.
• Increase MaxRaiseSize
  The MaxRaiseSize parameter indicates the maximum number of XML records that can be raised at a time to the BPEL engine. For example, if you setMaxRaiseSize = 10, then 10 database records are raised simultaneously. On an inbound read, for example, you can set MaxRaiseSize = 0 (unbounded) which means that if you read 1000 rows, you can create one XML with 1000 elements. These elements are passed through a single Oracle BPEL Process Manager instance. A merge on the outbound side can then take all 1000 in one group and write them all at once with batch writing. Use the MaxRaiseSizeparameter for publishing large payloads.
• Increase MaxTransactionSize
  This property controls the number of records processed per transaction by each thread. If set to a large value such as 1000, turning on theUseBatchDestroy option could have a negative impact on performance. Setting a large MaxTransactionSize and a small MaxRaiseSize could also have negative impact on performance. Consider maintaining up to a 10:1 ratio in a synchronous scenario. Ideally, you should consider increasing MaxRaiseSizeuntil it is a 1:1 ratio.
• Enable UseBatchDestroy
  This property controls how the processed records are updated (ex: Deleted for DeletePollingStrategy, MarkedProcessed forLogicalDeleteStrategy). If set, only one update/delete is executed for all the rows that are part of that transaction. The number of rows in a transaction is controlled by the MaxTransactionSize option. Note that this may not always offer an improvement because, by default, batch writing is used, which also ends up in a single round trip to the database.
• Enable Batch Reading
  Batch reading of one-to-many and one-to-one relationships is on by default. You can also use joined reading for one-to-one relationships instead, which may offer a slight improvement.
• Disable Delete Polling Strategy
  Avoid the delete polling strategy because it must individually delete each row. The sequencing polling strategy can destroy 1000 rows with a single update to a helper table. Note that a LogicalDelete is also better than Delete, as updates are typically faster than deletes. To maintain performance, however, ensure that you have indexed the table. If you have not indexed, you can keep the total number of rows small by using deletes. In some instances deletes may be faster as the cost of a full table scan is negligible.
• Use Distributed Polling
  Distributed polling enables you to configure polling for scalability.
• Use Synchronous Processes
  On BPEL you can configure Database Adapter processes to be synchronous. You can also create sequential routing rules in Mediator. This can improve throughput in database-to-database scenarios, as there is less instance processing impact.
• Use Insert
  The insert operation is the most performant because it uses no existence check and has no extra performance impact associated with it. There are no reads, only writes. If you know that you are inserting most of the time, use insert, and catch a unique key constraint SQL exception inside your BPEL process, which can then perform a merge or update instead. To monitor performance, you can enable debug logging and then watch the SQL for various inputs.
• Disable Merge
  Merge executes one extra SELECT per related table. The SELECT is used to determine whether each row should be inserted or updated. If the row is updated, the update performed is minimal. If no rows have changed, nothing is updated.
• Use Connection Pooling
  The adapter should also point to a tuned data source connection pool. Tuning the connection pool is important because creating and tearing down database connections can impact performance.
• Use Attribute Filtering
  On the Attribute Filtering page of the Adapter Configuration Wizard you can choose which fields to map to the XML and vice versa. You can improve performance by deselecting columns that are not needed for your particular business case, especially large columns like LOBs.
• Use Native Sequencing If you are using the XSL functions to assign primary keys to records, consider using the built-in native sequencing support in the adapter. Sequencing support obtains and caches 50 keys at a time by default. Caching improves performance by reducing the number of round trips. The chunk size can be controlled incrementally by modifying the sequencePreallocationSize connector property.
• Do not use primary or foreign keys on the database
  Using primary and foreign keys can impact performance. Avoid using them when possible.
• JDBC Driver Class
  The default JDBC driver class used to create the physical database connections in the connection pool is oracle.jdbc.xa.client.OracleXADataSource. Changing the driver to oracle.jdbc.OracleDriver may provide some performance improvement.

DB Adapter Properties:

DB Adapter Properties: