Contention Cause By The INDEX XT033S3 During BILLING (Doc ID 2137682.1)

Last updated on MAY 16, 2016

Applies to:

Oracle Utilities Customer Care and Billing - Version 2.3.1 and later
Information in this document applies to any platform.

Symptoms

On :  2.3.1 version, DB - Database

ACTUAL BEHAVIOR  
---------------
Contention caused by the INDEX XT033S3 during BILLING  

Runing an oracle CCnB 2.3 on a 11gR2 single instance database, during the batch night, one has some severe contentions on an index from the table CI_BILL during the billing : XT033S3.

SQL_ID        CURRENT_OBJ# OBJECT_NAME                   WAIT_COUNT
------------- ------------ ----------------------------- ----------
2nsaqr6w75ya5       982896 XT033S3                            24705
2nsaqr6w75ya5       982895 XT033S2                               10
2nsaqr6w75ya5       982893 XT033P0                                7
2nsaqr6w75ya5           -

One of the insert (2nsaqr6w75ya5) used in the batch get slowed down by enq TX : allocate ITL entry.
Raising the ini_trans up to 40 did not provide positive results.

EXPECTED BEHAVIOR
-----------------------
In the test environment, which is iso-production data-wise, marking XT033S3 as unusable gives excellent insert performance, the average elapsed time before was between 3 and 7 seconds, now it is no longer seeable in the AWR.
So the question is : As it is a standard Oracle INDEX made for CCnB, can this index it be marked unusable ? Marking it unusable didn't give any side effect but it was in a range of 10 to 15 days.


STEPS
-----------------------
Check the AWR reports and compare tab SQL ordered by Elapsed Time in a report when index XT033S3 is used and in one where it is disabled.

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