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)
good job
ReplyDeleteNice one ..
ReplyDeleteFinally I've found this query. Nice jobb!!!!
ReplyDelete