Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Killed lot of blocking sessions. But, that didn't kill the rogue SQL without WHERE clause.

J.KiechleSep 15 2017 — edited Sep 15 2017

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'

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 13 2017
Added on Sep 15 2017
3 comments
1,145 views