Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Oracle RAC blocking sessions

Oracle Tips by Burleson Consulting
May 26,  2009

Question:  How to I find Oracle blocking sessions in RAC?

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.

 

<  ges_blockers.sql

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.

 
   
Oracle Grid and Real Application Clusters

See working examples of Oracle Grid and RAC in the book Oracle Grid and Real Application Clusters.

Order directly from Rampant and save 30%. 
 



 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.