Slow Performance of purge_instances_oracle.sql when Purging BPEL Dehydration Store
(Doc ID 555970.1)
Last updated on SEPTEMBER 10, 2019
Applies to:
Oracle(R) BPEL Process Manager 10g - Version 10.1.3.3 and laterInformation in this document applies to any platform.
***Checked for relevance on 15-Dec-2009***
***Checked for relevance on 16-May-2011***
purge_instances_oracle.sql
online_rebuild_index_oracle.sql
BPEL 10.1.3.x
Symptoms
The purge_instances_oracle.sql procedure executes for an unreasonably long time as reported in <Bug 6806290> and <Bug 6727429>. (Even with the optimization in <Bug 6501312> applied.)
A SQL trace of the purge might appear to be stuck on the following DELETE but an analysis of the trace file should reveal that the script is in a slow CURSOR FOR-LOOP:
DELETE FROM document_dlv_msg_ref
WHERE message_guid = r_invoke_message.message_guid;
Or the purge might appear to be stuck on the following DELETE which is its own bottleneck:
DELETE FROM xml_document
WHERE dockey NOT IN
(SELECT dlv_ref.dockey FROM document_dlv_msg_ref dlv_ref
UNION
SELECT doc_ref.dockey FROM document_ci_ref doc_ref );
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 |
Example Purge Script Setup/Execution |
Alternatives |
Reporting Purge Performance Problem to Oracle Support |
References |