Billing Extremely Slow On Oracle 12c (Doc ID 2205885.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Communications Billing and Revenue Management - Version 7.5.0.0.0 and later
Information in this document applies to any platform.

Symptoms

We have migrated  from 11.2.0.4 Oracle DB to 12c

We have found that billing is extremely slow during item_t update. The following query is doing full scans on iem_t

UPDATE ITEM_T I
  SET MOD_T = :B4,
  DUE_T = :B12,
  ITEM_NO = DECODE (:B11, 1, ITEM_NO, (:B10 || ',' || ROWNUM)),
  AR_BILL_OBJ_DB = :B9,
  AR_BILL_OBJ_ID0 = :B8,
  AR_BILL_OBJ_TYPE = :B7,
  AR_BILL_OBJ_REV = :B6,
  STATUS = DECODE (DUE - DISPUTED, 0, 4, 2),
  EFFECTIVE_T = :B5,
  CLOSED_T = DECODE (DUE - DISPUTED, 0, :B4, CLOSED_T),
  OPENED_T = DECODE (GREATEST (DUE - DISPUTED, 0), 0, OPENED_T, :B4),
  DUE = DECODE (:B3, 1, PIN_ROUND_ON_SCALE (DUE, :B2, :B1), DUE),
  DELTA_DUE =
  DECODE (:B3, 1, DUE - PIN_ROUND_ON_SCALE (DUE, :B2, :B1), 0)
WHERE BILL_OBJ_ID0 IN
  (SELECT DECODE (
  POID_ID0 - AR_BILLINFO_OBJ_ID0,
  0, BILL_OBJ_ID0,
  DECODE (LAST_BILL_OBJ_ID0, 0, -1, LAST_BILL_OBJ_ID0))
  FROM BILLINFO_T
  WHERE AR_BILLINFO_OBJ_ID0 = :B13)
  AND STATUS = 1

This is the explain plan

UPDATE STATEMENT ALL_ROWSCost: 2,510,880 Bytes: 134 Cardinality: 1
  6 UPDATE PIN.ITEM_T
  5 COUNT
  4 HASH JOIN RIGHT SEMI Cost: 2,510,880 Bytes: 134 Cardinality: 1
  2 TABLE ACCESS BY INDEX ROWID BATCHED TABLE PIN.BILLINFO_T Cost: 5 Bytes: 28 Cardinality: 1
  1 INDEX RANGE SCAN INDEX PIN.I_AR_BILLINFO_OBJ__ID Cost: 3 Cardinality: 1
  3 TABLE ACCESS FULL TABLE PIN.ITEM_T Cost: 2,510,848 Bytes: 356,277,024 Cardinality: 3,361,104

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