ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle中诊断阻塞的session

Oracle中诊断阻塞的session

原创 Linux操作系统 作者:zhouxianwang 时间:2013-11-18 16:43:26 0 删除 编辑


创建测试环境:

TEST@ PROD> create table t(id int constraint pk_id2 primary key,val int);

TEST@ PROD> select * from t;

        ID        VAL
---------- ----------
         1          1
         2          2


session 1:   
  查看SID:               
      
      SQL>  select distinct sid from v$mystat;
      
             SID
      ----------
             125       
      
      update 数据,但不要提交:
               
      TEST@ PROD> update t set val = 3 where id=1;
                                                   
      1 row updated.
        
      
  session 2:
                                                                                
      TEST@ PROD> select distinct sid from v$mystat;
                                              
             SID                                    
      ----------                                    
             141                 
         
         update 数据,操作被阻塞:
            
      TEST@ PROD> update t set val = 4 where id=1;  
      
      
      
 session 3(query):     
 
    但有活动事务对对象加锁的时候,会在v$locked_object视图中有记录如object_id,session_id等,
       通常被阻塞的session的XIDUSN,XIDSLOT,XIDSQN字段都为空  ,下图中session_id为141的是被阻塞的session.
 
      SQL>  select * from v$locked_object;                                                                                                 
                                                                                                                                           
          XIDUSN    XIDSLOT     XIDSQN  OBJECT_ID SESSION_ID ORACLE_USERNAME OS_USER_NAME         PROCESS                  LOCKED_MODE                                  
      ---------- ---------- ---------- ---------- ---------- --------------- -------------------- ------------------------ -----------
               5         21       1141      69926        125 TEST            oracle               32086                              3
               0          0          0      69926        141 TEST            oracle               31089                              3
               
        
        
 
               
     SQL> SELECT a.sid  blocking_sid ,b.sid blocked_sid FROM v$lock a ,v$lock b                    
       2      WHERE b.id1=a.id1 AND a.id2=b.id2                                
       3       AND a.sid<>b.sid                                                
       4      AND b.request<>0;                                                
                                                                          
     BLOCKING_SID BLOCKED_SID                                                  
     ------------ -----------                                                  
              125         141     
        
        
        在通过v$session可以查到session相关的信息,被阻塞的status一般为ACTIVE,还可以通过sql_address联合v$sql找到被阻塞的SQL语句.      
     
     SQL>  select sid,serial#,status,sql_address from v$session where sid in(125,141);                                                      
                                                                                 
            SID    SERIAL# STATUS   SQL_ADDRESS                                       
     ---------- ---------- -------- ----------------                                  
            125         27 INACTIVE 00                                                
            141         37 ACTIVE   00000000B8DFDF38    
            
            
    查看被block 的SQL        
    SQL>  select sql_text from V$sql where address='00000000B8DFDF38';                                                              
                                                                                          
    SQL_TEXT                                                                                  
    ------------------------------------------------------------------------------------------
    update t set val = 4 where id=1                                                          

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

下一篇: iscsi挂载和删除
请登录后发表评论 登录
全部评论

注册时间:2012-06-04

  • 博文量
    138
  • 访问量
    452229