BPEL & Human Workflow Performance Issue in 10.1.3.4, 10.1.3.5 and 11g - Full Table Scan on orabpel.dlv_subscription Table (Doc ID 1170777.1)

Last updated on NOVEMBER 03, 2016

Applies to:

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

Symptoms


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 ddmr.MESSAGE_TYPE = :"SYS_B_1"
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.

 

Cause

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