My Oracle Support Banner

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 JUNE 18, 2018

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

To view full details, 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 a vibrant support community of peers and Oracle experts.