Have you ever heard:
“My session was killed. I’m trying again. There is no locks. But My SQL doesn’t run!”
By default, when a session is disconnected from Oracle, the SQLs uncommitted are undone (rollback). So, in case of a long routing there is a probably a rollback in place.
How Oracle rollback take that long? Well, it’s Oracle mechanism. The RDBMS basically assume you know what your are doing and start writing the new blocks so the commit will be very quick. Which, however, is bad in case of rollbacks.
How to check if this is your case?
select ktuxeusn, to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') "Time", ktuxesiz, ktuxesta from x$ktuxe where ktuxecfl = 'DEAD' 4 / KTUXEUSN Time KTUXESIZ KTUXESTA ---------- -------------------- ---------- ---------------- 31 07-APR-2018 16:48:53 17705 ACTIVE 48 07-APR-2018 16:48:53 108856 ACTIVE
Hm.. What can I do? Not much really, we need to wait the rollback to complete.
One thing that can be done to increase the rollback is changing the fast_start_parallel_rollback parameter, as per:
SQL> show parameters roll NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fast_start_parallel_rollback string LOW rollback_segments string transactions_per_rollback_segment integer 5 SQL> alter system set fast_start_parallel_rollback=HIGH; System altered.
Hope it helps!
Cheers!
