enqueue waits : locks. #.2 copyright 2006 kyle hailey wait tree - locks waits disk i/o library cache...

of 43 /43
Enqueue Waits : Locks

Upload: martha-burdick

Post on 14-Dec-2015

243 views

Category:

Documents


2 download

TRANSCRIPT

Page 1: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Enqueue Waits : Locks

Page 2: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.2

Wait Tree - Locks

Waits

Disk I/O

Library Cache

Enqueue

Undo

TX 6 Row Lock

TX 4 ITL Lock

HW LockRedo

Buffer Cache

SQL*Net

TM 3 Row Lock

ST Lock

TS Lock

TX 4 PK/FK

Page 3: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

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

Page 4: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.4

Statspack

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

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

Enqueue 42 126 3000 96.5

CPU time 4 2.8

db file sequential read 165 1 4 .4

control file sequential read 214 0 1 .1

log file switch completion 2 0 40 .1

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

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

Enqueue 42 126 3000 96.5

CPU time 4 2.8

db file sequential read 165 1 4 .4

control file sequential read 214 0 1 .1

log file switch completion 2 0 40 .1

Need more info from v$session_waitNeed more info from v$session_wait

Page 5: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.5

v$session_wait

SQL> select event, p1,p2,p3 from v$session_wait;

EVENT P1 P2 P3

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

enqueue 1415053318 589855 1592

SQL> select event, p1,p2,p3 from v$session_wait;

EVENT P1 P2 P3

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

enqueue 1415053318 589855 1592

What can we do with this info?What can we do with this info?

Note: v$session_wait is for current waits. Need ASH or someNote: v$session_wait is for current waits. Need ASH or somesimilar data source for historic analysissimilar data source for historic analysis

Page 6: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.6

Enqueue : Args

P1 = Type | mode P2 = ID1 , depends on P1 P3 = ID2 , depends on P1

Page 7: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

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

ModeModeTypeType

Page 8: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.8

Translating P1 to Lock and Mode

Type: 5458

P1RAW--------54580006

Hex Decimal ASCII54 = 84 = “T”58 = 88 = “X”

Lock = TX 6

Mode: 0006

Page 9: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.9

Translating P1 to Lock and Modecolumn Type format a4column Mode format a4select 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

Page 10: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

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"

Page 11: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.11

Translating P1 to Lock and Mode

select sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",

mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue‘;

select sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",

mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue‘;

SID EVENT Ty mode P2 P3

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

240 enqueue TX 6 2686995 433

SID EVENT Ty mode P2 P3

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

240 enqueue TX 6 2686995 433

Page 12: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.12

Modes

1 Null Null

2 SS Sub share

3 SX Sub exclusive

4 S Share

5 SSX Share/sub exclusive

6 X Exclusive

Page 13: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.13

TypesCF – Control FileHW – High WaterSQ - SequenceST - Space TransactionTM - DMLTS – Temporary Segment / Table SpaceTX –TransactionUL – DBMS_LOCKUN – User NamedUS – Undo Segment

Page 14: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.14

Looking at v$lock

select * from v$lock where type in ('TX', 'TM');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK--- -- ---------- ---------- ----- ------- ----- -----151 TX 589855 1592 0 6 4049 0135 TM 53737 0 3 0 4058 0151 TM 53737 0 3 0 4049 0135 TX 589855 1592 6 0 4058 1

select * from v$lock where type in ('TX', 'TM');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK--- -- ---------- ---------- ----- ------- ----- -----151 TX 589855 1592 0 6 4049 0135 TM 53737 0 3 0 4058 0151 TM 53737 0 3 0 4049 0135 TX 589855 1592 6 0 4058 1

TX ID1 = RBS seg# | RBS slot # ID2 = rbs wrap #

TM ID1 = object id ID2 = 0

Page 15: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

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

Page 16: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.16

ID1 and ID2 Definitions

column parameter1 format a15column parameter2 format a15column parameter3 format a15column lock format a8

Select substr(name,1,7) as "lock",parameter1,parameter2,parameter3 from

v$event_namewhere name like 'enq%'

LOCK Parmeter1 Parmeter2(ID1) Parameter3(ID2)------- --------- ------------- --------------- enq: CF name|mode 0 operationenq: HW name|mode table space # blockenq: SQ name|mode object # 0enq: ST name|mode 0 0enq: TM name|mode object # table/partitionenq: TS name|mode tablespace ID dbaenq: TX name|mode usn<<16 | slot sequence

Page 17: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.17

Enqueues Decoded in 10g 10gR2 waits distinguish 208 enqueues

