Poor performance running verify_soa.exec_row_movement_1 (Doc ID 2044922.1)

Last updated on SEPTEMBER 11, 2015

Applies to:

Oracle SOA Suite - Version 11.1.1.7.0 and later
Information in this document applies to any platform.

Goal

On: 11.1.1.7.0 version, Fabric Layer

When running the Oracle supplied procedure verify_soa.exec_row_movement_1, the SQL ran for over 24 hours before filling the UNDO tablespace.

update REFERENCE_INSTANCE set CPST_PARTITION_DATE = '24-MAY-15 05.12.23.000000 AM'
where composite_instance_id in (select id from verify_fabric_group1 )

It is doing a full table scan on both tables.
There are over 6 million rows in the verify_fabric_group1 table.
There is no index on the composite_instance_id column of the REFERENCE_INSTANCE table.

How can the performance of this be improved?
 

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms