Find Sessions that are Blocking Other Sessions Using SQL

文章转自MOS文档：
Find Sessions that are Blocking Other Sessions Using SQL (文档 ID 728539.1)

APPLIES TO:

Oracle Database - Personal Edition - Version 7.1.4.0 and later
Oracle Database - Standard Edition - Version 7.0.16.0 and later
Enterprise Manager for Oracle Database - Version 8.1.7.4 and later
Oracle Database - Enterprise Edition - Version 6.0.0.0 and later
Information in this document applies to any platform.

GOAL

Sometimes a systemstate cannot be taken and uploaded to support, and it is desirable to manually find what sessions are blocking other sessions. This guide enables the 'blocking' session to be ascertained manually.

The note is written only with currently supported versions in mind 10G and onwards. Therefore, while some of the methodology applies to 9.2 and below, it cannot be guaranteed.

SOLUTION

To use this guide, you first of all need to find the SID of a session you believe is blocked. Using that SID together with the corresponding wait events (ie resource we want), you will use the associated SQL to
determine which session is blocking. You can then run the following select against v$session:-

SELECT event,p1,p2,p3 FROM v$session_wait WHERE sid=X AND wait_time=0

To determine if that session is also blocked, you need to repeat the process to find who is blocking this session or if this session is on the CPU (now rows returned).

Finding sessions which are blocked

Oracle processes will be in one of two possible states for the duration of their existence:-

o They will be waiting for a resource or call
o They will be on the CPU 

Processes on the CPU are not waiting and therefore cannot be blocked although it is quite possible and common for them to be blockers (due to the fact they own a resource another session may require). Processes waiting will either wait for a resource (an enqueue, a lock , a latch etc), or a call ( ie sql-net message from client, db file sequential read). In the case of calls, we are rarely blocked but if we are waiting for a resource we typically are. When looking for sessions which are blocked, it is very important the correct select is run against v$session (or v$session_wait in 10G if you wish). A very common mistake is to run the wrong select and assume a session is blocked waiting on a resource when actually that resource has been acquired and the process is actually on the CPU. Therefore, the correct sql to use is the following:

SELECT sid,event,seq#,p1,p1raw,p2,p3 FROM v$session WHERE wait_time=0;

....wait_time is zero if we are currently waiting. If we have completed, the wait is != 0. It is vital you use the wait_time predicate if you are looking for sessions which are potentially blocked. You should always run the select against v$session_wait multiple times to ensure you are seeing a genuine block versus just seeing a point in time image of the normal operation of the database (processes will sometimes have to wait for resources after all but this should be quick)

ENQUEUE

SELECT sid,type,lmode,request,id1,id2,request 
FROM v$lock 
WHERE id1={p2 from select against v$session} 
AND id2={p3 from select against v$session};

The following table can be used to determine which sessions with lmode > 0 are blocking:-

LMODE	Description	Name	NULL	SS	SX	S	SSX	X
-----	-----------	-------	-----	-----	-----	-----	-----	-----
0,1	No Lock	NULL	YES	YES	YES	YES	YES	YES
2	Row-Share	SS	YES	YES	YES	YES	YES	no
3	Row-Exclusive	SX	YES	YES	YES	no	no	no
4	Share	S	YES	YES	no	YES	no	no
5	Share Row-Excl	SSX	YES	YES	no	no	no	no
6	Exclusive	X	YES	no	no	no	no	no

LATCH  

SELECT sid FROM v$latchholder WHERE laddr=&p1raw;

    --- where &p1raw is p1raw from select against v$session

PIN  

SELECT s.sid, case kglpnmod when 2 then 'S' when 3 then 'X' end "Mode",
case kglpnreq when 2 then 'S' when 3 then 'X' end "Req"
FROM x$kglpn p, v$session s
AND kglpnhdl=' &p1raw';

   --- where &p1raw is p1raw from select against v$session

NOTE: An X request will be blocked by any pins held in S mode on the object. An S request will be blocked by any X mode pin held, or may queue behind some other X request, as X requests take precedence over
current S requests and do not operate on a FIFO basis.

LIBRARY CACHE LOCK   

SELECT s.sid,
  CASE x.KGLLKMOD
    WHEN 1
    THEN 'NULL'
    WHEN 2
    THEN 'S'
    WHEN 3
    THEN 'X'
  END "Mode",
  CASE x.KGLLKREQ
    WHEN 1
    THEN 'NULL'
    WHEN 2
    THEN 'S'
    WHEN 3
    THEN 'X'
  END "Req",
  x.KGLNAOBJ "Object Name"
FROM v$session s,
  x$kgllk x
WHERE x.KGLLKADR=s.saddr
AND KGLLKHDL    ='&p1raw';

     --- where &p1raw is p1raw from select against v$session

LIBRARY CACHE LOAD LOCK

Not currently possible

BUFFER BUSY WAIT

Not currently possible

ROW CACHE LOCK  

SELECT s.sid,
  CASE KQRFPMOD
    WHEN 0
    THEN 'NULL'
    WHEN 3
    THEN 'S'
    WHEN 5
    THEN 'X'
  END "Mode",
  CASE KQRFPREQ
    WHEN 0
    THEN 'NULL'
    WHEN 3
    THEN 'S'
    WHEN 5
    THEN 'X'
  END "Req"
FROM v$session s,
  X$KQRFP x
WHERE x.KQRFPSES=s.saddr
AND KQRFPCID    ='&p1raw';

   --- where &p1raw is p1raw from select against v$session

What next ?

If you are unable to complete the wait tree due to the holder not being possible to trace using sqlplus (ie
buffer busy waits), then you will need to provide a systemstate for Oracle Support to analyze. To do this
log in as sysdba and do:-  

ALTER session SET events 'immediate trace name systemstate level 266';

(You should run this twice, one straight after the other) 

Otherwise, ultimately you will find you will reach one of 3 scenarios:

1) There are no blockers which means there is no hang, and this is a performance issue.

For this, you need to gather an AWR report for a period of poor performance:

Document 276103.1 PERFORMANCE TUNING USING 10g ADVISORS AND MANAGEABILITY FEATURES

2) You find you have met a deadlock. 

For instance, say my first blocking session is SID 147 and waits for an enqueue:
SID 167 holds the enqueue and waits for a library cache lock
SID 187 holds the library cache lock and waits for a library cache pin
SID 167 holds the pin

Here we have a deadlock between SID 187 and 167 (SID 147 is just a victim - we are not really interested in it anymore as we know once 187/167 are clear it will acquire the enqueue). We need to take errorstacks from SID
167 and 187 (See below)

3) You reach a process which is on the CPU.

Run following to see which process is on CPU:  

SELECT event,p1,p2,p3 FROM v$session_wait WHERE sid=X AND wait_time=0

You will get 'no rows returned' which means this SID is on the CPU. The resources it holds cannot be released if
it is spinning on the CPU. As with (2), we are not interested in the victims, just the process on the CPU. Again we 
need an errorstack (See below)

Taking errorstacks

First gather all the SIDs you believe are causing the problem (not victims): 

SELECT process FROM v$session WHERE sid={sid});

Login as sysdba and run following:  

oradebug setospid { process}
oradebug dump errorstack 3 (Do this 3 times, one after the other}

This will write a trace file to user_dump_dest (the filename will have the spid in the name).
Upload the file to Oracle Support.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29953799/viewspace-1845428/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29953799/viewspace-1845428/