Transcript Document

Enqueue Waits : Locks
Kyle Hailey
http://perfvision.com/ftp/emea2010
[email protected]
#.1
Locks Covered in this Section
Part I : Intro
 Lock Name(type) and Mode
 Finding waiter and blocker
 Finding Object
Part II : User
 TM – table modification
 TX – Transaction locks
 UL – user lock
Part III : Internal
 CI – Cross Instance
 HW – High Water
 RO – Reusable Object
Copyright 2006 Kyle Hailey
#.2
Part I : Intro
To Solve we need:
1. Waiter
2. Blocker
3. Lock Type


type
mode
4. Object blocking on
Missing : blocking SQL
 Possibly
with log miner
Copyright 2006 Kyle Hailey
#.3
Solving – Data Sources
 V$active_session_history
or
 In “real time” can also use
 v$lock
 v$session
(v$session_wait)
 dba_blockers
 dba_waiters
 ?/rdbms/admin/utllockt.sql
 http://www.evdbt.com/enqwaits.sql
Copyright 2006 Kyle Hailey
#.4
v$active_session_history
Fields in ASH for lock analysis and solution:
 Waiter
 SESSION_ID
 SESSION_SERIAL#
 USER_ID
 Object
 CURRENT_OBJ#
 CURRENT_FILE#
 CURRENT_BLOCK#
SQL Waiting
SQL_ID
 Blocker
 BLOCKING_SESSION
 BLOCKING_SESSION_STATUS
 BLOCKING_SESSION_SERIAL#
 Lock Type and Mode
 Event = Type (name)
 P1
= Type | Mode
Missing: SQL Blocking not reliably
possible, Maybe by dumping REDO
Copyright 2006 Kyle Hailey
#.5
Lock Name and Mode
Select parameter1 from v$event_name where name=‘enqueue’;
Parameter1
---------Name|mode
Select p1, p1raw from v$session where event like 'enq%';
P1
P1RAW
---------- -------1415053318 54580006
Name: 5458
Hex
Decimal
54 =
84
58 =
88
Mode: 0006
ASCII
=
“T”
=
“X”
Copyright 2006 Kyle Hailey
Lock = TX 6
#.6
Type and Mode
SELECT
chr(bitand(p1,-16777216)/16777215)||
chr(bitand(p1, 16711680)/65535) Type,
mod(p1,16) lmode
from v$session_wait
where event=‘enqueue’;
TY
LMODE
-- ---------TX
6
#.7
Lock Names (types)
 9i
 One
Wait : “enqueue”
 10g
 208
enqueue waits
 Specific to each type of enqueue
enq:
enq:
enq:
enq:
enq:
enq:
enq:
enq:
enq:
enq:
HW
SQ
SS
ST
TM
TW
TX
TX
TX
TX
–
contention
contention
contention
contention
contention
contention
allocate ITL entry
index contention
row lock contention
contention
Copyright 2006 Kyle Hailey
Configuration
Configuration
Configuration
Configuration
Application
Administrative
Configuration
Concurrency
Application
Application
#.8
Lock Modes
# Type
--- ------1 Null
2 SS
3 SX
4 S
5 SSX
6 X
Name
--------------------------Null
Sub share
Sub exclusive
Share
Share/sub exclusive
Exclusive
Copyright 2006 Kyle Hailey
#.9
P1 = name | mode
 P1 (parameter1) same for all locks
select distinct parameter1 from v$event_name
where name like 'enq:%'
PARAMETER1
---------name|mode
select
event,
mod(p1,16) as "mode"
from v$active_session_history
where event like 'enq:%‘;
EVENT
mode
----------------------------- ---enq: TX - allocate ITL entry
4
enq: TX - row lock contention
6
enq: TX - row lock contention
4
Copyright 2006 Kyle Hailey
#.10
OEM 10g
if P1 = 1415053318
then mode = 6
Then it is a data block
row lock
Copyright 2006 Kyle Hailey
#.11
Querying ASH
select
substr(event,0,20)
lock_name,
ash.session_id
waiter,
mod(ash.p1,16)
lmode,
ash.p2
p2,
ash.p3
p3,
o.object_name
object,
o.object_type
otype,
CURRENT_FILE#
filen,
CURRENT_BLOCK#
blockn,
ash.SQL_ID
waiting_sql,
BLOCKING_SESSION
blocker
--,ash.xid
from
v$active_session_history ash,
all_objects o
where
event like 'enq: %'
and o.object_id (+)= ash.CURRENT_OBJ#
/
Copyright 2006 Kyle Hailey
#.12
Part II : User Locks
 TX – Transaction Lock
 Mode
