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 laterInformation in this document applies to any platform.
Symptoms
CCB - Oracle Utilities Customer Care and Billing
OUAF/FW - Oracle Utilities Framework
AWR - Automatic Workload Repository
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
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
Symptoms |
Cause |
Solution |
References |