在看ORA-00060生成的trace檔案的時候，有以下的資訊：

waited for 'enq: TX - row lock contention', seq_num: 31888

p1: 'name|mode'=0x54580006

p2: 'usn<<16 | slot'=0x50017

p3: 'sequence'=0x17bdd57

time_waited: >= 2 sec (still in wait)

如何解讀呢？

針對Enqueue類的等待事件，P1、P2、P3的含義可以從v$event_name檢視中獲知。

比如針對上面的enq: TX - row lock contention時間，P1、P2、P3分別代表：

SQL> SELECT name, parameter1, parameter2, parameter3 from v$event_name where name='enq: TX - row lock contention';

NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ ------------------------------ ------------------------------ ------------------------------
enq: TX - row lock contention name|mode usn<<16 | slot sequence

P1代表name|mode。針對到此例，可以透過下面的換算從0x54580006得到具體可讀的值。

SQL> select to_number('54580006','xxxxxxxxxxxxxxxxxxxxx') from dual;

TO_NUMBER('54580006','XXXXXXXXXXXXXXXXXXXXX')

---------------------------------------------

1415053318

SQL> select chr(bitand(1415053318,-16777216)/16777215)||chr(bitand(1415053318,16711680)/65535) "name",bitand(1415053318,65535) "mode" from dual;

name mode

------------------------------ ----------

TX 6

P2代表usn<<16 | slot，即高16位表示事務的xidusn，剩下的表示事務的xidslot。

P3代表事務的sequence。

P2和P3的值會跟v$lock檢視中的ID1和ID2值相同，代表一個特定的事務，也可以和v$transaction中的資訊對應起來。

比如參考以下的案例：

SQL> select
  2        (select username from v$session where sid=a.sid) blocker,
  3         a.sid,
  4        ' is blocking ',
  5         (select username from v$session where sid=b.sid) blockee,
  6             b.sid
  7    from v$lock a, v$lock b
  8   where a.block = 1
  9     and b.request > 0
10     and a.id1 = b.id1
11     and a.id2 = b.id2;

BLOCKER                               SID 'ISBLOCKING'                     BLOCKEE                               SID
---------------------------- ---------- -------------------------------- ---------------------------- ----------
BEN                                1477  is blocking                     BEN                                 1513


SQL> col event format a30
SQL> select event,p1,p2,p3 from v$session_wait where sid=1513;

EVENT                                  P1         P2         P3
------------------------------ ---------- ---------- ----------
enq: TX - row lock contention  1415053318     655367       2981

SQL> select id1,id2 from v$Lock where sid=1513 and type='TX';

       ID1        ID2
---------- ----------
    655367       2981

SQL> select username,
       v$lock.sid,
  2    3         trunc(id1/power(2,16)) rbs,
  4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
  5         id2 seq,
  6         lmode,
  7         request
  8  from v$lock, v$session
  9  where v$lock.type = 'TX'
10    and v$lock.sid = v$session.sid
11    and v$Lock.sid = 1513;

USERNAME                              SID        RBS       SLOT        SEQ      LMODE    REQUEST
---------------------------- ---------- ---------- ---------- ---------- ---------- ----------
BEN                               1513         10          7       2981          0          6

SQL> select XIDUSN, XIDSLOT, XIDSQN
  2    from v$transaction;

    XIDUSN    XIDSLOT     XIDSQN
---------- ---------- ----------
        10          7       2981

About Enqueue:P1/P2/P3

相關文章