6: Modifying same row
 Mode 4: several reasons
 TM – Table Modification
 Mode
4: Unindexed Foreign Key
 UL – User Lock
Copyright 2006 Kyle Hailey
#.13
TX Lock
Session B
update toto set name =
‘ADAMS’ where id = 1;
Wait for Tx
To commit
Undo
Segment
Header
Undo
Segment
Table Toto
DataBlock
Block
Data
Header
Header
Transaction 1
Session a
update toto set name =
‘SMITH’ where id = 1;
Row 1
Copyright 2006 Kyle Hailey
Delete from toto where id = 2;
Delete from toto where id = 9;
#.14
Transaction Locks (TX)
TX = Transaction = Wait on UNDO
Mode 6 (exclusive)

modification of a row lock
Mode 4 (share)
 Index
block spilt
 Unique Index Key enforcement
 Foreign key enforcement
 ITL space waits
 Bitmap chunk conflicts
 Alter tablespace … read only;
 Free Lists slot waits
 Possible with two phase commit
Copyright 2006 Kyle Hailey
#.15
Transaction Locks (TX)
Mode 4, new Events:
1. enq: TX - allocate ITL entry

Wait on an ITL slot

Index block split
2. enq: TX - index contention
3. enq: TX - row lock contention
2.
Mode 6 – classic row lock
Mode 4 - pk violation, fk violation, bitmap chunk wait

Wait for a data file extension, Alter tbs read only
1.
4. enq: TX – contention
Copyright 2006 Kyle Hailey
#.16
enq: TX - row lock contention
Mode 6, row in data block
User 1
SQL> delete from toto
where id = 1;
User 2
SQL> delete from toto
where id =1;
Table
ID
Value
1
foo
Copyright 2006 Kyle Hailey
#.17
enq: TX - row lock contention
if P1 = 1415053318
then mode = 6
Then it is a data block
row lock
Copyright 2006 Kyle Hailey
#.18
TX – Mode 4
if P1 = 1415053316
then mode = 4
Not same data but conflicts
Copyright 2006 Kyle Hailey
#.19
enq: TX - row lock contention
Mode 4, happens for 3 reasons
1. Unique key contention
2. Foreign Key contention
3. Bitmap index contention
(others?)
Copyright 2006 Kyle Hailey
#.20
1. enq: TX - row lock contention
Mode 4 , unique index
User 2
User 1
create table p(n number);
create unique index p_i on p(n);
insert into p values(2);
insert into p values(2);
PK
Table
ID
2?
ID Value
2?
Copyright 2006 Kyle Hailey
#.21
2. enq: TX - row lock contention
Mode 4, foreign key
User 1
create table parent (
id number primary key);
create table child (
id number references parent,
name varchar2(20));
insert into parent values (2);
User 2
insert into child values (2,88);
PK
ID
2?
Child
ID Name
2
Parent
ID Value
2?
Copyright 2006 Kyle Hailey
#.22
3. enq: TX - row lock contention
 Mode 4
 Bitmaps are compressed
 Changes to the same bitmap cause locks
Value
1
Start End
Bitmap
Rowid Rowid
01010000111000011100001100
000.000.0000
000.000.000
2
01010000111000011100001100
000.000.0000
000.000.000
Copyright 2006 Kyle Hailey
#.23
3. enq: TX - row lock contention
Session 1
create table t1 (
n1
number(4),
n2
number(4));
insert into t1
select 1, rownum
from all_objects
where rownum <= 400;
commit;
create bitmap index i1 on t1(n1);
Different rows but
same key value
update t1 set n1 = 2
where n2 = 12;
Session 2
update t1 set n1 = 2
where n2 = 13;
Copyright 2006 Kyle Hailey
#.24
3. enq: TX - row lock contention
 Bitmaps are compressed
 Changes to the same bitmap chunk cause