enq: DB - contention Administrativeenq: HW - contention Configurationenq: KO - fast object checkpoint Applicationenq: PW - flush prewarm buffers Applicationenq: RO - contention Applicationenq: RO - fast object reuse Applicationenq: SQ - contention Configurationenq: SS - contention Configurationenq: ST - contention Configurationenq: TM - contention Application enq: TW - contention Administrativeenq: TX - allocate ITL entry Configurationenq: TX - index contention Concurrencyenq: TX - row lock contention Applicationenq: UL - contention Applicationenq: ZG - contention Administrative

Page 18: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.18

Enqueue SolutionsSQ – Sequence Lock logon/logoff problem

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

Page 19: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.19

Enqueue Data Needed

If highest wait time is Enqueue ,Find out the kind of Enqueue and tune it

To tune enqueues we need one of the following to determine the type of enqueueASH Datav$session_wait dataSql Trace with waits

Page 20: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

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

Page 21: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.21

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 0143 -1 0 0 0144 -1 0 0 0149 -1 0 0 0151 53737 4 428 0 410343AC

10g Lockwait not null is blockerPre-10g Lockwait not null is the waiter

Page 22: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.22

Enqueue : TX 6 Example

User 1

SQL> delete from emp where empno = 7934;

User 2

SQL> update emp set sal=2000 Where empno = 7934;

Exclusive Row Level Lock

Page 23: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.23

Enqueue : TX 6 v$session_wait

SQL> select event, p1,p2,p3 from v$session_wait;

EVENT P1 P2 P3

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

enqueue 1415053318 589855 1592

SQL> select event, p1,p2,p3 from v$session_wait;

EVENT P1 P2 P3

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

enqueue 1415053318 589855 1592

What can we do with this info?What can we do with this info?

Page 24: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.24

Enqueue : TX 6 Type and Mode

select sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",

mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue‘;

select sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",

mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue‘;

SID EVENT Ty mode P2 P3

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

240 enqueue TX 6 2686995 433

SID EVENT Ty mode P2 P3

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

240 enqueue TX 6 2686995 433

Page 25: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.25

Enqueue : TX 6 v$lock

select * from v$lock where type in ('TX', 'TM');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK--- -- ---------- ---------- ----- ------- ----- -----151 TX 589855 1592 0 6 4049 0135 TM 53737 0 3 0 4058 0151 TM 53737 0 3 0 4049 0135 TX 589855 1592 6 0 4058 1

select * from v$lock where type in ('TX', 'TM');

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK--- -- ---------- ---------- ----- ------- ----- -----151 TX 589855 1592 0 6 4049 0135 TM 53737 0 3 0 4058 0151 TM 53737 0 3 0 4049 0135 TX 589855 1592 6 0 4058 1

TX ID1 = RBS seg# | RBS slot # ID2 = rbs wrap #

TM ID1 = object id ID2 = 0

Page 26: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

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

Page 27: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.27

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 0143 -1 0 0 0144 -1 0 0 0149 -1 0 0 0151 53737 4 428 0 410343AC

10g Lockwait not null is blockerPre-10g Lockwait not null is the waiter

Page 28: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.28

Enqueue : TX 4

User 1

SQL> insert into p values(3);

User 2

SQL> insert into p values(3);

Index on p(id)

Page 29: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.29

Enqueue : TX 4

select sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",

mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue‘;

select sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",

mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue‘;

SID EVENT Ty mode P2 P3

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

240 enqueue TX 4 2686995 433

SID EVENT Ty mode P2 P3

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

240 enqueue TX 4 2686995 433

Page 30: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.30

Enqueue : TX 4SQL> select sid, type, id1, id2, lmode , request from v$lock where type in ('TX', 'TM');

SID TY ID1 ID2 LMODE REQUEST---------- -- ---------- ---------- ---------- ---------- 139 TX 327689 1901 0 4 146 TM 55166 0 3 0 146 TM 55168 0 2 0 139 TM 55166 0 3 0 139 TM 55168 0 2 0 139 TX 720914 168 6 0 146 TX 327689 1901 6 0

Page 31: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.31

Enqueue : TX 4 - difficultDifficult – uses modifying different data ITL Unique Key Bitmap Index

Rare Read only Tablespace Free Lists Two phase commit

Page 32: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.32

Enqueue : TX 4 – ITL

Data Block Data Block HeaderHeader

ITLITL

DataData

Data Block Data Block HeaderHeader

Transaction 1 InfoTransaction 1 Info

Transaction 2 InfoTransaction 2 Info

