Item Publish Services Not Responding After Conversion (Doc ID 2988505.1)

Last updated on NOVEMBER 23, 2023

Applies to:

Symptoms

Item Publish Services not responding after conversion

Following a conversion cycle where around 2.3 Million SKUs and their parents were loaded - so around 4 Millions lines.
After the Lift and Shift the Item Publishing web-service was enabled in the UI and ran the MERCHAPI_REFRESH_ADHOC_JOB batch to activate the web-service and do the json store initial load. This job ran for many hours but finished with success and the json store loaded with expected volume of data:

select count(*) from MERCHAPI_EXT_ITEM
COUNT(*)
4137044
1 rows returned in 1.13 seconds

The problem is that if you call the web-service foundation/item without parameters, it hangs indefinitely and eventually errors out with time out, even selecting the page limit to 10 items :

GET /MerchIntegrations/services/foundation/item?limit=10

If trying to get the content of an individual item, it works :
 
GET /MerchIntegrations/services/foundation/item/11433679
200
139 ms

or, if you want to get changes since a certain time after the data conversion - so small number of items have been changed, it also works:
GET /MerchIntegrations/services/foundation/item?since=2023-08-18T14:00:31.251Z&limit=10
200
9.60 s

So why is the web service not returning data when calling the web service without parameters?



STEPS
-----------------------
The issue can be reproduced at will with the following steps:
1. Call the publish item webservice with a page size of 10:
  /MerchIntegrations/services/foundation/item?limit=10

2. Call hangs and never return data


BUSINESS IMPACT
-----------------------
The issue has the following business impact:
Due to this issue, users cannot call the webservice without specifying parameters.

Cause

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