BPEL & Human Workflow Performance Issue in, and 11g - Full Table Scan on orabpel.dlv_subscription Table

(Doc ID 1170777.1)

Last updated on JUNE 18, 2018

Applies to:

Oracle SOA Platform - Version to [Release 11gR1]
Oracle(R) BPEL Process Manager - Version to
Information in this document applies to any platform.


During runtime, the following BPEL related query is executing for over 20 minutes:

SELECT /*+ INDEX ( dm dm_conversation ) INDEX ( ddmr doc_dlv_msg_ref_pk ) */
dm.conv_id, dm.conv_type, dm.message_guid, dm.domain_ref,
dm.process_id, dm.revision_tag, dm.operation_name, dm.receive_date,
dm.state, dm.res_process_guid, dm.res_subscriber, dm.properties,
dm.headers_ref_id, ddmr.dockey, ddmr.message_guid, ddmr.part_name,
ddmr.domain_ref, ddmr.MESSAGE_TYPE
FROM dlv_message dm, document_dlv_msg_ref ddmr
WHERE dm.conv_id = :1
AND dm.domain_ref = :2
AND dm.state = :"SYS_B_0"
AND dm.message_guid = ddmr.message_guid
ORDER BY dm.message_guid

The performance of the query slows down when there is some load to the system.



Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms