pin_inv_accts Poor Performance Due To Access To au_purchased_product_t Table
Last updated on SEPTEMBER 19, 2016
Applies to:Oracle Communications Billing and Revenue Management - Version 22.214.171.124.0 to 126.96.36.199.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***
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
- 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:
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