Version Info:
RDBMS version: 11.2.0.4 (July 2017 PSU)
OS : Oracle Linux 7.3
3-Node RAC DB
At 2PM, I noticed DB slownes with several DMLs waiting in my DB. For the next 1 hour, I had killed several blocking sessions at least 5 times. Each time I was killing at least 20 blocking sessions using information provided by the below query
SELECT s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
WHERE s1.sid=l1.sid AND s2.sid=l2.sid
AND l1.BLOCK=1 AND l2.request > 0
AND l1.id1 = l2.id1
AND l1.id2 = l2.id2;
Luckily, my colleague spotted the below UPDATE statement on the most important table in this DB. It didn't have a WHERE clause !!! and this was the root cause of all the issues in the DB.
This query was executed from SQLDeveloper. Although I killed blocking sessions from at least 5 times, This rogue session was never killed . I know this because GV$SESSION.LOGON_TIME of this rogue session was 1:30 PM which is before the DB slowness issue started. Why was this rogue session never got classified as a blocking session (and killed) ?
--- The rogue SQL without WHERE clause
UPDATE pkt_order
SET last_upd = SYSDATE,
last_upd_by = 'GB6392B',
stat_code = 'CYP 4',
desc_text = desc_text || 'CYP Code updated as part of CR83651'