Tuning hot blocks
Issue 1 in one application:
One table with around 44 rows and one index on it. The size of the table is minimum extent, ie, 1 MB and index is also 1MB.
The data for the table and also data for the index reside in only one block.
The application is OLTP and this table is accessed (select) in a user defined function. This function is called in almost all the select statements (though web) issued by user. ie, when user opens an application page, navigates between pages etc.
It was found that lot of contention on this index block as the total no. of users is more (around 200 +). This table will be accessed once (through function) per row. So, this index is accessed more than 500 times concurrently leading to heavy contention on this index block.