Find blocking sessions:
Blocking sessions occur when one sessions holds an exclusive lock on an object and doesn't release it before another
sessions wants to update the same data. This will block the second until the first one has done its work.
From the view of the user it will look like the application completely hangs while waiting for the first session to release
its lock. You'll often have to identify these sessions in order to improve your application to avoid as many blocking
sessions as possible.
select
blocking_session,
sid,
serial#,
wait_class,
seconds_in_wait,
event
from
v$session
where
blocking_session is not NULL
order by
blocking_session;
SELECT s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$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;
Identifying blocked objects:
The view v$lock we've already used in the queries above exposes even more information. There are differnet kind of locks -
check this site for a complete list:
If you encounter a TM lock is means that two sessions are trying to modify some data but blocking each other. Unless one
sessions finished (commit or rollback), you'll never have to wait forever.
The following queries shows you all the TM locks:
SELECT sid, id1 FROM v$lock WHERE TYPE='TM'
SID ID1
92 20127
51 20127
The ID you get from this query refers to the actual database object which can help you to identify the problem, look at the
next query:
SELECT object_name FROM dba_objects WHERE object_id=20127
===========================
The simplest query for determining database state performance wise would be this:
SQL> select event, state, count(*) from v$session_wait group by event, state order by 3 desc;
EVENT STATE COUNT(*)
---------------------------------------------------------------- ------------------- ----------
rdbms ipc message WAITING 9
SQL*Net message from client WAITING 8
log file sync WAITING 6
gcs remote message WAITING 2
PL/SQL lock timer WAITING 2
PL/SQL lock timer WAITED KNOWN TIME
SQL> select
2 count(*),
3 CASE WHEN state != 'WAITING' THEN 'WORKING'
4 ELSE 'WAITING'
5 END AS state,
6 CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
7 ELSE event
8 END AS sw_event
9 FROM
10 v$session
11 WHERE
12 type = 'USER'
13 AND status = 'ACTIVE'
14 GROUP BY
15 CASE WHEN state != 'WAITING' THEN 'WORKING'
16 ELSE 'WAITING'
17 END,
18 CASE WHEN state != 'WAITING' THEN 'On CPU / runqueue'
19 ELSE event
20 END
21 ORDER BY
22 1 DESC, 2 DESC
23 /
COUNT(*) STATE EVENT
---------- ------- ----------------------------------------
6 WAITING PL/SQL lock timer
4 WORKING On CPU / runqueue
3 WAITING db file sequential read
1 WAITING read by other session
1 WAITING Streams AQ: waiting for messages in the
1 WAITING jobq slave wait
6 rows selected.
By the way, the above scripts report quite similar data what ASH is actually using (especially the instance performance
graph which shows you the instance wait summary). ASH nicely puts the CPU count of server into the graph as well (that you
would be able to put the number of “On CPU” sessions into perspective), so another useful command to run after this script
is “show parameter cpu_count” or better yet, check at OS level to be sure :)
Note that you can use similar technique for easily viewing the instance activity from other perspectives/dimensions, like
which SQL is being executed:
SQL> select sql_hash_value, count(*) from v$session
2 where status = 'ACTIVE' group by sql_hash_value order by 2 desc;
SQL_HASH_VALUE COUNT(*)
-------------- ----------
0 20
966758382 8
2346103937 2
3393152264 1
3349907142 1
2863564559 1
4030344732 1
1631089791 1
8 rows selected.
SQL> select sql_text,users_executing from v$sql where hash_value = 966758382;
SQL_TEXT USERS_EXECUTING
------------------------------------------------------------ ---------------
BEGIN :1 := orderentry.neworder(:2,:3,:4); END; 10
==========================================
Find the blocking session detail from history table:
==========================================
set pagesize 50
set linesize 120
col sql_id format a15
col inst_id format '9'
col sql_text format a50
col module format a10
col blocker_ses format '999999'
col blocker_ser format '999999'
SELECT distinct a.sql_id ,a.inst_id,a.blocking_session,a.blocking_session_serial#,a.user_id,s.sql_text,a.module
FROM GV$ACTIVE_SESSION_HISTORY a ,gv$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0 —- exclude SYS user
-- and a.sample_time > sysdate – 7
SELECT distinct a.sql_id, a.blocking_session,a.blocking_session_serial#,
a.user_id,s.sql_text,a.module
FROM V$ACTIVE_SESSION_HISTORY a, v$sql s
where a.sql_id=s.sql_id
and blocking_session is not null
and a.user_id <> 0
and a.sample_time between to_date('17/06/2011 00:00', 'dd/mm/yyyy hh24:mi')
and to_date('17/06/2011 23:50', 'dd/mm/yyyy hh24:mi');
==============================================
Procedure for finding the dynamic script for kill blocking session
==============================================
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;
