Enqueue Waits : Locks
Wait Tree - Locks
Buffer Cache Disk I/O Enqueue Waits Library Cache Redo SQL*Net Undo TM 3 Row Lock TX 6 Row Lock TX 4 ITL Lock TX 4 PK/FK HW Lock ST Lock TS Lock
#.2
Copyright 2006 Kyle Hailey
#.3
Enqueue Types
DDL Locks Data Dictionary
Row Cache Library Cache Locks
DML Locks Data Locks
Row locks Table Locks
Internal Structure Locks
High Water Buffer Header Sequence Cache Space Transaction Temporary Space
Copyright 2006 Kyle Hailey
#.4
Statspack
Top 5 Timed Events Top 5 Timed Events ~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~ Avg %Total Avg %Total wait wait Call Call Event Waits Time (s) Event Waits Time (s) (ms) (ms) Time Time -------------------------- ------------ ----------- ------ ------------------------------- ------------ ----------- ------ -----Enqueue Enqueue CPU time CPU time db file sequential read db file sequential read control file sequential read control file sequential read log file switch completion log file switch completion 42 42 165 165 214 214 2 2 126 126 4 4 1 1 0 0 0 0 3000 3000 4 4 1 1 40 40 96.5 96.5 2.8 2.8 .4 .4 .1 .1 .1 .1
Need more info from v$session_wait
Copyright 2006 Kyle Hailey
#.5
v$session_wait
SQL> select event, p1,p2,p3 from v$session_wait; SQL> select event, p1,p2,p3 from v$session_wait; EVENT EVENT enqueue enqueue P1 P1 1415053318 1415053318 P2 P2 589855 589855 P3 P3 1592 1592 ----------------- -------------- ---------- -------------------------- -------------- ---------- ----------
What can we do with this info? Note: v$session_wait is for current waits. Need ASH or some similar data source for historic analysis
Copyright 2006 Kyle Hailey
#.6
Enqueue : Args
P1 = Type | mode P2 = ID1 , depends on P1 P3 = ID2 , depends on P1
Copyright 2006 Kyle Hailey
#.7
Translating P1 to Lock and Mode
SQL> select p1, p1raw from v$session_wait where sid=151; P1 P1RAW ---------- -------1415053318 54580006
Type Mode
Copyright 2006 Kyle Hailey
#.8
Translating P1 to Lock and Mode
P1RAW -------54580006
Type: 5458
Hex Decimal 54 = 84 58 = 88 ASCII = T = X
Mode: 0006
Lock = TX 6
Copyright 2006 Kyle Hailey
#.9
Translating P1 to Lock and Mode
column Type format a4 column Mode format a4 select sid, chr(to_number(substr(p1raw,1,1)) * 16 + to_number(substr(p1raw,2,1))) || chr(to_number(substr(p1raw,3,1)) * 16 + to_number(substr(p1raw,4,1))) Type, substr(p1raw,8,1) as "Mode" from v$session_wait where name=enqueue; SID TYPE Mode --- ---- ---151 TX 06
Copyright 2006 Kyle Hailey
#.10
Translating p1 to Lock and Mode
SELECT chr(bitand(p1,-16777216)/16777215)|| chr(bitand(p1, 16711680)/65535) "Lock", mod(p1,16) as "mode" FROM V$SESSION_WAIT Where sid=151 / bitand(p1, 65536) "Mode"
Copyright 2006 Kyle Hailey
Translating P1 to Lock and Mode
select sid, select sid, event, event, chr(bitand(P1,-16777216)/16777215)|| chr(bitand(P1,-16777216)/16777215)|| chr(bitand(P1,16711680)/65535) as "Type", chr(bitand(P1,16711680)/65535) as "Type", mod(p1,16) as "mode" mod(p1,16) as "mode" from v$session_wait from v$session_wait where event = 'enqueue; where event = 'enqueue; SID SID ----240 240 EVENT EVENT ------------enqueue enqueue Ty Ty --TX TX mode mode ------6 6 P2 P3 P2 P3 ------- ---------- ---2686995 433 2686995 433
#.11
Copyright 2006 Kyle Hailey
#.12
Modes
1 2 3 4 5 6 Null SS SX S SSX X Null Sub share Sub exclusive Share Share/sub exclusive Exclusive
Copyright 2006 Kyle Hailey
#.13
Types
CF Control File HW High Water SQ - Sequence ST - Space Transaction TM - DML TS Temporary Segment / Table Space TX Transaction UL DBMS_LOCK UN User Named US Undo Segment
Copyright 2006 Kyle Hailey
Looking at v$lock
select * from v$lock where type in ('TX', 'TM'); select * from v$lock where type in ('TX', 'TM');
#.14
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK --- -- ---------- ---------- ----- ------- ----- ------- -- ---------- ---------- ----- ------- ----- ----151 TX 589855 1592 0 6 4049 0 151 TX 589855 1592 0 6 4049 0 135 TM 53737 0 3 0 4058 0 135 TM 53737 0 3 0 4058 0 151 TM 53737 0 3 0 4049 0 151 TM 53737 0 3 0 4049 0 135 TX 589855 1592 6 0 4058 1 135 TX 589855 1592 6 0 4058 1
TX TM
ID1 = RBS seg# | RBS slot # ID2 = rbs wrap # ID1 = object id ID2 = 0
Copyright 2006 Kyle Hailey
#.15
ID1 and ID2 Examples
Lock = TX
ID1
= RBS seg# | RBS slot # ID2 = rbs wrap #
Lock = TM
ID1
= object id ID2 = 0
ID1 and ID2 meanings can be determined from v$event_name in 10g
Copyright 2006 Kyle Hailey
ID1 and ID2 Definitions
column parameter1 format a15 column parameter2 format a15 column parameter3 format a15 column lock format a8 Select substr(name,1,7) as "lock",parameter1,parameter2,parameter3 from v$event_name where name like 'enq%'
LOCK ------enq: CF enq: HW enq: SQ enq: ST enq: TM enq: TS enq: TX Parmeter1 --------name|mode name|mode name|mode name|mode name|mode name|mode name|mode Parmeter2(ID1) ------------0 table space # object # 0 object # tablespace ID usn<<16 | slot Parameter3(ID2) --------------operation block 0 0 table/partition dba sequence
#.16
Copyright 2006 Kyle Hailey
Enqueues Decoded in 10g
10gR2 waits distinguish 208 enqueues
enq: DB - contention Administrative enq: HW - contention Configuration enq: KO - fast object checkpoint Application enq: PW - flush prewarm buffers Application enq: RO - contention Application enq: RO - fast object reuse Application enq: SQ - contention Configuration enq: SS - contention Configuration enq: ST - contention Configuration enq: TM - contention Application enq: TW - contention Administrative enq: TX - allocate ITL entry Configuration enq: TX - index contention Concurrency enq: TX - row lock contention Application enq: UL - contention Application Copyright 2006 Kyle Hailey enq: ZG - contention Administrative
#.17
Enqueue Solutions
SQ Sequence Lock
logon/logoff problem
#.18
TX - mode 6
application problem
Look at what application is doing Find SQL Look at locked data
TX - mode 4
probably ITL problem find the object and SQL
HW High Water
Look at object and SQL use LMT, freelists, pre-allocate extents,
ST - Space Transaction
only one per database used for space allocations uet, fet Find object use LMT
UL - User Lock
find out what application is doing
Copyright 2006 Kyle Hailey
Enqueue Data Needed
If highest wait time is Enqueue ,
Find
#.19
out the kind of Enqueue and tune it
To tune enqueues we need one of the following to determine the type of enqueue
ASH
Data v$session_wait data Sql Trace with waits
Copyright 2006 Kyle Hailey
#.20
Blockers and Waiters
SQL> select * from dba_blockers;
HOLDING_SESSION --------------10
SQL> select * from dba_waiters;
WAITING HOLDING LOCK_TYPE MODE_HELD MODE_REQUESTE LOCK_ID1 LOCK_ID2 ------- ------- ---------- ---------- ------------- -------- -------14 10 Transaction Exclusive Exclusive 458765 2379
Copyright 2006 Kyle Hailey
V$session
select sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, lockwait from v$session;
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOCKWAIT --- ------------- -------------- --------------- ------------- -------141 53651 3 53980 0 143 -1 0 0 0 144 -1 0 0 0 149 -1 0 0 0 151 53737 4 428 0 410343AC
#.21
10g Lockwait not null is blocker Pre-10g Lockwait not null is the waiter
Copyright 2006 Kyle Hailey
#.22
Enqueue : TX 6 Example
Exclusive Row Level Lock User 1 SQL> delete from emp where empno = 7934; SQL> update emp set sal=2000 Where empno = 7934; User 2
Copyright 2006 Kyle Hailey
#.23
Enqueue : TX 6 v$session_wait
SQL> select event, p1,p2,p3 from v$session_wait; SQL> select event, p1,p2,p3 from v$session_wait; EVENT EVENT enqueue enqueue P1 P1 1415053318 1415053318 P2 P2 589855 589855 P3 P3 1592 1592 ----------------- -------------- ---------- -------------------------- -------------- ---------- ----------
What can we do with this info?
Copyright 2006 Kyle Hailey
Enqueue : TX 6 Type and Mode
select sid, select sid, event, event, chr(bitand(P1,-16777216)/16777215)|| chr(bitand(P1,-16777216)/16777215)|| chr(bitand(P1,16711680)/65535) as "Type", chr(bitand(P1,16711680)/65535) as "Type", mod(p1,16) as "mode" mod(p1,16) as "mode" from v$session_wait from v$session_wait where event = 'enqueue; where event = 'enqueue; SID SID ----240 240 EVENT EVENT ------------enqueue enqueue Ty Ty --TX TX mode mode ------6 6 P2 P3 P2 P3 ------- ---------- ---2686995 433 2686995 433
#.24
Copyright 2006 Kyle Hailey
Enqueue : TX 6 v$lock
select * from v$lock where type in ('TX', 'TM'); select * from v$lock where type in ('TX', 'TM');
#.25
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK --- -- ---------- ---------- ----- ------- ----- ------- -- ---------- ---------- ----- ------- ----- ----151 TX 589855 1592 0 6 4049 0 151 TX 589855 1592 0 6 4049 0 135 TM 53737 0 3 0 4058 0 135 TM 53737 0 3 0 4058 0 151 TM 53737 0 3 0 4049 0 151 TM 53737 0 3 0 4049 0 135 TX 589855 1592 6 0 4058 1 135 TX 589855 1592 6 0 4058 1
TX TM
ID1 = RBS seg# | RBS slot # ID2 = rbs wrap # ID1 = object id ID2 = 0
Copyright 2006 Kyle Hailey
#.26
Enqueue : TX 6 Blockers and Waiters
SQL> select * from dba_blockers;
HOLDING_SESSION --------------10
SQL> select * from dba_waiters;
WAITING HOLDING LOCK_TYPE MODE_HELD MODE_REQUESTE LOCK_ID1 LOCK_ID2 ------- ------- ---------- ---------- ------------- -------- -------14 10 Transaction Exclusive Exclusive 458765 2379
Copyright 2006 Kyle Hailey
Enqueue : TX 6 V$session
select sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, lockwait from v$session;
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOCKWAIT --- ------------- -------------- --------------- ------------- -------141 53651 3 53980 0 143 -1 0 0 0 144 -1 0 0 0 149 -1 0 0 0 151 53737 4 428 0 410343AC
#.27
10g Lockwait not null is blocker Pre-10g Lockwait not null is the waiter
Copyright 2006 Kyle Hailey
#.28
Enqueue : TX 4
Index on p(id) User 1 SQL> insert into p values(3); SQL> insert into p values(3); User 2
Copyright 2006 Kyle Hailey
Enqueue : TX 4
select sid, select sid, event, event, chr(bitand(P1,-16777216)/16777215)|| chr(bitand(P1,-16777216)/16777215)|| chr(bitand(P1,16711680)/65535) as "Type", chr(bitand(P1,16711680)/65535) as "Type", mod(p1,16) as "mode" mod(p1,16) as "mode" from v$session_wait from v$session_wait where event = 'enqueue; where event = 'enqueue; SID SID ----240 240 EVENT EVENT ------------enqueue enqueue Ty Ty --TX TX mode mode ------4 4 P2 P3 P2 P3 ------- ---------- ---2686995 433 2686995 433
#.29
Copyright 2006 Kyle Hailey
#.30
Enqueue : TX 4
SQL> select sid, type, id1, id2, lmode , request from v$lock where type in ('TX', 'TM'); SID ---------139 146 146 139 139 139 146 TY ID1 ID2 LMODE REQUEST -- ---------- ---------- ---------- ---------TX 327689 1901 0 4 TM 55166 0 3 0 TM 55168 0 2 0 TM 55166 0 3 0 TM 55168 0 2 0 TX 720914 168 6 0 TX 327689 1901 6 0
Copyright 2006 Kyle Hailey
#.31
Enqueue : TX 4 - difficult
Difficult uses modifying different data ITL Unique Key Bitmap Index Rare Read only Tablespace Free Lists Two phase commit
Copyright 2006 Kyle Hailey
#.32
Enqueue : TX 4 ITL
Data Block Data Block Header Header ITL Transaction 1 Info Transaction 2 Info
Data
Copyright 2006 Kyle Hailey
#.33
Enqueue : TX 4 ITL
Data Block Data Block Header Header Transaction 1 Transaction 2 Row 3 Row 2 Data Row 1 Transaction 3
Copyright 2006 Kyle Hailey
#.34
Enqueue : TX 4 ITL
SQL> select sid, type, id1, id2, lmode , request SQL> select sid, type, id1, id2, lmode , request from v$lock where type in ('TX', 'TM'); from v$lock where type in ('TX', 'TM'); SID TY ID1 ID2 LMODE REQUEST SID TY ID1 ID2 LMODE REQUEST ---------- -- ---------- ---------- ---------- ------------------- -- ---------- ---------- ---------- ---------148 TX 148 TX 135 TM 135 TM 151 TM 151 TM 148 TM 148 TM 135 TX 135 TX 151 TX 151 TX 65559 65559 54557 54557 54557 54557 54557 54557 524312 524312 65559 65559 1284 1284 0 0 0 0 0 0 1592 1592 1284 1284 0 0 3 3 3 3 3 3 6 6 6 6 4 4 0 0 0 0 0 0 0 0 0 0
Copyright 2006 Kyle Hailey
#.35
Enqueue : TX 4 Unique Key
Exclusive Row Level Lock User 1 create table parent ( id number primary key); create table child ( id number references parent, name varchar2(20)); insert into parent values (1); insert into child values (1,2); commit; delete from parent;
Copyright 2006 Kyle Hailey
User 2
insert into child values (1,2);
#.36
Enqueue : TX 4 Unique Key
PK ID Parent ID Value Child ID Name
Session 1: Insert into Child ID=1 Session 2: Delete from Parent ID=2 : would require a FTS of child still not atomic, solution lock child Enqueue TX 4
Copyright 2006 Kyle Hailey
#.37
Enqueue : TX 4 - Unique Key
SQL> select sid, type, id1, id2, lmode , request SQL> select sid, type, id1, id2, lmode , request from v$lock where type in ('TX', 'TM'); from v$lock where type in ('TX', 'TM'); SID TY ID1 ID2 LMODE REQUEST SID TY ID1 ID2 LMODE REQUEST ---------- -- ---------- ---------- ---------- ------------------ -- ---------- ---------- ---------- --------151 TM 54548 0 2 0 151 TM 54548 0 2 0 151 TM 54550 0 3 0 151 TM 54550 0 3 0 151 TX 524306 1590 6 0 151 TX 524306 1590 6 0 135 TM 54548 0 3 0 135 TM 54548 0 3 0 135 TM 54550 0 0 4 135 TM 54550 0 0 4
Copyright 2006 Kyle Hailey
PK
Enqueue : TX 4 Unique Key Solution
Parent ID Index ID Child ID Name Value
#.38
ID
Session 1: Insert into Child ID=1 Session 2: Delete from Parent ID=2 OK can Copyright 2006 Kyle Haileyin the child index verify quickly
#.39
Enqueue : TX 4 V$session
select sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#, lockwait from v$session;
SID ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOCKWAIT ----- ------------- -------------- --------------- ------------- -------135 -1 0 0 0 40B4EE1C 137 -1 0 0 0 138 -1 0 0 0 139 -1 0 0 0 140 -1 0 0 0 141 53651 3 53980 0
10g Lockwait not null is blocker Pre-10g Lockwait not null is the waiter
Copyright 2006 Kyle Hailey
#.40
Enqueue : TX 4 Bitmap Indexes
Two sessions update keys in same key range
Copyright 2006 Kyle Hailey
#.41
Enqueue : ST
Space Transaction Lock Used in Dictionary Managed Tables Solution
Got
to Locally Managed Tablespaces
Copyright 2006 Kyle Hailey
#.42
Enqueue : HW
Table
Header
Data
High Water Mark Empty
Copyright 2006 Kyle Hailey
Enqueue : HW
select sid, select sid, event, event, chr(bitand(P1,-16777216)/16777215)|| chr(bitand(P1,-16777216)/16777215)|| chr(bitand(P1,16711680)/65535) as "Type", chr(bitand(P1,16711680)/65535) as "Type", mod(p1,16) as "mode" mod(p1,16) as "mode" from v$session_wait from v$session_wait where event = 'enqueue; where event = 'enqueue; SID SID ----240 240 EVENT EVENT ------------enqueue enqueue Ty Ty --HW HW mode P2 P3 mode P2 P3 ---- ---- ---------- ---- ------6 4 16777715 6 4 16777715
#.43
Copyright 2006 Kyle Hailey
#.44
Enqueue : HW
Use Freelists
Cause
multiple jumps in High Water Mark
Pre-Allocate Extents
Alter
table XXXX allocate extent;
Hidden Parameter
bump_highwater_mark_count
ASSM
Automatic segment space management
Copyright 2006 Kyle Hailey
