I am seeing occasional long transactions (59 seconds or over 1 minute) when most of my transactions
I am seeing an occasional long transaction in my system. Normally, my transactions run in under 10seconds but there have been a few that are taking a a lot longer. I have a log table that captures blow by blow of what is happening and when I look at the log I see gaps in the log entry times that I cannot explain. Below is an example of the gaps I am seeing.
- 8/29/2019 3:57:39 PM to 8/29/2019 3:57:41 PM --> my transaction went silent at the start
- 8/29/2019 3:57:47 PM to 8/29/2019 3:57:57 PM
- 8/29/2019 3:58:00 PM to 8/29/2019 3:58:34 PM -->my transaction woke up at the end
If I can look at the rows from gv$active_session_history for the time interval, I do see my process running but it is never waiting on anything but "ON CPU". I know what SQL statement the process appears to be hanging on but that same statement is (or has been executed) hundreds of times per day. And it is only sometimes I see the pause.
1