Dear DBA Friends,
DB version - 11.1.0.7
We have a table that receives Inserts 24/7 ( nearly 2 Million rows inserted each day) and we have a purge (Delete - Row ID based ) that runs every 4 hours and deletes (anything older than 2 days)...Total data volume in this table is always b/w 3 to 4 Million. Table is non-partitioned, but has 3 indices (All indices are Reverse key, partitioned with 128 partitions for each index)
Now the problem is, we are seeing huge concurrency waits whenever the purge is colliding with insert activity. The wait duration is usually b/w 5 - 20 min and is self resolved. But when concurrency occurs, app is seeing failures with data load activity...
Question is - If purge is deleting older data and inserts are new data that is being loaded, why concurrency waits? Table does not acquire an exclusive lock when its deleting rows... so why waits ?
Trying to understand how to troubleshoot the issue and avoid app failures.
Here's how Delete and Insert statements look for our application -