Slow Performance Of SOA Purge Job (Doc ID 2209967.1)

Last updated on MAY 08, 2023

Applies to:

Goal

On : 12.1.3.0.0 version, BPEL Service Engine

Slow Performance of SOA Purge Job

Customer ran the SOA purge job (DELETE_INSTANCES_AUTO_JOB2) three times per day for four hours per run.
It is unable to cope with the volume of rows being generated by SOA. It is configured to retain only 7 days of data, but is currently over one month behind and falling further behind each day.
On investigation, it spends 90% of its time performing a single SQL statement (sqlid f8g5kx5y4djrs, an insert into TEMP_CUBE_INSTANCE).
The Oracle SQL tuning advisor has recommended a new index on CUBE_INSTANCE, and they have created this in the PreProduction database and it has hugely reduced the cost of the insert statement.
 

Solution

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