locks
Value
1
Start End
Bitmap
Rowid Rowid
01010000111000011100001100
200.0
204.7
1
01010000111000011100001100
205.0
210.3
2
01010000111000011100001100
200.0
block
205.6
row
Copyright 2006 Kyle Hailey
#.25
3. enq: TX - row lock contention
Value
1
Start End
Bitmap
Rowid Rowid
01010000111000011100001100
200.0
204.7
2
01010000111000011100001100
205.0
210.3
3
01010000111000011100001100
200.0
205.6
Session 1
Session 2
Update id=12
set value 2
Update id=13
set value 2
Copyright 2006 Kyle Hailey
#.26
Summary: TX 4 from ASH
uniq index
ST
EVENT
----- ---------------------10:39 enq: TX - row lock c
10:39 enq: TX - row lock c
10:39 enq: TX - row lock c
10:39 enq: TX - row lock c
SID LM
P2
P3 OBJ
OTYPE FN BLOCKN SQL_ID
--- --- ------ ---- ----- ----- --- ------ -141
4 655406 6672 -1
0
0 bjvx94vnxtxgv
141
4 655406 6672 -1
0
0 bjvx94vnxtxgv
141
4 655406 6672 -1
0
0 bjvx94vnxtxgv
141
4 655406 6672 -1
0
0 bjvx94vnxtxgv
BSID
FK (10.2.0.3)
ST
EVENT
----- ---------------------10:41 enq: TX - row lock c
10:41 enq: TX - row lock c
10:41 enq: TX - row lock c
SID LM
P2
P3 OBJ
--- --- ------ ---- ----144
4 179681 7074 CHILD
144
4 179681 7074 CHILD
144
4 179681 7074 CHILD
OTYPE FN BLOCKN SQL_ID
----- --- ------ -TABLE
1 60954 ahm7c9rupbz9r
TABLE
1 60954 ahm7c9rupbz9r
TABLE
1 60954 ahm7c9rupbz9r
BSID
bitmap
ST
EVENT
----- ---------------------10:41 enq: TX - row lock c
10:41 enq: TX - row lock c
10:41 enq: TX - row lock c
10:41 enq: TX - row lock c
SID LM
P2
P3 OBJ
--- --- ------ ---- ----143
4 966081 4598 I1
143
4 966081 4598 I1
143
4 966081 4598 I1
143
4 966081 4598 I1
OTYPE FN BLOCKN SQL_ID
----- --- ------ -INDEX
0
0 azav296xxqcjx
INDEX
0
0 azav296xxqcjx
INDEX
0
0 azav296xxqcjx
INDEX
0
0 azav296xxqcjx
BSID
Copyright 2006 Kyle Hailey
158
158
158
158
1
1
1
144
144
144
144
#.27
enq: TX - allocate ITL entry
Transaction 1 Info
DataBlock
Block
Data
Header
Header
Transaction 2 Info
ITL
Data
Copyright 2006 Kyle Hailey
#.28
enq: TX - allocate ITL entry
DataBlock
Block
Data
Header
Header
Transaction 1
Transaction 2
Transaction 3
Row 3
Row 2
Data
Row 1
Copyright 2006 Kyle Hailey
#.29
enq: TX - allocate ITL entry
create table itl (
id number,
data varchar2(20)
)
pctfree 0
initrans 1
;
insert into itl select rownum,'a' from all_objects
where rownum < 2000;
commit;
session
session
session
session
session
1:
2:
3:
4:
5:
update
update
update
update
update
itl
itl
itl
itl
itl
set
set
set
set
set
data=data
data=data
data=data
data=data
data=data
Copyright 2006 Kyle Hailey
where
where
where
where
where
id=1;
id=2;
id=3;
id=4;
id=5;
#.30
enq: TX - contention
1. Altering tablespace read only with open transaction
Example
 Session 1 – start transaction, don’t commit
 Session 2 – alter tablespace read only
