My Oracle Support Banner

Slow Performance of purge_instances_oracle.sql when Purging BPEL Dehydration Store (Doc ID 555970.1)

Last updated on MARCH 02, 2019

Applies to:

Oracle(R) BPEL Process Manager 10g - Version and later
Information in this document applies to any platform.
***Checked for relevance on 15-Dec-2009***
***Checked for relevance on 16-May-2011***

BPEL 10.1.3.x


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
      SELECT doc_ref.dockey FROM document_ci_ref doc_ref );


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
 Example Purge Script Setup/Execution
 Reporting Purge Performance Problem to Oracle Support

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.