Sunday, November 30, 2014

Decoding P1/P2/P3 in for event 'enq: TX - row lock contention'

SQL> col wait_class for a20
SQL> col display_name for a35

SQL> select event#,wait_class,display_name
  2  from v$event_name where name='enq: TX - row lock contention';

    EVENT# WAIT_CLASS           DISPLAY_NAME
---------- -------------------- -----------------------------------
       267 Application          enq: TX - row lock contention

SQL> select count(event#),max(event#) from v$event_name;

COUNT(EVENT#) MAX(EVENT#)
------------- -----------
         1650        1649

SQL> select parameter1 from v$event_name
  2  where name='enq: TX - row lock contention';

PARAMETER1
----------------------------------------------------------------
name|mode

SQL> select parameter2 from v$event_name
  2  where name='enq: TX - row lock contention';

PARAMETER2
----------------------------------------------------------------
usn<<16 | slot

SQL> select parameter3 from v$event_name
  2  where name='enq: TX - row lock contention';

PARAMETER3
----------------------------------------------------------------
sequence


SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
            67

SQL> create table t1 (c1 number);
Table created.

SQL> insert into t1 values(1);
1 row created.

SQL> commit;
Commit complete.

SQL> update t1 set c1=2;

1 row updated.


oracle@solaris:~$ sqlplus donghua/password@pdb1

SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
            71
SQL> update t1 set c1=3;


SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
             1

SQL> select seq#,event from v$session where sid=71;

      SEQ# EVENT
---------- ----------------------------------------------------------------
        45 enq: TX - row lock contention

SQL> col p1text for a40
SQL> col p2text for a40
SQL> col p3text for a40

SQL> select p1text,p1,p1raw from v$session where sid=71;

P1TEXT                                           P1 P1RAW
---------------------------------------- ---------- ----------------
name|mode                                1415053318 0000000054580006

SQL> select p2text,p2,p2raw from v$session where sid=71; <—refer to xidusn and xidslot column in v$transaction

P2TEXT                                           P2 P2RAW
---------------------------------------- ---------- ----------------
usn<<16 | slot                               196619 000000000003000B

SQL> select p3text,p3,p3raw from v$session where sid=71; <—Refer to xidsqn in v$transaction

P3TEXT                                           P3 P3RAW
---------------------------------------- ---------- ----------------
sequence                                       2018 00000000000007E2

SQL> select * from v$lock where sid=71;

ADDR             KADDR                   SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK     CON_ID
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00000000BDE3BCA8 00000000BDE3BD20         71 AE        133          0          4          0       1339          0          3
00000000BDE3F250 00000000BDE3F2C8         71 TX     196619       2018          0          6       1270          0          0
FFFF80FFBDD386A0 FFFF80FFBDD38708         71 TM      92684          0          3          0       1270          0          3

SQL> select chr(bitand(p2, -16711680) / 16777215) || chr(bitand(p2, 16711680) / 65535) from v$session where sid=71;

CH
--
TX

SQL> select addr,xidusn,xidslot,xidsqn from v$transaction;

ADDR                 XIDUSN    XIDSLOT     XIDSQN
---------------- ---------- ---------- ----------
00000000BBB5F328          3         11       2018


SQL> col os_user for a10
SQL> col os_pid for a6
SQL> col oracle_user for a12
SQL> col lock_type for a15
SQL> col lock_held for a10
SQL> col lock_requested for a15
SQL> col status for a15
SQL> col owner for a10
SQL> col object_name for a11
SQL> set line 140
SQL> select     OS_USER_NAME os_user,
  2     PROCESS os_pid,
  3     ORACLE_USERNAME oracle_user,
  4     l.SID oracle_id,
  5     decode(TYPE,
  6             'MR', 'Media Recovery',
  7             'RT', 'Redo Thread',
  8             'UN', 'User Name',
  9             'TX', 'Transaction',
10             'TM', 'DML',
11             'UL', 'PL/SQL User Lock',
12             'DX', 'Distributed Xaction',
13             'CF', 'Control File',
14             'IS', 'Instance State',
15             'FS', 'File Set',
16             'IR', 'Instance Recovery',
17             'ST', 'Disk Space Transaction',
18             'TS', 'Temp Segment',
19             'IV', 'Library Cache Invalidation',
20             'LS', 'Log Start or Switch',
21             'RW', 'Row Wait',
22             'SQ', 'Sequence Number',
23             'TE', 'Extend Table',
24             'TT', 'Temp Table', type) lock_type,
25     decode(LMODE,
26             0, 'None',
27             1, 'Null',
28             2, 'Row-S (SS)',
29             3, 'Row-X (SX)',
30             4, 'Share',
31             5, 'S/Row-X (SSX)',
32             6, 'Exclusive', lmode) lock_held,
33     decode(REQUEST,
34             0, 'None',
35             1, 'Null',
36             2, 'Row-S (SS)',
37             3, 'Row-X (SX)',
38             4, 'Share',
39             5, 'S/Row-X (SSX)',
40             6, 'Exclusive', request) lock_requested,
41     decode(BLOCK,
42             0, 'Not Blocking',
43             1, 'Blocking',
44             2, 'Global', block) status,
45     OWNER,
46     OBJECT_NAME
47  from       v$locked_object lo,
48     dba_objects do,
49     v$lock l
50  where      lo.OBJECT_ID = do.OBJECT_ID
51  AND     l.SID = lo.SESSION_ID;

OS_USER    OS_PID ORACLE_USER   ORACLE_ID LOCK_TYPE       LOCK_HELD  LOCK_REQUESTED  STATUS          OWNER      OBJECT_NAME
---------- ------ ------------ ---------- --------------- ---------- --------------- --------------- ---------- -----------
oracle     2010   DONGHUA              71 DML             Row-X (SX) None            Not Blocking    DONGHUA    T1
oracle     2010   DONGHUA              71 Transaction     None       Exclusive       Not Blocking    DONGHUA    T1
oracle     2010   DONGHUA              71 AE              Share      None            Not Blocking    DONGHUA    T1
oracle     1959   DONGHUA              67 Transaction     Exclusive  None            Blocking        DONGHUA    T1
oracle     1959   DONGHUA              67 DML             Row-X (SX) None            Not Blocking    DONGHUA    T1
oracle     1959   DONGHUA              67 AE              Share      None            Not Blocking    DONGHUA    T1

6 rows selected.

SQL> col username for a10
SQL> col sql_text for a20

SQL> select     sn.USERNAME,
  2     m.SID,
  3     sn.SERIAL#,
  4     m.TYPE,
  5     decode(LMODE,
  6             0, 'None',
  7             1, 'Null',
  8             2, 'Row-S (SS)',
  9             3, 'Row-X (SX)',
10             4, 'Share',
11             5, 'S/Row-X (SSX)',
12             6, 'Exclusive') lock_type,
13     decode(REQUEST,
14             0, 'None',
15             1, 'Null',
16             2, 'Row-S (SS)',
17             3, 'Row-X (SX)',
18             4, 'Share',
19             5, 'S/Row-X (SSX)',
20             6, 'Exclusive') lock_requested,
21     m.ID1,
22     m.ID2,
23     t.SQL_TEXT
24  from       v$session sn,
25     v$lock m ,
26     v$sqltext t
27  where      t.ADDRESS = sn.SQL_ADDRESS
28  and        t.HASH_VALUE = sn.SQL_HASH_VALUE
29  and        ((sn.SID = m.SID and m.REQUEST != 0)
30  or         (sn.SID = m.SID and m.REQUEST = 0 and LMODE != 4 and (ID1, ID2) in
31          (select s.ID1, s.ID2
32           from      v$lock S
33           where     REQUEST != 0
34           and       s.ID1 = m.ID1
35           and       s.ID2 = m.ID2)))
36  order by sn.USERNAME, sn.SID, t.PIECE
37  /

USERNAME          SID    SERIAL# TY LOCK_TYPE       LOCK_REQUESTED         ID1        ID2 SQL_TEXT
---------- ---------- ---------- -- --------------- --------------- ---------- ---------- --------------------
DONGHUA            71      26079 TX None            Exclusive           196619       2018 update t1 set c1=3

SQL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME='V$LOCK';

VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------------------------------------------
select  ADDR , KADDR , SID , TYPE , ID1 , ID2 , LMODE , REQUEST , CTIME , BLOCK, CON_ID from GV$LOCK where inst_id = USERENV('Instance')

SQL> SELECT VIEW_DEFINITION FROM V$FIXED_VIEW_DEFINITION WHERE VIEW_NAME='GV$LOCK';

VIEW_DEFINITION
--------------------------------------------------------------------------------------------------------------------------------------------
select s.inst_id,l.laddr,l.kaddr,s.ksusenum,r.ksqrsidt,r.ksqrsid1,         r.ksqrsid2,l.lmode,l.request,l.ctime,decode(l.lmode,0,0,l.block),
         r.con_id  from v$_lock l, x$ksuse s, x$ksqrs r  where l.saddr=s.addr and        concat(USERENV('Instance'),l.raddr)=concat(r.inst_i
d,r.addr)