Last updated on SEPTEMBER 26, 2016
Applies to:Oracle Communications Billing and Revenue Management - Version 220.127.116.11.0 to 18.104.22.168.0 [Release 7.3.1 to 7.5.0]
Information in this document applies to any platform.
Checked for relevance on 19-Nov-2013.
***Checked for relevance on 4-May-2015***
The purpose of this article is to address one aspect of improving the BRM database performance during billing activities and other searches.
Consider below incident :
During billing, the performance started to degrade all of a sudden, while the performance was normal in previous billing cycle. As per investigation of the AWR reports gathered during the bad performance period, the below taxation query was causing the bottleneck :
SELECT TAX_CODE , SUM (AMOUNT_DEFERRED) "AMOUNT" FROM (SELECT /*+ INDEX(eb
I_EVENT_BI_ITEM_OBJ__ID) ORDERED */ EB.RESOURCE_ID , EB.TAX_CODE ,
EB.AMOUNT_DEFERRED FROM ITEM_T I , EVENT_BAL_IMPACTS_T EB WHERE
EB.AMOUNT_DEFERRED != 0 AND EB.RESOURCE_ID = :b1 AND I.POID_DB IS NOT NULL
AND I.POID_ID0 = EB.ITEM_OBJ_ID0 AND I.BILLINFO_OBJ_ID0 = :b2 AND ( (
I.EFFECTIVE_T = 0 AND I.BILL_OBJ_ID0 = :b3 ) OR ( I.EFFECTIVE_T > :b4 AND
I.EFFECTIVE_T <= :b5 AND I.ARCHIVE_STATUS = 0 AND I.BILL_OBJ_ID0 = 0 ) ) )
GROUP BY TAX_CODE
The above query was taking 4-6 seconds to return.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms