Answer: You can query the
gv$lock and gv$session views to locate a blocking session in RAC.
Killing a session in RAC is different than killing an ordinary blocking
session, and dba_blockers and dba_waiters cannot
always help identify blocking RAC sessions.
For full scripts, download the Oracle
script collection.
Miladin Modrakovic offers this script to detect and
kill RAC blocking sessions, using
gv$session and gv$lock:
CREATE OR REPLACE PROCEDURE kill_blocker
AS
sqlstmt
VARCHAR2 (1000);
BEGIN
FOR x IN (SELECT gvh.SID sessid, gvs.serial#
serial,
gvh.inst_id instance_id
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 gvh.id2 = gvw.id2
AND gvh.request = 0
AND gvw.lmode = 0
AND gvh.SID = gvs.SID
AND gvh.inst_id = gvs.inst_id)
LOOP
sqlstmt :=
'ALTER SYSTEM KILL SESSION "'
|| x.sessid
|| ','
|| x.serial
|| ',@'
|| x.instance_id
|| "";
DBMS_OUTPUT.put_line (sqlstmt);
EXECUTE IMMEDIATE sqlstmt;
END kill_blovk;
END TEST;
/
When you run this script it will generate the
alter system
kill session syntax for the RAC blocking session:
SQL> set serveroutput on
SQL> exec kill_blocker;
ALTER SYSTEM KILL SESSION '115,9779,@1′
PL/SQL procedure successfully completed.
Also see these related notes on Oracle blocking
sessions:
SELECT DECODE (l.BLOCK, 0,
'Waiting', 'Blocking ->') user_status
,CHR (39) || s.SID || ',' || s.serial# || CHR (39)
sid_serial
,(SELECT
instance_name FROM gv$instance WHERE inst_id = l.inst_id)
conn_instance
,s.SID
,s.PROGRAM
,s.osuser
,s.machine
,DECODE (l.TYPE,'RT', 'Redo Log Buffer','TD',
'Dictionary'
,'TM', 'DML','TS',
'Temp Segments','TX', 'Transaction'
,'UL', 'User','RW', 'Row Wait',l.TYPE) lock_type
--,id1
--,id2
,DECODE (l.lmode,0, 'None',1, 'Null',2, 'Row
Share',3, 'Row Excl.'
,4, 'Share',5, 'S/Row Excl.',6, 'Exclusive'
,LTRIM (TO_CHAR (lmode, '990'))) lock_mode
,ctime
--,DECODE(l.BLOCK, 0, 'Not Blocking', 1,
'Blocking', 2, 'Global') lock_status
,object_name
FROM
gv$lock l
JOIN
gv$session s
ON (l.inst_id = s.inst_id
AND l.SID = s.SID)
JOIN gv$locked_object o
ON (o.inst_id = s.inst_id
AND s.SID = o.session_id)
JOIN dba_objects d
ON (d.object_id = o.object_id)
WHERE (l.id1, l.id2, l.TYPE) IN (SELECT id1, id2,
TYPE
FROM gv$lock
WHERE request > 0)
ORDER BY id1, id2, ctime DESC;
Also see these notes on RAC blocking sessions
detection:
find blocking sessions with
v$session
find the data block for a
blocking
session
If a session is holding on to locks too long, other
sessions may have to wait excessively, and the end user waiting on the
lock will see that their performance is slow. It is often helpful to
understand how GES locks in Oracle RAC can be viewed to determine the
sessions that are blocked and their blockers.
To start, it is interesting to note if a resource is
on the grant queue and which instance is the resource master. The
gv$ges_resource view can provide this information. Resource names
have the format "[0xblock_id][0xfile_id],BL" with BL meaning the Block
Lock or sometimes called the Buffer Lock. The block id and file_id are
in hexadecimal format. The query below shows a table that resides in
file 5 from blocks 160 to 167.
SQL> select
2
file_id,
3
block_id as start_block,
4
block_id+blocks-1 as end_block
5
from
6
dba_extents
7
where
8
owner='HR'
9
and
10
segment_name='EMPLOYEES'
11
order by
12
file_id,
13
block_id;
FILE_ID START_BLOCK END_BLOCK
---------- ----------- ----------
5
160
167
The file and block numbers need to be converted to
hexadecimal. File 5 is 0x5. Block 160 is 0xA0 and block 167 is 0xA7. The
resource name for the first block would be "[0xA0][0x5],BL" and we can
look in gv$ges_resource for
more information on any of the resources for this table similar to the
following query.
SQL> select
2
resource_name,
3
on_grant_q,
4
master_node+1 as master_inst
5
from
6
gv$ges_resource
7
where
8
resource_name like '[0xA%][0x5],BL%';
RESOURCE_NAME
ON_GRANT_Q MASTER_INST
------------------------------ ----------
-----------
[0xA2][0x5],[BL][ext 0x0,0x
1
2
The output above shows that one of the blocks is on
the grant queue. The resource master is instance 2 of the clustered
database. Note that the
master_node column has a value of zero for instance id 1 so the
query above adds one to obtain the instance identifier.
Now that a resource name can be determined, it can
be used in the
gv$ges_blocking_enqueue view to see blockers and those that are
blocked.
SQL> select
2
inst_id,
3
pid,
4
resource_name1 as resource_name,
5
blocker,
6
blocked,
7
owner_node
8
from
9
gv$ges_blocking_enqueue
10
order by
11
resource_name;
INST_ID
PID RESOURCE_NAME
BLOCKER
BLOCKED OWNER_NODE
-------- ------ ------------------------------
------- ---------- ----------
2
30421 [0xA7][0x5],[RS][ext 0x0,0x0]
1
0
1
1
29102 [0xA7][0x5],[RS][ext 0x0,0x0]
0 1
0
2
14494 [0x2000e][0x65a],[TX][ext 0x2,
0
1
1
1
29086 [0x2000e][0x65a],[TX][ext 0x2,
1
0
0
The first two lines show the resource being blocked
on instance 1 from a session on instance 2. Note the resource name
conforms to the hr.employees
table as shown earlier in this section. The blocker and blocked columns
identify that instance 2 has the lock and is blocking the session on
instance 1.
The last two lines show a transaction (TX) lock that
is probably familiar to most readers of this book. What is interesting
are the participants involved with the TX lock. Joining
gv$ges_blocking_enqueue to
gv$process and gv$session
gives us insight.
<
ges_blocking_programs.sql
SQL> select
2
s.inst_id,
3
p.spid,
4
s.program
5
from
6
gv$session s
7
join
8
gv$process p
9
on s.inst_id=p.inst_id
10
and
11
s.paddr=p.addr
12
join
13
gv$ges_blocking_enqueue e
14
on p.inst_id=e.inst_id
15
and
16
p.spid=e.pid;
INST_ID SPID PROGRAM
---------- -----
------------------------------------------------
1 29102 oracle@host01 (CKPT)
1 29086 oracle@host01 (LMD0)
2 30421 oracle@host02 (CKPT)
2 14494 sqlplus@host02 (TNS V1-V3)
Comparing the
spid column of this output to the
pid column of the previous
output, it is clear that the checkpoint process on each instance holds
the two RS resource locks. The holder of the TX lock
(pid 29086) is the Lock Manager Daemon process (LMD). The waiter
of the TX (pid 14494) is the SQL*Plus session. From the GES perspective,
the LMD process holds the lock on the resource, not a user's session in
the instance. Keep in mind that the
gv$ges_blocking_enqueue view is showing resource locks, not
transactional locks in the Oracle database engine.
The TX resource locks are named "[0x2000e][0x65a],[TX]"
with the first parameter being hexadecimal 2000E. Converting to decimal,
this becomes number 131086. This value can be used to query the
id1 column of the gv$lock
view for TX locks.
SQL> select
2
inst_id,
3
sid,
4
type,
5
lmode,
6
request
7
from
8
gv$lock
9
where
10
id1=131086;
INST_ID
SID TY
LMODE
REQUEST
-------- ---------- -- ---------- ----------
1 31 TX
6
0
2 33 TX
0
6
The query above is similar to one that readers may
already be familiar with. The TX locks are identified in the instance,
one has a lock mode of 6 and the other is waiting for the exclusive lock
to be released so that it can obtain the same lock mode. The next
queries verify that the TX locks are held by SQL*Plus sessions.
SQL> select
2
program
3
from
4
gv$session
5
where
6
inst_id=1
7
and
8
sid=31;
PROGRAM
------------------------------------------------
sqlplus@host01 (TNS V1-V3)
SQL> select
2
program
3
from
4
gv$session
5
where
6
inst_id=2
7
and
8
sid=33;
PROGRAM
------------------------------------------------
sqlplus@host02 (TNS V1-V3)
As was expected, both sessions participating in the
output from gv$lock are the
SQL*Plus sessions running this test.
