Performance Issue With ICX Queries When Using Item-Related APIs From EGO_ITEM_PUB Package (Doc ID 1486605.1)

Last updated on MAY 15, 2023

Applies to:

Goal

Problem:

Performance issues occur when using below item-related APIs –
 
EGO_ITEM_PUB.Process_items
EGO_ITEM_PUB.Process_item_lifecycle
EGO_ITEM_PUB.Update_item_approval_status
EGO_ITEM_PUB.Process_item_org_assignments
EGO_ITEM_PUB.Process_item_cat_assignment

All these APIs seem to be performing poorly when the IProcurement-related queries are executed (iProcurement Product short name=ICX). You see a lot of executions in the trace files related to these causing a huge performance hit to bulk load operations using API.

Cause:
These APIs are not designed for bulk-load operations. The ICX-related queries in them are executed once for each item created by the API, instead of running once for the complete set of items created.

ICX API will always be executed in concurrent request context, or normal script context, but since ICX API is executed for each item in concurrent request context by INV package (as INV package is defined as seeded concurrent request), it's the cause of the performance issue.

As EGO_ITEM_PUB.PROCESS_ITEMS is not suggested to be executed in concurrent request

You may be seeing below queries taking a lot of time, with multiple executions in the trace file -

Solution

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