Poor Performance During Deferred Taxation (Doc ID 1541723.1)

Last updated on MARCH 01, 2017

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.4.0.0.0 to 7.5.0.0.0 [Release 7.4.0 to 7.5.0]
Information in this document applies to any platform.
*** Checked for relevance on 04-17-2015 ***

Symptoms

 

Poor performance is observed during Billing-time / Deferred taxation

The billing process is taking too long to complete due to below query :

 


SELECT TAX_CODE, SUM(AMOUNT_DEFERRED) "AMOUNT", TAX_LOCALES
FROM (
SELECT /*+ USE_CONCAT INDEX(eb I_EVENT_BI_ITEM_OBJ__ID) ORDERED */ EB.RESOURCE_ID, EB.TAX_CODE, EB.AMOUNT_DEFERRED, E.TAX_LOCALES, E.TAX_SUPPLIER
FROM ITEM_T I, EVENT_BAL_IMPACTS_T EB, EVENT_T E
WHERE ((EB.OBJ_ID0 > :B11 AND EB.OBJ_ID0 <= :B10 ) OR (EB.OBJ_ID0 > :B9 AND EB.OBJ_ID0 <= :B8 ) OR EB.OBJ_ID0 <= :B7 ) AND EB.AMOUNT_DEFERRED <> 0 AND EB.RESOURCE_ID = :B6 AND I.POID_DB IS NOT NULL AND I.POID_ID0 = EB.ITEM_OBJ_ID0 AND E.POID_ID0 = EB.OBJ_ID0 AND I.BILLINFO_OBJ_ID0 = :B5 AND E.TAX_SUPPLIER = :B4 AND EB.OBJ_ID0 = (CASE WHEN BITAND(EB.OBJ_ID0, 1152921504606846976) <> 0 THEN (CASE WHEN NOT EXISTS
(SELECT START_POID_ID0
FROM TMP_UNPROCESSED_EVENTS_T
WHERE EB.OBJ_ID0 BETWEEN START_POID_ID0 AND END_POID_ID0) THEN EB.OBJ_ID0 ELSE 0 END) ELSE EB.OBJ_ID0 END) AND ( ( ((I.EFFECTIVE_T = 0) OR (I.EFFECTIVE_T <= :B1 AND I.STATUS = 1)) AND I.BILL_OBJ_ID0 = :B3 ) OR ( I.EFFECTIVE_T >= :B2 AND I.EFFECTIVE_T < :B1 AND I.ARCHIVE_STATUS = 0 AND I.BILL_OBJ_ID0 = 0 ) ) ) GROUP BY TAX_CODE, TAX_LOCALES

 
 

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