BPEL Database Adapter Batching Options

This blog investigates BPEL process manager's database records batching capabilities and under-laying resource utilization for both inbound and outbound type of database requests. BPEL process manager uses JCA adapter for database to integrate BPEL processes with access to database tables. Database adapter uses Oracle Toplink for mapping betweeen XML data(objects) into relational schema.

Event notification and database polling for inbound interaction

Database polling is not one time event, however it is activation. A polling thread is started which polls database table for new rows or events. “NumberOfThreads” parameter allows to configure number of concurrent inbound threads blocked and waiting for incoming messages.

Database polling threads

"JCA-work-instance:Database Adapter-0" id=118 idx=0x1a8 tid=2568 prio=5 alive, in native, waiting

-- Waiting for notification on: oracle/tip/adapter/db/InboundWork@0x052CE210[fat lock]

at jrockit/vm/Threads.waitForNotifySignal(JLjava/lang/Object;)Z(Native Method)

at java/lang/Object.wait(J)V(Native Method)

at oracle/tip/adapter/db/InboundWork.run(InboundWork.java:433)

^-- Lock released while waiting: oracle/tip/adapter/db/InboundWork@0x052CE210[fat lock]

at oracle/tip/adapter/db/inbound/InboundWorkWrapper.run(InboundWorkWrapper.java:43)

at oracle/tip/adapter/fw/jca/work/WorkerJob.go(WorkerJob.java:51)

at oracle/tip/adapter/fw/common/ThreadPool.run(ThreadPool.java:272)

at java/lang/Thread.run(Thread.java:595)

at jrockit/vm/RNI.c2java(IIIII)V(Native Method)

-- end of trace

"JCA_WorkerThreadPool-1" id=120 idx=0x1ac tid=2088 prio=5 alive, in native, waiting

-- Waiting for notification on: oracle/tip/adapter/fw/common/FIFOQueue@0x06201F20[fat lock]

at jrockit/vm/Threads.waitForNotifySignal(JLjava/lang/Object;)Z(Native Method)

at java/lang/Object.wait(J)V(Native Method)

at java/lang/Object.wait(Object.java:474)

at oracle/tip/adapter/fw/common/Queue.get(Queue.java:108)

^-- Lock released while waiting: oracle/tip/adapter/fw/common/FIFOQueue@0x06201F20[fat lock]

at oracle/tip/adapter/fw/common/ThreadPool.run(ThreadPool.java:241)

at java/lang/Thread.run(Thread.java:595)

at jrockit/vm/RNI.c2java(IIIII)V(Native Method)

-- end of trace

………

------------------------------------------------------------------------------------------------------------------------------

Following parameters are available for batch processing for inbound interactions. Description of these parameter directly taken from Adapater user guide

Database rows per XML document - MaxRaiseSize

On read (inbound) you can set maxRaiseSize = 0 (unbounded), meaning that if you read 1000 rows, you will create one XML with 1000 elements, which is 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

Database rows per Transactions - MaxTransactionSize

Assume that there are 10,000 rows at the start of a polling interval and that maxTransactionSize is 100. In standalone mode, a cursor is used to iteratively read and process 100 rows at a time until all 10,000 have been processed, dividing the work into 10,000 / 100 = 100 sequential transactional units. In a distributed environment, a cursor is also used to read and process the first 100 rows. However, the adapter instance will release the cursor, leaving 9,900 unprocessed rows (or 99 transactional units) for the next polling interval or another adapter instance. For load balancing purposes, it is dangerous to set the maxTransactionSize too low in a distributed environment (where it becomes a speed limit). It is best to set the maxTransactionSize close to the per CPU throughput of the entire business process. This way, load balancing occurs only when you need it

Database adapter polling options.

UseBatchDestroy

This property controls how the processed records are updated (ex: Deleted for DeletePollingStrategy, MarkedProcessed for LogicalDeleteStrategy). 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.

Request-Response (Outbound Invocations)

BPEL process manager have multiple options for outbound database connectivity.

1) Connection property from WSDL - This is for design time only and no connection pool is created. It is not recommended for production deployment.

2) JCA Connection pool

3) Application server data source – Preferred method for production deployment.

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. Reading a collection of rows from the database is significantly faster than reading each row individually. A common performance challenge is to read a collection of objects that have a one-to-one reference to another object. This normally requires one read operation to read in the source rows, and one call for each target row in the one-to-one relationship. To reduce the number of reads required we can use join and batch reading. Batch reading propagates query selection criteria through an object's relationship attribute mappings. Batch reading can be nested down through complex object graphs. This significantly reduces the number of required SQL select statements and improves database access efficiency.

Batch-writing

Batch writing can improve database performance by sending groups of INSERT, UPDATE, and DELETE statements to the database in a single transaction, rather than individually. TopLink supports batch writing for selected databases and for JDBC 2.0 batch-compliant drivers. Select this option if you use a JDBC driver that supports sending groups of INSERT, UPDATE, and DELETE statements to the database in a single transaction, rather than individually. Select JDBC to use the batch writing capabilities of your JDBC driver. Select TopLink to use the native batch writing capabilities that TopLink provides.

Select this option if your JDBC driver does not support batch writing.