Timeout/Performance Issue Caused by PCM_OP_SUBSCRIPTION_GET_PURCHASED_OFFERINGS (Doc ID 2917164.1)

Last updated on JANUARY 26, 2024

Applies to:

Goal

PCM_OP_SUBSCRIPTION_GET_PURCHASED_OFFERINGS and PCM_OP_BAL_GET_CREDIT_PROFILE are called by PCM_OP_SUBSCRIPTION_PURCHASE_DEAL opcode when a product has been purchased. Within PCM_OP_SUBSCRIPTION_GET_PURCHASED_OFFERINGS opcode flow, PCM_OP_READ_OBJ is called several times for all products and discounts, which indicates multiple queries being performed against purchased_prod/disc_t tables.

If the account has a lot of products/discounts, this behavior results in PCM_OP_SUBSCRIPTION_PURCHASE_DEAL taking considerably long execution time and introduces timeout or bad performance.

What is the need for performing such an action on all products/discounts of an account when only one single voucher/bundle/product is being purchased?

cm.pinlog has the following sample calls:

0.245899801 .Enter PCM_OP_BILL_PURCHASE_DEAL (0x0)
2.630414865 .Exit PCM_OP_BILL_PURCHASE_DEAL (0x0)
...
0.467681296 .....Enter PCM_OP_SUBSCRIPTION_GET_PURCHASED_OFFERINGS (0x0)
0.468700206 ......Enter PCM_OP_READ_OBJ (0x400)
0.468712657 ......Exit PCM_OP_READ_OBJ (0x400) - from cache
...
0.473876018 .....Exit PCM_OP_SUBSCRIPTION_GET_PURCHASED_OFFERINGS (0x0)
...
0.433991219 .......Enter PCM_OP_BAL_GET_CREDIT_PROFILE (0x0)
0.434080458 .......Exit PCM_OP_BAL_GET_CREDIT_PROFILE (0x0)

As recorded in dm_oracle.pinlog, below two queries of all SQL queries seem to be of interest, which can take upto 3 seconds for each table when ran in production for one account:
(a) select from purchased_discount_t
(b) select from purchased_product_t

Why are there queries performed against purchased_prod/disc_t tables when purchasing a product?
Why in the call stack, there is PCM_OP_BILL_PURCHASE_DEAL when calling PCM_OP_SUBSCRIPTION_PURCHASE_DEAL?

Solution

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