In a recent engagement with a Remedy customer we looked into their performance issue and discovered that the slowdown in the database was stemming from "enq:Index contention" waits.
Drilling down further into the waits we found that the waits were on the "S" tables corresponding to the Remedy forms "SMT: Social_FollowConfig" and "HPD: WorkLog".
Drilling down further into the waits we found that the waits were on the "S" tables corresponding to the Remedy forms "SMT: Social_FollowConfig" and "HPD: WorkLog". These "S" tables were introduced in Remedy's newest implementation of RLS that was CA (Controlled Availability = limited to few chosen customers) in version 1902 and GA (General Availability) in version 1908.
The SQL statements that were waiting for an ITL (Interested Transaction List - at Oracle's block level) slot to open up were performing INSERTS into the two tables. The number of initial ITL slots when an index is created is specified by its INITRANS parameter. That parameter defaults to 1 for tables and 2 for indexes.
The default value of 2 for an index means that TWO transactions can each take one slot and perform an INSERT/UPDATE/DELETE operation on rows in the block.
If a third or fourth transaction comes along and needs to update rows in the block and both the slots are taken Oracle can allocate additional slots, up to the table's/index's MAXTRANS parameter, provided there is space available in the block.
For the customer in question we had them increase the value of INITRANS for the indexes that were experiencing concurrency waits. The increase was from 2 to 10 in one case and 15 in another index.
The change can be accomplished by executing the following SQL command:
SQL> alter index<index name here>INITRANS<new value here>