lock 09 enqueues

Download Lock 09 Enqueues

Post on 05-Mar-2015




1 download

Embed Size (px)


Enqueue Waits : Locks


LocksLocks REDO Lib Cache Buffer Cache Network


Copyright 2006 Kyle Hailey

Enqueues = LocksCI TM Buffer Cache Disk I/O Enqueue Waits Library Cache Redo ST SQL*Net Undo TS USCopyright 2006 Kyle Hailey


TX - row lock contention TX - allocate ITL entry TX - index contention HW Locks Come in Many Types


Locks Covered in this SectionPart I TM table modification TX Transaction locks UL user lock Part II CI Cross Instance CU Cursor Bind HW High Water RO Reusable Object ST Space Transaction TS Temporary SpaceCopyright 2006 Kyle Hailey



Locks Can be Intractable Lock seems easy compared to latches or other esoteric waits, but they can be frustratingly intractable Statspack & AWR lacks enough data to solve To Solve, you need either Real ASH


Insolvable: No dependable way to find the blocking SQLCopyright 2006 Kyle Hailey

Solving Enqueues Need SQL and Object/row Statspack


fails V$active_session_history succeeds

In real time can also use v$lock v$session dba_blockers dba_waiters ?/rdbms/admin/utllockt.sql http://www.evdbt.com/enqwaits.sql

Copyright 2006 Kyle Hailey

Statspack 9iTop 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


Statspack is INSUFFICIENT, missing Who is blocked Who Blocks What blocks How they are blockedCopyright 2006 Kyle Hailey


9i InvestigationSelect * from v$event_name where name=enqueue; Parameter1 ---------Name|mode parameter2 ---------id1 parameter3 ---------id2

Copyright 2006 Kyle Hailey


9i Enqueue P1: Type and ModeP1 P1RAW ---------- -------1415053318 54580006 Type: 5458Hex Decimal 54 = 84 58 = 88 ASCII = T = X

Mode: 0006

Lock = TX 6Copyright 2006 Kyle Hailey


9i Translating Lock and ModeSELECT chr(bitand(p1,-16777216)/16777215)|| SELECT chr(bitand(p1, 16711680)/65535) Type, chr(bitand(p1,-16777216)/16777215)|| mod(p1,16) lmode fromchr(bitand(p1, 16711680)/65535) Type", v$session_wait where event=enqueue; mod(p1,16) as Mode"

from v$session_wait TY LMODE -- ---------Where event=enqueue TX 6 /


10g Translates all the Enquques 9i One

Wait : enqueue

10genqueue waits Specific to each type of enqueueenq: 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 contentionCopyright 2006 Kyle Hailey


Configuration Configuration Configuration Configuration Application Administrative Configuration Concurrency Application Application

Version 9 : P2 and P3Parameter1 ---------Name|mode ID1 and ID2


parameter2 ---------id1

parameter3 ---------id2

had different meanings for each lock type Never defined

Defined in 10g Parameter 2 = ID1 Parameter 3 = ID2Copyright 2006 Kyle Hailey

Common Lock TypesSelect event, parameter2 , parameter3 from v$event_nameWait enq: enq: enq: enq: enq: enq: enq: enq: enq: enq: enq: enq: enq: enq: enq: enq: enq: Event Parameter2 Parameter3 CI - contention opcode type CF - contention 0 operation CU - contention handle handle HW - contention table space # block IM - contention for blr pool # 0 RO - contention 2 0 RO - fast object reuse 2 0 SQ - contention object # 0 ST - contention 0 0 TM - contention object # table/partition TS - contention tablespace ID dba TX - allocate ITL entry usn