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

Last updated on AUGUST 03, 2022

Applies to:

Symptoms

On CCB v2.3.1, DB - Database

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

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


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

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