2. Data File Extension – waiter waiting for another session to extend
file
3. Index Block Split – waiter waiting for another session to split the
block
Copyright 2006 Kyle Hailey
#.31
TX Further Investigation
select
event, sql_id,
CURRENT_OBJ# || ' ' || name obj
,CURRENT_FILE# file#
,CURRENT_BLOCK# block#
from v$active_session_history ash,
obj$ o
where
event like 'enq: TX%'
and o.obj# (+)= ash.current_obj#
order by sample_time
EVENT
-----------------------------enq: TX - row lock contention
enq: TX - row lock contention
enq: TX - row lock contention
enq: TX - row lock contention
enq: TX - row lock contention
SQL_ID
------------ahm7c9rupbz9r
bjvx94vnxtxgv
ahm7c9rupbz9r
bjvx94vnxtxgv
ahm7c9rupbz9r
Copyright 2006 Kyle Hailey
OBJ
FILE# BLOCK#
--------- --- -----53363 FOO 1 123586
53363 FOO 1 123586
53363 FOO 1 123586
53363 FOO 1 123586
53363 FOO 1 123586
#.32
TX Further Investigation
Who is the blocker:
V$active_session_history :
BLOCKING_SESSION
BLOCKING_SESSION_STATUS
BLOCKING_SESSION_SERIAL#
No Guarentee of finding blocker SQL
Copyright 2006 Kyle Hailey
#.33
enq: TM - contention
 TX locks have a corresponding TM lock
 TM locks the structure from change
 Parameter1
LOCK
------enq: TM
= object id
Parmeter1
--------name|mode
Parmeter2(ID1)
------------object #
Copyright 2006 Kyle Hailey
Parameter3(ID2)
--------------table/partition
#.34
enq: TM - contention
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 parent values (2);
commit;
User 2
insert into child values (1,2);
delete from parent where id=2;
Copyright 2006 Kyle Hailey
#.35
enq: TM - contention
PK
Parent
Child
ID
1
X
2
ID
1
X
2
ID Name
1
Session 1
Session 2
Value
Insert into Child ID=1
Delete from Parent where ID=2 :
Enqueue TM 4
Session 2 doesn’t know the value Session 1 inserted
Session 2 only knows there is an outstanding change
Copyright 2006 Kyle Hailey
#.36
enq: TM – contention Solution
PK
Parent
ID
1
2
ID
Index
Value
ID
1
Child
ID Name
Foreign Key
Session 1: Insert into Child ID=1
Session 2: Delete from Parent ID=2
OK – can verify quickly in the child index
Copyright 2006 Kyle Hailey
#.37
TM Further Investigation
select
event,
sql_id,
mod(p1,16) as "mode",
p2|| ' ' || o.name obj
from v$active_session_history ash,
obj$ o
where
event like 'enq: TM%'
and o.obj# (+)= ash.p2
order by sample_time;
EVENT
-----------------------------enq: TM - contention
enq: TM - contention
enq: TM - contention
enq: TM - contention
enq: TM - contention
enq: TM - contention
SQL_ID
mode OBJ
------------- ---- --------------8zw36yw3fq4yy
4 53372 CHILD
8zw36yw3fq4yy
4 53372 CHILD
8zw36yw3fq4yy
4 53372 CHILD
8zw36yw3fq4yy
4 53372 CHILD
8zw36yw3fq4yy
4 53372 CHILD
8zw36yw3fq4yy
4 53372 CHILD
Copyright 2006 Kyle Hailey
#.38
UL Locks
 User-defined Locks
 dbms_lock
Wait Event
enq: UL - contention
Parameter2
id
Parameter3
0
dbms_lock.allocate_unique(v_lockname, v_lockhandle);
dbms_lock.request(v_lockhandle, p_ltype);
dbms_lock.release(v_lockhandle);
Copyright 2006 Kyle Hailey
#.39
Internal Locks
 CI – Cross Instance
 HW – High Water
 KO – fast object checkpoint
 RO – Reuse Object
 SQ – Sequence Lock
 ST – Space Transaction
Copyright 2006 Kyle Hailey
#.40
enq: CI - contention
 Cross Instance
 not OPS lock.
 invoke actions in background processes



