RDBMS version :11.2.0.4
OS version : RHEL 6.5
While diagnosing a contention issue, I have noticed that the blocking SQL is not visible in gv$session.sql_id column
Without this crucial diagnostic info, I was not able to kill the blocking session. To explain this, I am using a simple demo
--- Demo
create table orders (order_id number, order_item varchar2(35), order_date date);
insert into orders values (1, 'APPLE', sysdate-5 );
insert into orders values (2, 'ORANGE', sysdate-3 );
insert into orders values (3, 'BLUBERRIES', sysdate-8 );
SQL> select * from orders;
ORDER_ID ORDER_ITEM ORDER_DAT
---------- ----------------------------------- ---------
1 APPLE 03-SEP-17
2 ORANGE 05-SEP-17
3 BLUBERRIES 31-AUG-17
Session1. The blocking session . After the below UPDATE, no rollback or commit is issued.
SQL> update orders set ORDER_DATE=sysdate;
3 rows updated.
SQL>
Session2. This session is being blocked by Session1
--- SQL2
SQL> update orders set ORDER_DATE=sysdate+3;
<hangs. waits for Session2 to COMMIT/ROLLBACK>
At this point the following query returns the SID, Serial of the blocking session.
But , Session1's gv$session.sql_id column value is NULL because its gv$session.status is in INACTIVE state.
This could be because the blocking session has finished UPDATE/DELETE and is waiting to ROLLBACK or COMMIT.
So, is there any way I could see the blocking SQL (SQL1 in this case) ?
SELECT gvh.SID sessid, gvs.serial# serial, gvh.type, gvh.lmode,gvh.inst_id instance_id,
gvs.sql_id, gvs.status
FROM gv$lock gvh, gv$lock gvw, gv$session gvs
WHERE (gvh.id1, gvh.id2) IN (SELECT id1, id2
FROM gv$lock
WHERE request = 0
INTERSECT
SELECT id1, id2
FROM gv$lock
WHERE lmode = 0)
AND gvh.id1 = gvw.id1
AND gvw.inst_id = gvh.inst_id
AND gvh.id2 = gvw.id2
AND gvh.type = gvw.type
AND gvh.request = 0
AND gvw.lmode = 0
AND gvh.SID = gvs.SID
AND gvh.inst_id = gvs.inst_id;
SESSID SERIAL TY LMODE INSTANCE_ID SQL_ID STATUS
---------- ---------- -- ---------- ----------- ------------- --------
8 39209 TX 6 2 INACTIVE
Note: If the table (blocked) is big, then the UPDATE/DELETE can take long and gv$session.status may remain 'ACTIVE' and I might be able to get the SQL_ID.
But, for small tables, is there any way I could see the blocking SQL (SQL1 in this case) ?