pin_inv_accts Poor Performance Due To Access To au_purchased_product_t Table (Doc ID 1521527.1)

Last updated on AUGUST 07, 2024

Applies to:

Symptoms

Scenario:

A production system with over 10 million accounts to bill, split into three schemas, as below:
- Number of account : More than 10 100 000
- Split on 3 secondary schema, with around 3 500 000 million accounts each
- Number of service : 1 per account
- Number of purchased_product : Around 10 per account. So around 35 000 000 on one schema
- Billing / invoicing : one per day from day 1 to 28
- Even day : small billing, around 20 000 accounts split into the 3 schemas
- Odd day : Around 250 000 to 300 000 account billed par schemas

Issue :

- Saturation of CPU usage of Database server odd day
- More than 1 100 000 millions requests performed on /au_purchased_products objects, this causes a severe performance issue in production.
- pin_inv_accts is accessing the au_purchased_product_t many times, because of which billing is having poor performance.
- The DBA has captured the details about this table on Production system. Here is the query:

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.