My Oracle Support Banner

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

Last updated on AUGUST 03, 2022

Applies to:

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


**Disclaimer:** This KM article may include the following abbreviations:

CCB - Oracle Utilities Customer Care and Billing
OUAF/FW - Oracle Utilities Framework
AWR - Automatic Workload Repository

On CCB v2.3.1, DB - Database

Contention caused by the INDEX XT033S3 during BILLING  

Runing an oracle CCB 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.

------------- ------------ ----------------------------- ----------
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.

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 CCB, 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.

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.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

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