Database Performance Issues - Full Table Scan join of table dlv_message and document_dlv_msg_ref
(Doc ID 1370830.1)
Last updated on MARCH 27, 2020
Applies to:
Oracle(R) BPEL Process Manager 10g - Version 10.1.3.5.0 and laterInformation in this document applies to any platform.
***Checked for relevance on 22-August-2013***
Symptoms
With 10.1.3.5 MLR 2 you are experiencing considerable performance issues with the BPEL hydration database. Your DBAs are continuing to monitor the BPEL hydration database, and have reported the following SQL statement consuming the majority of system resources when executing:
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 = 0
AND ddmr.message_type = 0
AND dm.message_guid = ddmr.message_guid
ORDER BY dm.message_guid
/*+ 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 = 0
AND ddmr.message_type = 0
AND dm.message_guid = ddmr.message_guid
ORDER BY dm.message_guid
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 |
| Cause |
| Solution |
| References |