Recently I ran the Purge Inactive Sessions concurrent program after it had not run for a while, and based on my Workflow purging I wanted to make sure we didn't purge out too much data or else it would be likely the system would hit High Water Mark contention when trying to query the ICX_SESSIONS table. On the first day I purged out records to leave us above where I thought the HWM contention would occur, but I noticed even after a Gather Table Statistics concurrent program was run there was slight slow down on querying from ICX_SESSIONS but nothing in HWM enqueue yet. Reporting the issue to the DBAs, they suggested that I continue purging out past what I felt was safe since this was in QA and they were prepared to do a shrink to restore the table(s) affected by this program.
On the next day I ran the Purge Inactive Sessions report again, and as expected, there is now a performance problem with ICX_SESSIONS which I thought was due to purging too many records since a simple SELECT was not returning even after 90 minutes. Why is that bad performance? Before purging, the same statement was returning in less than a minute so obviously my statement must have been affected by the HWM enqueue contention right? Wrong! Curiouser, and curiouser. That wasn't what I expected at all. Since the report was still running, I decided to let it continue deleting before doing anything else and then when it completed I ran my SELECT statement. Guess what I found? That statement no longer took 90 minutes, as it was completing in a minute or two, nor did it go into HWM contention even before another Gather Table Statistics was done.
Why did my query take so long to return on the second day? My best guess is that the system was trying to calculate the number of results in the table, but was unable to do so while I was busy having the report deleting from the table so it was stuck until after the report was completed. When I do this in another environment prior to going to PROD, I'll be able to look at this closer and hopefully I'll gain more insight as to what was going on!