Page 33: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.33

Enqueue : TX 4 – ITL

Data Block Data Block HeaderHeader

Transaction 1Transaction 1

DataData

Data Block Data Block HeaderHeader

Transaction 2Transaction 2

Row 1Row 1Row 2Row 2Row 3Row 3

Transaction 3Transaction 3

Page 34: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.34

Enqueue : TX 4 – ITL

SQL> select sid, type, id1, id2, lmode , request from v$lock where type in ('TX', 'TM');

SID TY ID1 ID2 LMODE REQUEST

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

148 TX 65559 1284 0 4

135 TM 54557 0 3 0

151 TM 54557 0 3 0

148 TM 54557 0 3 0

135 TX 524312 1592 6 0

151 TX 65559 1284 6 0

SQL> select sid, type, id1, id2, lmode , request from v$lock where type in ('TX', 'TM');

SID TY ID1 ID2 LMODE REQUEST

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

148 TX 65559 1284 0 4

135 TM 54557 0 3 0

151 TM 54557 0 3 0

148 TM 54557 0 3 0

135 TX 524312 1592 6 0

151 TX 65559 1284 6 0

Page 35: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.35

Enqueue : TX 4 – Unique Key

User 1create 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;

User 2

insert into child values (1,2);

Exclusive Row Level Lock

Page 36: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.36

Enqueue : TX 4 – Unique KeyParentParent ChildChild

IDID IDID NameNameValueValueIDID

PKPK

Session 1: Insert into Child ID=1Session 1: Insert into Child ID=1

Session 2: Delete from Parent ID=2 :Session 2: Delete from Parent ID=2 :

would require a FTS of childwould require a FTS of child

still not atomic, solution lock childstill not atomic, solution lock child

Enqueue TX 4 Enqueue TX 4

Page 37: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.37

Enqueue : TX 4 - Unique Key

SQL> select sid, type, id1, id2, lmode , request

from v$lock where type in ('TX', 'TM'); SID TY ID1 ID2 LMODE REQUEST---------- -- ---------- ---------- ---------- --------- 151 TM 54548 0 2 0 151 TM 54550 0 3 0 151 TX 524306 1590 6 0 135 TM 54548 0 3 0 135 TM 54550 0 0 4

SQL> select sid, type, id1, id2, lmode , request

from v$lock where type in ('TX', 'TM'); SID TY ID1 ID2 LMODE REQUEST---------- -- ---------- ---------- ---------- --------- 151 TM 54548 0 2 0 151 TM 54550 0 3 0 151 TX 524306 1590 6 0 135 TM 54548 0 3 0 135 TM 54550 0 0 4

Page 38: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.38

Enqueue : TX 4 – Unique Key Solution

ParentParent ChildChild

IDID IDID NameNameValueValueIDID

PKPK

Session 1: Insert into Child ID=1Session 1: Insert into Child ID=1

Session 2: Delete from Parent ID=2Session 2: Delete from Parent ID=2

OK – can verify quickly in the child indexOK – can verify quickly in the child index

IDID

IndexIndex

Page 39: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.40

Enqueue : TX 4 – Bitmap Indexes

Two sessions update keys in same key range

Page 40: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.41

Enqueue : ST

Space Transaction Lock Used in Dictionary Managed Tables

SolutionGot to Locally Managed Tablespaces

Page 41: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.42

Enqueue : HW

DataData

EmptyEmptyHigh Water MarkHigh Water Mark

HeaderHeaderTableTable

Page 42: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.43

Enqueue : HW

select sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",

mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue‘;

select sid,

event,

chr(bitand(P1,-16777216)/16777215)||

chr(bitand(P1,16711680)/65535) as "Type",

mod(p1,16) as "mode"

from v$session_wait

where event = 'enqueue‘;

SID EVENT Ty mode P2 P3

--- ------- -- ---- ---- -------240 enqueue HW 6 4 16777715

SID EVENT Ty mode P2 P3

--- ------- -- ---- ---- -------240 enqueue HW 6 4 16777715

Page 43: Enqueue Waits : Locks. #.2 Copyright 2006 Kyle Hailey Wait Tree - Locks Waits Disk I/O Library Cache Enqueue Undo TX 6 Row Lock TX 4 ITL Lock HW Lock

Copyright 2006 Kyle Hailey

#.44

Enqueue : HW

Use FreelistsCause multiple jumps in High Water Mark

Pre-Allocate ExtentsAlter table XXXX allocate extent;

Hidden Parameterbump_highwater_mark_count

ASSM Automatic segment space management