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 DECEMBER 05, 2023
Applies to:
Oracle SOA Suite - Version 11.1.1.1.0 to 11.1.1.2.0 [Release 11gR1]Oracle(R) BPEL Process Manager 10g - 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.
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.
Changes
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! |
In this Document
| Symptoms |
| Changes |
| Cause |
| Solution |
| References |