checkpoints
log switches
instance is shut down
Copyright 2006 Kyle Hailey
#.41
CI – Cross Instance
Id1 Meaning (parameter2)
Id2 Meaning (parameter3)
0 Flush buffers for reuse as new class
1 LGWR checkpointing and Hot Backup
2 DBWR synchronization of SGA with control file
3 Log file add/drop/rename notification
4 Write buffer for CR read
5 Test Call
6 Invalidate KCK cache in all instances
7 Alter rollback segment optimal
8 Signal Query Servers/coordinator
9 Create Remote Parallel Query Server
10 Set Global Partitions
11 Stop Disk Writes
12 Drop Sort Segments
13 Release unused space from Sort Segments
14 Instance Recovery for Parallel operation Group
15 Validate parallel slave Lock Value
16 Check Transaction State Objects
1 Pass in Parameters
2 Invoke the call in background process
3 Foreground has not returned yet
4 Used to allocate the CI call
5 Used to queue up interested clients
Copyright 2006 Kyle Hailey
#.42
CI Locks
select
substr(sql_text,0,20) sql_text,
p2,p3,
CURRENT_OBJ# || ' ' || name obj
,CURRENT_FILE# file#
,CURRENT_BLOCK# block#
from v$active_session_history ash,
obj$ o,
v$sqltext sql
where
event like 'enq: CI%'
and o.obj# (+)= ash.current_obj#
and sql.sql_id (+)= ash.sql_id
order by sample_time;
SQL_TEXT
P2
P3 OBJ
FILE# BLOCK#
-------------------- ----- ------- ------------ ------ ------INSERT INTO TOTO1 VA
1
5 54225 TOTO1
6 682721
Copyright 2006 Kyle Hailey
#.43
CI Locks
SQL_TEXT
P2 P3
---------------------------------------- --- -alter table XXXXX drop partition YYYYY
1 5
P2 = 1 " LGWR checkpointing and Hot Backup "
P3 = 5. "Used to queue up interested clients"
If p2=1 and p3=5, then contention on blocks being
checkpointed, try raising fast_start_mttr_target
alter system set fast_start_mttr_target=600 scope=both;
Copyright 2006 Kyle Hailey
#.44
enq: HW - contention
Wait Event
enq: HW - contention
Session 1
Parameter2
table space #
Parameter3
block
Header
Table
Session 2
Data
Session 3
High Water Mark
Empty
Copyright 2006 Kyle Hailey
#.45
HW
 Use Freelists

Cause a jump in High Water Mark by freelists *
_bump_highwater_mark_count
 Hidden Parameter
_bump_highwater_mark_count
 alter session set "_bump_highwater_mark_count"=100;


Not supported
 ASSM

Automatic segment space management
Copyright 2006 Kyle Hailey
#.46
HW Further Investigation
select
event,
sql_id,
CURRENT_OBJ# || ' ' || name obj
,CURRENT_FILE# file#
,CURRENT_BLOCK# block#
from v$active_session_history ash,
obj$ o
where
event like 'enq: HW%'
and o.obj# (+)= ash.current_obj#
order by sample_time;
EVENT
SQL_ID
OBJ
FILE# BLOCK#
-------------------- ------------- ----------- ----- -----enq: HW - contention 49ch3jfkncnsp 53410 T1_I1
13 29734
Copyright 2006 Kyle Hailey
#.47
enq: KO - fast object checkpoint
 Used when checking the cache for blocks from a
table for PQO direct read
Copyright 2006 Kyle Hailey
#.48
enq: RO - fast object reuse
 Drop or Truncate a table
 Wait
for DBWR to clean cache
 Solution
 Tune
DBWR using smaller MTTR
 Use GTT
 Truncate/drop less often
#.49
Other Resources
@?/rdbms/admin/utllockt
WAITING_SESSION LOCK_TYPE
MODE_REQUESTED MODE_HELD LOCK_ID1
LOCK_ID2
--------------- ----------- -------------- --------- --------- -------144
None
139
Transaction Share
Copyright 2006 Kyle Hailey
Exclusive 131113
7507
#.50
Blocking Sessions
Copyright 2006 Kyle Hailey
#.51
Summary : Internal Locks
 CI – Cross Instance
 HW – High Water


Look at object and SQL
use ASSM, freelists, pre-allocate extents
 KO – fast object checkpoint

PQO
 RO – reusable object

Reduce cache, tune DBWR, use GTT
 SQ – Sequence Lock

logon/logoff problem
 ST - Space Transaction




only one per database
used for space allocations uet, fet
Find object
use LMT
Copyright 2006 Kyle Hailey
#.52