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

Last updated on SEPTEMBER 19, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.4.0.0.0 to 7.4.0.1.0 [Release 7.4.0]
Information in this document applies to any platform.
Checked for relevance on 2-Jan-2015.
***Checked for relevance on 29-August-2016***

Symptoms

A production system with above 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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms