Full Table Scan Query Causing Performance Issue on the BusinessInteractionItem Table (Doc ID 2604182.1)

Last updated on JANUARY 31, 2024

Applies to:

Symptoms

Reported issue from customer:
    Full table scan on this query which having the performance issue as the BusinessInteractionItem table which has around 175 million records.

The Query is coming from:
    Based on the logs that we identified that the query is getting  generated when we are deleting the entity.

Why is it added in later release?
     This is Added as part of the EWO support enhancement.

How is it related when the customer is not using the EWO?
      When we are deleting, it is cross checking that the entity is not referenced anywhere in the activity.
      As the EWO and BI are sharing the same model, it is experiencing the BusinessInteractionItem full table scan.


Following SQL is observed in UIM:

Test Case:
1.  Create a Engineering Work Order with template and go to activity
     Add any resource which is in installed state( ex : logical device) in activity work space
     Delete the resource from search page in live context.
     It should give proper error message.
2.  Create a resource in live context and delete the resource                                                                                                                        
     It should get deleted without any errors and log should not contain the problematic query.
3.  Tested around the delete and in business interaction context and are all working as expected.

Changes

Cause

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