I need an expert opininon on this
We have a vendor supplied application that sends data into the database via MQ queue.. The data constantly gets inserted into a staging table and there is a second process, that takes this data away.. It does it by issueing a SELECT ............ FROM TABLE where ROWNUM < 100, and subsequently deleting them by issuing DELETE FROM TABLE WHERE ID= :1;
At peak hours, there would be 100 simultaneous sessions writing from the queue into this single table, while there could be many SELECT/DELETE running on this table, this is causing lot of contention on the underlying PK index and I huge BUFFER BUSY waits.
Application team have been complaining about the performance when the load increases to 100 concurrent sessions.. Just to give you an idea, the SELECT ran 26K times in 30 min period.
Wonder, if there is a better way to design this table to improve the throughput.. I know reducing the FREQUENCY of SELECT/DELETE may improve the situation. Any better design alternatives ?
The table won't have data older than the last 1 mins.. So, partitioning is definitely not an option..
Appreciate your thoughts and response here.. Thanks