Performance Issue of PCM_OP_CUST_SET_STATUS (Doc ID 3019520.1)

Last updated on APRIL 30, 2024

Applies to:

Symptoms

Scenario:

The performance for PCM_OP_CUST_SET_STATUS is not good in the below scenario:

• for one subscriber, the main package is changed many times and, as a result, there are a lot of products cancelled in the purchased_product main table. Details of this step are mentioned below:
  1. create a subscriber with a main package, pk1
  2. main package means a normal package with many products(62) and discounts(23) inside
  3. change main package, pk1, with another one, pk2
  4. change pk2 with pk1
  5. change this package many times, say 7 times. In the purchased_product table there would be more than 400 entries with products in status 3 (for the packages cancelled) and 1 entry for the products included in the current main package
  6. Now change all the products/discounts available for this subscriber to status INACTIVE by calling op_cust_set_status to set status of service (in service_t) as 10102. Note that this status change for the products is done automatically by system.

Issue is that the call to op_cust_set_status is taking more time, in the above example, more than 40 seconds to complete.

Cause

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