如何查看哪些SQL语句锁的表及如何分析锁表的原因

ALLSTARS_ORACLE

我的应用程序在登录时会出现无法登录的现象，经查找分析是许多数据表被锁了，但不是总是出现这一问题，请问：

1、有什么办法可以查出是哪些SQL语句锁了哪些表？

2、如何分析锁表的原因？

1. 如果是相关于enqueue lock的话，您可以利用下面的sql 试着去观察一下这个问题：

select
    mm.addr
  , mm.kaddr
  , mm.sid
  , row_number() over (partition by mm.type,mm.id1,mm.id2 order by mm.lmode desc ,mm.ctime desc) resource_row_number
  , mm.type
  , mm.id1
  , mm.id2
  , decode(mm.lmode, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') lmode
  , decode(mm.request, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') request
--  , mm.ctime
  , lpad(trunc(mm.ctime/60/60),3) || ' Hour '
    || lpad(to_char(trunc(mm.ctime/60) - trunc(mm.ctime/60/60) * 60,'fm09'), 2) || ' Min '
    || lpad(to_char(mm.ctime - trunc(mm.ctime/60)*60,'fm09'), 2) || ' Sec' ctime
  , case when mm.block = 1 and mm.lmode != 0 then 'holder'
         when mm.block = 0 and mm.request != 0 then 'waiter'
         else null end role
  , case when ee.blocking_session is not null then 'waiting for SID '|| ee.blocking_session else null end blocking_session
  , dd.sql_text sql_text
  , cc.event wait_event
from
    v$lock mm
  , v$session ee
  , v$sqlarea dd
  , v$session_wait cc
where mm.sid in (
                select nn.sid
                from (
                  select
                      tt.*
                    , count(1) over (partition by  tt.type,tt.id1,tt.id2) cnt
                    , max(tt.lmode) over (partition by tt.type,tt.id1,tt.id2) lmod_flag
                    , max(tt.request) over (partition by tt.type,tt.id1,tt.id2) request_flag
                  from v$lock tt ) nn
                where nn.cnt > 1
                  and nn.lmod_flag != 0
                  and nn.request_flag != 0)
  and mm.sid = ee.sid(+)
  and ee.sql_id = dd.sql_id(+)
  and mm.sid = cc.sid(+)
order by mm.type, mm.id1, mm.id2,mm.lmode desc ,mm.ctime desc;

2. 如果是相关于普通锁（比如说： library cache lock, library cache pin 等等）， 在问题发生的时候需要收集systemstate 和 hanganalyze dump。
因为分析这类dump 比较复杂，建议开个服务请求由工程师帮您分析。收集步骤如下：

对于单节点
-------------
A. export ORACLE_SID=PROD ## Replace PROD with the SID you want to trace

B. Using SQL*Plus connect as SYSDBA using the following command:
sqlplus -prelim / as sysdba
Do this 3 times in 3 separate windows, creating 3 sqlplus sessions (SQL1, SQL2,and SQL3)

C. In SQL1 execute the following:
SQL1> oradebug setmypid
SQL1> oradebug unlimit
SQL1> oradebug hanganalyze 3

D. In SQL2 execute the following:
SQL2> oradebug setmypid
SQL2> oradebug unlimit
SQL2> oradebug dump systemstate 258

E. Wait at least 2 minutes to give time to identify process state changes.

F. In SQL1 execute the following:
SQL1> oradebug hanganalyze 3

G. In SQL3 execute the following:
SQL3> oradebug setmypid
SQL3> oradebug unlimit
SQL3> oradebug dump systemstate 258

Verify that 3 new trace files were created in the user_dump_destination. Examine the
files to ensure that one file contains 2 hanganalyze dumps and each of the other two files
contains a systemstate dump.

对于RAC的环境
-----------------
A. Identify the SID you want to trace
export ORACLE_SID=PROD
Replace PROD with the SID you want to trace

B. Using SQL*Plus connect as "/ AS SYSDBA" using the following command:
sqlplus -prelim / as sysdba

C. Execute the following:
SQL> oradebug setmypid
SQL> oradebug unlimit
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all dump systemstate 258

D. Wait at least 2 minutes to give time to identify process state changes.

E. Execute the following:
SQL> oradebug -g all hanganalyze 3
SQL> oradebug -g all dump systemstate 258

Verify that the the "diag" background process trace file has been updated in the
background_dump_destination on each node. Examine each "diag" file to ensure that it
contains 2 hanganalyze dumps and a systemstate dump.

ALLSTARS_ORACLE

上面的信息是hanganalyze 的信息，并不是systemstate dump。 根据hanganalyze的信息， oracle 进程40 被进程oracle 进程33 阻塞了，
进程40 正在等待 ‘enq: TX - row lock contention’。因为进程40的call stack 中包括

kdusru updaul updexe

当前的sql 语句应该跟update 语句相关。 关于进程33，尽管没有current sql，这种现象是正常的。
因为造成持有相关资源的sql语句可能早已完成，因此看阻塞者 current sql是没有什么意义的，
也就是为什么之前的脚本里也没有给出current sql的原因。 关于如何查找,参照下面的例子：

skydl@R11202(26,7)>update t set id = 3 where id = 2;

1 row updated.

--hang
skydl@R11202(38,43)>delete from t where id =2;

select
    mm.addr
  , mm.kaddr
  , mm.sid
  , row_number() over (partition by mm.type,mm.id1,mm.id2 order by mm.lmode desc ,mm.ctime desc) resource_row_number
  , mm.type
  , mm.id1
  , mm.id2
  , decode(mm.lmode, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') lmode
  , decode(mm.request, 0, null, 1, 'N', 2, 'SS|RS', 3, 'SX|RX', 4, 'S', 5, 'SSX|SRX', 6, 'X') request
--  , mm.ctime
  , lpad(trunc(mm.ctime/60/60),3) || ' Hour '
    || lpad(to_char(trunc(mm.ctime/60) - trunc(mm.ctime/60/60) * 60,'fm09'), 2) || ' Min '
    || lpad(to_char(mm.ctime - trunc(mm.ctime/60)*60,'fm09'), 2) || ' Sec' ctime
  , case when mm.block = 1 and mm.lmode != 0 then 'holder'
         when mm.block = 0 and mm.request != 0 then 'waiter'
         else null end role
  , case when ee.blocking_session is not null then 'waiting for SID '|| ee.blocking_session else null end blocking_session
  , dd.sql_text sql_text
  , cc.event wait_event
from
    v$lock mm
  , v$session ee
  , v$sqlarea dd
  , v$session_wait cc
where mm.sid in (
                select nn.sid
                from (
                  select
                      tt.*
                    , count(1) over (partition by  tt.type,tt.id1,tt.id2) cnt
                    , max(tt.lmode) over (partition by tt.type,tt.id1,tt.id2) lmod_flag
                    , max(tt.request) over (partition by tt.type,tt.id1,tt.id2) request_flag
                  from v$lock tt ) nn
                where nn.cnt > 1
                  and nn.lmod_flag != 0
                  and nn.request_flag != 0)
  and mm.sid = ee.sid(+)
  and ee.sql_id = dd.sql_id(+)
  and mm.sid = cc.sid(+)
order by mm.type, mm.id1, mm.id2,mm.lmode desc ,mm.ctime desc;

ADDR             KADDR            SID  RESOURCE_ROW_NUMBER    TYPE ID1     ID2    LMODE   REQUEST CTIME                    ROLE   BLOCKING_SESSION     SQL_TEXT                   WAIT_EVENT                    
---------------- ---------------- ---- ---------------------- ---- ------- ------ ------- ------- ------------------------ ------ -------------------- -------------------------- ------------------------------
00000000774BD3F0 00000000774BD448 26   1                      AE   100     0      S                 0 Hour 34 Min 38 Sec                                                          SQL*Net message from client   
00000000774BE128 00000000774BE180 38   2                      AE   100     0      S                 0 Hour 04 Min 35 Sec          waiting for SID 26   delete from t where id =2  enq: TX - row lock contention
00007F841C96D078 00007F841C96D0D8 26   1                      TM   79763   0      SX|RX             0 Hour 02 Min 58 Sec                                                          SQL*Net message from client   
00007F841C96D078 00007F841C96D0D8 38   2                      TM   79763   0      SX|RX             0 Hour 02 Min 43 Sec          waiting for SID 26   delete from t where id =2  enq: TX - row lock contention
00000000774BDF68 00000000774BDFC0 26   1                      TO   66944   1      SX|RX             0 Hour 34 Min 25 Sec                                                          SQL*Net message from client   
000000007612C9F8 000000007612CA70 26   1                      TX   589827  4014   X                 0 Hour 02 Min 58 Sec   holder                                                 SQL*Net message from client   
00000000774BE208 00000000774BE260 38   2                      TX   589827  4014           X         0 Hour 02 Min 43 Sec   waiter waiting for SID 26   delete from t where id =2  enq: TX - row lock contention

7 rows selected

select owner,object_name from dba_objects where object_id = 79763;

OWNER                          OBJECT_NAME
------------------------------ -----------
SKYDL                          T         

select pid from v$process aa, v$session bb where aa.addr=bb.paddr and bb.sid = 26;
PID                    
------------------------
24

sys@R11202(40,105)>oradebug setmypid
Statement processed.
sys@R11202(40,105)>oradebug dump systemstate 258
Statement processed.
sys@R11202(40,105)>oradebug tracefile_name
/home/oracle/u01/app/diag/rdbms/r11202/R11202/trace/R11202_ora_29488.trc

PROCESS 24:
  ----------------------------------------
  SO: 0x77c95570, type: 2, owner: (nil), flag: INIT/-/-/0x00 if: 0x3 c: 0x3
   proc=0x77c95570, name=process, file=ksu.h LINE:12451, pg=0
  (process) Oracle pid:24, ser:2, calls cur/top: (nil)/0x778831a8
            flags : (0x0) -
            flags2: (0x0),  flags3: (0x0)
            intr error: 0, call error: 0, sess error: 0, txn error 0
            intr queue: empty
    ksudlp FALSE at location: 0
  (post info) last post received: 136 0 2
              last post received-location: ksl2.h LINE:2293 ID:kslpsr
              last process to post me: 77c87ef0 1 6
              last post sent: 0 0 26
              last post sent-location: ksa2.h LINE:282 ID:ksasnd
              last process posted by me: 77c87ef0 1 6
    (latch info) wait_event=0 bits=0
    Process Group: DEFAULT, pseudo proc: 0x77d23c88
    O/S info: user: oracle, term: UNKNOWN, ospid: 25782
    OSD pid info: Unix process pid: 25782, image: oracle@OraLinux01 (TNS V1-V3)
    Short stack dump:
ksedsts()+461<-ksdxfstk()+32<-ksdxcb()+1900<-sspuser()+112<-__sighandler()<-read()+14<-ntpfprd()+115<-nsbasic_brc()+338<-nsbrecv()+69<-nioqrc()+485<-__PGOSF36_opikndf2()+978<-opitsk()+850<-opiino()+966<-opiodr()+910<-opidrv()+570<-sou2o()+103<-opimai_real()+133<-ssthrdmain()+252<-main()+201<-__libc_start_main()+244<-_start()+36
    ----------------------------------------
    ----------------------------------------
    SO: 0x77fc7210, type: 4, owner: 0x77c95570, flag: INIT/-/-/0x00 if: 0x3 c: 0x3
     proc=0x77c95570, name=session, file=ksu.h LINE:12459, pg=0
    (session) sid: 26 ser: 7 trans: 0x7612c9f8, creator: 0x77c95570
              flags: (0x8000045) USR/- flags_idl: (0x0) -/-/-/-/-/-
              flags2: (0x40008) -/-
              DID: , short-term DID:
              txn branch: (nil)
              oct: 0, prv: 0, sql: (nil), psql: 0x706bc3d8, user: 85/SKYDL
    ksuxds FALSE at location: 0
    service name: SYS$USERS
    client details:
      O/S info: user: oracle, term: pts/1, ospid: 25779
      machine: OraLinux01 program: sqlplus@OraLinux01 (TNS V1-V3)
      application name: SQL*Plus, hash value=3669949024
    Current Wait Stack:
     0: waiting for 'SQL*Net message from client'
        driver id=0x62657100, #bytes=0x1, =0x0
        wait_id=526 seq_num=527 snap_id=1
        wait times: snap=12 min 46 sec, exc=12 min 46 sec, total=12 min 46 sec
        wait times: max=infinite, heur=12 min 46 sec
        wait counts: calls=0 os=0
        in_wait=1 iflags=0x1a0
    There are 1 sessions blocked by this session.
    Dumping one waiter:
      inst: 1, sid: 38, ser: 43
      wait event: 'enq: TX - row lock contention'
        p1: 'name|mode'=0x54580006
        p2: 'usn<<16 | slot'=0x90003
        p3: 'sequence'=0xfae
      row_wait_obj#: 79763, block#: 255, row#: 1, file# 6
      min_blocked_time: 748 secs, waiter_cache_ver: 890
     
      LibraryHandle:  Address=0x6eaef198 Hash=25c9aa46 LockMode=N PinMode=0 LoadLockMode=0 Status=VALD
        ObjectName:  Name=update t set id = 3 where id = 2