Enqueue Waits Locks Wait Tree Locks Waits Buffer
Enqueue Waits : Locks
Wait Tree - Locks Waits Buffer Cache TM 3 Row Lock Disk I/O TX 6 Row Lock Enqueue TX 4 ITL Lock Library Cache TX 4 PK/FK Redo HW Lock SQL*Net ST Lock TS Lock Undo Copyright 2006 Kyle Hailey #. 2
#. 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 Avg ~~~~~~~~~ wait Call (ms) Time Event Waits Time (s) %Total ------------- ------Enqueue 42 CPU time 126 3000 4 96. 5 2. 8 db file sequential read 165 1 4 . 4 control file sequential read 214 0 1 . 1 2 0 40 . 1 log file switch completion Need more info from v$session_wait Copyright 2006 Kyle Hailey
#. 5 v$session_wait SQL> select event, p 1, p 2, p 3 from v$session_wait; EVENT P 1 P 2 P 3 --------- -----enqueue 1415053318 589855 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 Ø P 1 = Type | mode Ø P 2 = ID 1 , depends on P 1 Ø P 3 = ID 2 , depends on P 1 Copyright 2006 Kyle Hailey
#. 7 Translating P 1 to Lock and Mode SQL> select p 1, p 1 raw from v$session_wait where sid=151; P 1 RAW -------1415053318 54580006 Type Mode Copyright 2006 Kyle Hailey
#. 8 Translating P 1 to Lock and Mode P 1 RAW -------54580006 Type: 5458 Hex Decimal 54 = 84 58 = 88 ASCII = “T” = “X” Lock = TX 6 Copyright 2006 Kyle Hailey Mode: 0006
#. 9 Translating P 1 to Lock and Mode column Type format a 4 column Mode format a 4 select sid, chr(to_number(substr(p 1 raw, 1, 1)) * 16 + to_number(substr(p 1 raw, 2, 1))) || chr(to_number(substr(p 1 raw, 3, 1)) * 16 + to_number(substr(p 1 raw, 4, 1))) Type, substr(p 1 raw, 8, 1) as "Mode" from v$session_wait where name=‘enqueue’; SID TYPE Mode ---- ---151 TX 06 Copyright 2006 Kyle Hailey
#. 10 Translating p 1 to Lock and Mode SELECT chr(bitand(p 1, -16777216)/16777215)|| chr(bitand(p 1, 16711680)/65535) "Lock", mod(p 1, 16) as "mode" FROM V$SESSION_WAIT Where sid=151 / bitand(p 1, 65536) "Mode" Copyright 2006 Kyle Hailey
Translating P 1 to Lock and Mode select sid, event, chr(bitand(P 1, -16777216)/16777215)|| chr(bitand(P 1, 16711680)/65535) as "Type", mod(p 1, 16) as "mode" from v$session_wait where event = 'enqueue‘; SID EVENT Ty mode P 2 P 3 ------- ---240 enqueue TX 6 2686995 433 Copyright 2006 Kyle Hailey #. 11
#. 12 Modes 1 2 3 4 5 6 Null SS SX S SSX X Null Sub share Sub exclusive 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 #. 14 select * from v$lock where type in ('TX', 'TM'); SID --151 135 TY ID 1 ID 2 LMODE REQUEST CTIME BLOCK -- ---------- ----TX 589855 1592 0 6 4049 0 TM 53737 0 3 0 4058 0 TM 53737 0 3 0 4049 0 TX 589855 1592 6 0 4058 1 TX TM ID 1 = RBS seg# | RBS slot # ID 2 = rbs wrap # ID 1 = object id ID 2 = 0 Copyright 2006 Kyle Hailey
#. 15 ID 1 and ID 2 Examples Ø Lock = TX Ø ID 1 = RBS seg# | RBS slot # Ø ID 2 = rbs wrap # Ø Lock = TM Ø ID 1 = object id Ø ID 2 = 0 ID 1 and ID 2 meanings can be determined from v$event_name in 10 g Copyright 2006 Kyle Hailey
ID 1 and ID 2 Definitions column parameter 1 format a 15 column parameter 2 format a 15 column parameter 3 format a 15 column lock format a 8 Select substr(name, 1, 7) as "lock", parameter 1, parameter 2, parameter 3 from v$event_name where name like 'enq%' LOCK ------enq: CF enq: HW enq: SQ enq: ST enq: TM enq: TS enq: TX Parmeter 1 ----name|mode name|mode Parmeter 2(ID 1) ------0 table space # object # 0 object # tablespace ID usn<<16 | slot Parameter 3(ID 2) -------operation block 0 0 table/partition dba sequence Copyright 2006 Kyle Hailey #. 16
Enqueues Decoded in 10 g Ø 10 g. R 2 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 enq: ZG - contention Administrative Copyright 2006 Kyle Hailey #. 17
Enqueue Solutions SQ – 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 Copyright 2006 Kyle Hailey #. 18
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 enqueue Ø ASH Data Ø v$session_wait data Ø Sql Trace with waits Copyright 2006 Kyle Hailey #. 19
#. 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_ID 1 LOCK_ID 2 ---------- -------14 10 Transaction 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 410343 AC 10 g Lockwait not null is blocker Pre-10 g Lockwait not null is the waiter Copyright 2006 Kyle Hailey #. 21
#. 22 Enqueue : TX 6 Example ØExclusive Row Level Lock User 1 User 2 SQL> delete from emp where empno = 7934; SQL> update emp set sal=2000 Where empno = 7934; Copyright 2006 Kyle Hailey
#. 23 Enqueue : TX 6 v$session_wait SQL> select event, p 1, p 2, p 3 from v$session_wait; EVENT P 1 P 2 P 3 --------- -----enqueue 1415053318 What can we do with this info? Copyright 2006 Kyle Hailey 589855 1592
Enqueue : TX 6 Type and Mode select sid, event, chr(bitand(P 1, -16777216)/16777215)|| chr(bitand(P 1, 16711680)/65535) as "Type", mod(p 1, 16) as "mode" from v$session_wait where event = 'enqueue‘; SID EVENT Ty mode P 2 P 3 ------- ---240 enqueue TX 6 2686995 433 Copyright 2006 Kyle Hailey #. 24
Enqueue : TX 6 v$lock #. 25 select * from v$lock where type in ('TX', 'TM'); SID --151 135 TY ID 1 ID 2 LMODE REQUEST CTIME BLOCK -- ---------- ----TX 589855 1592 0 6 4049 0 TM 53737 0 3 0 4058 0 TM 53737 0 3 0 4049 0 TX 589855 1592 6 0 4058 1 TX TM ID 1 = RBS seg# | RBS slot # ID 2 = rbs wrap # ID 1 = object id ID 2 = 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_ID 1 LOCK_ID 2 ---------- -------14 10 Transaction 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 410343 AC 10 g Lockwait not null is blocker Pre-10 g Lockwait not null is the waiter Copyright 2006 Kyle Hailey #. 27
#. 28 Enqueue : TX 4 ØIndex on p(id) User 1 User 2 SQL> insert into p values(3); Copyright 2006 Kyle Hailey
Enqueue : TX 4 select sid, event, chr(bitand(P 1, -16777216)/16777215)|| chr(bitand(P 1, 16711680)/65535) as "Type", mod(p 1, 16) as "mode" from v$session_wait where event = 'enqueue‘; SID EVENT Ty mode P 2 P 3 ------- ---240 enqueue TX 4 2686995 433 Copyright 2006 Kyle Hailey #. 29
#. 30 Enqueue : TX 4 SQL> select sid, type, id 1, id 2, lmode , request from v$lock where type in ('TX', 'TM'); SID -----139 146 139 139 146 TY ID 1 ID 2 LMODE REQUEST -- ----------TX 327689 1901 0 4 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 Header Transaction 1 Info Transaction 2 Info ITL Data Copyright 2006 Kyle Hailey
#. 33 Enqueue : TX 4 – ITL Data. Block Data 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, id 1, id 2, lmode , request from v$lock where type in ('TX', 'TM'); SID TY ID 1 ID 2 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 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 varchar 2(20)); insert into parent values (1); insert into child values (1, 2); commit; User 2 insert into child values (1, 2); delete from parent; Copyright 2006 Kyle Hailey
#. 36 Enqueue : TX 4 – Unique Key PK ID Parent Child ID ID Name Value 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, id 1, id 2, lmode , request from v$lock where type in ('TX', 'TM'); SID TY ID 1 ID 2 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 Copyright 2006 Kyle Hailey
Enqueue : TX 4 – Unique Key Solution PK ID Parent ID Value Index ID Child ID Name Session 1: Insert into Child ID=1 Session 2: Delete from Parent ID=2 OK – can Copyright verify 2006 quickly in the child index Kyle Hailey #. 38
#. 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, event, chr(bitand(P 1, -16777216)/16777215)|| chr(bitand(P 1, 16711680)/65535) as "Type", mod(p 1, 16) as "mode" from v$session_wait where event = 'enqueue‘; SID EVENT Ty mode P 2 P 3 ------- ------240 enqueue HW 6 4 16777715 Copyright 2006 Kyle Hailey #. 43
#. 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
- Slides: 43