Poor Performance With Tax Procedure During Bill Now (Doc ID 1922797.1)

Last updated on DECEMBER 01, 2024

Applies to:

Goal

On BRM 7.4, there was very poor performance in production environment with the execution of the tax procedure during bill now.

From AWR report, this is the query causing performance:

"insert into event_billing_tax_cycles_t ( start_t, end_t, currency, bill_vat, bill_obj_DB, bill_obj_ID0, bill_obj_TYPE, bill_obj_REV, rec_id, obj_id0 ) values ( :start_t, :end_t, :currency, :bill_vat, :bill_obj_DB, :bill_obj_ID0, :bill_obj_TYPE, :bill_obj_REV, :rec_id, :obj_id0 )"

And, it triggered the performance problem in:

"SELECT TAX_CODE , SUM (AMOUNT_DEFERRED) "AMOUNT" , TAX_LOCALES FROM (SELECT /*+ 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 , BILL_T B , EVENT_T E WHERE EB.AMOUNT_DEFERRED != 0 AND EB.RESOURCE_ID = :b1 AND EB.OBJ_ID0 = E.POID_ID0 AND EB.ITEM_OBJ_ID0 = I.POID_ID0 AND I.POID_DB IS NOT NULL AND ( ( I.BILL_OBJ_ID0 = B.POID_ID0 AND I.OPENED_T >= 0 ) OR ( ( E.POID_TYPE LIKE '/event/billing/adjustment/%' OR E.POID_TYPE LIKE '/event/billing/dispute/%' OR E.POID_TYPE LIKE '/event/billing/settlement/%' ) AND ( :b2 = :b3 OR :b2 = I.BILLINFO_OBJ_ID0 ) AND :b4 < I.EFFECTIVE_T AND I.EFFECTIVE_T <= :b5 AND I.ARCHIVE_STATUS = 0 ) ) AND B.AR_BILLINFO_OBJ_ID0 = I.AR_BILLINFO_OBJ_ID0 AND B.POID_ID0 = :b6 AND I.AR_BILLINFO_OBJ_ID0 = :b3 AND E.TAX_SUPPLIER = :b7 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.BILL_OBJ_ID0 = :b6 ) OR ( I.AR_BILL_OBJ_ID0 = 0 AND ( I.EFFECTIVE_T = 0 OR I.EFFECTIVE_T <= :b5 ) ) ) ) GROUP BY TAX_CODE , TAX_LOCALES "

Note that in /config/tax_supplier, OOB configuration "pin_tax_supplier.xml" is configured with only one tax supplier:

Also, the tax is calculated for each charge when its generated.

What is the root cause of this performance issue and how to fix?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.