Transcript Document
Enqueue Waits : Locks #.2 Locks Locks REDO Lib Cache Buffer Cache Network IO Copyright 2006 Kyle Hailey Enqueues = Locks CI TM Buffer Cache Disk I/O Enqueue Waits Library Cache TX - row lock contention TX - allocate ITL entry TX - index contention HW Redo SQL*Net Undo Locks ST Come in TS Many US Types SQ Copyright 2006 Kyle Hailey #.3 Locks Covered in this Section Part 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 Space Copyright 2006 Kyle Hailey #.4 #.5 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 time ASH Insolvable: No dependable way to find the blocking SQL Copyright 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 #.6 #.7 Statspack 9i 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 Statspack is INSUFFICIENT, missing Who is blocked Who Blocks What blocks How they are blocked Copyright 2006 Kyle Hailey #.8 9i Investigation Select * from v$event_name where name=‘enqueue’; Parameter1 ---------Name|mode parameter2 ---------id1 Copyright 2006 Kyle Hailey parameter3 ---------id2 #.9 9i Enqueue P1: Type and Mode P1 P1RAW ---------- -------1415053318 54580006 Type: 5458 Hex Decimal 54 = 84 58 = 88 ASCII = “T” = “X” Lock = TX 6 Copyright 2006 Kyle Hailey Mode: 0006 #.10 9i Translating Lock and Mode SELECT chr(bitand(p1,-16777216)/16777215)|| SELECT chr(bitand(p1, 16711680)/65535) Type, chr(bitand(p1,-16777216)/16777215)|| mod(p1,16) lmode fromchr(bitand(p1, v$session_wait 16711680)/65535) “Type", where event=‘enqueue’; mod(p1,16) as “Mode" from v$session_wait TY LMODE -- ---------Where event=‘enqueue’ TX 6 / #.11 10g Translates all the Enquques 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 Version 9 : P2 and P3 Parameter1 ---------Name|mode parameter2 ---------id1 parameter3 ---------id2 ID1 and ID2 had different meanings for each lock type Never defined Defined in 10g Parameter 2 = ID1 Parameter 3 = ID2 Copyright 2006 Kyle Hailey #.12 Common Lock Types Select event, parameter2 , parameter3 from v$event_name Wait 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<<16 | slot sequence TX - contention usn<<16 | slot sequence TX - index contention usn<<16 | slot sequence TX - row lock contention usn<<16 | slot sequence UL - contention id 0 US - contention undo segment # 0 Copyright 2006 Kyle Hailey #.13 Statspack 10g 5 Timed Events Avg %Total ~~~~~~~~~~~~~~~~~~ wait Call Event Waits Time (s) (ms) Time ----------------------------- ------ -------- ------ -----enq: TX - row lock contention 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 10g Better, tells what kind of lock But still not who is blocked, who blocks them what they are blocked on Copyright 2006 Kyle Hailey #.14 #.15 V$active_session_history Succeeds Who is waiting Who is the blocker SESSION_ID SESSION_SERIAL# USER_ID On what object CURRENT_OBJ# CURRENT_FILE# CURRENT_BLOCK# BLOCKING_SESSION BLOCKING_SESSION_STATUS BLOCKING_SESSION_SERIAL# What is the Blocker SQL not reliably possible Maybe by dumping REDO With what SQL SQL_ID OEM externalize all of this Copyright 2006 Kyle Hailey OEM 10g if P1 = 1415053318 then mode = 6 Then it is a data block row lock Copyright 2006 Kyle Hailey #.16 10g: find the lock mode #.17 P1 (parameter1) same for all locks select distinct parameter1 from v$event_name where name like 'enq:%' PARAMETER1 ---------name|mode select distinct event, p1, to_char(p1,'XXXXXXXX') p1raw from v$active_session_history where event like 'enq:%‘; EVENT P1 ------------------------------ ---------enq: TX - row lock contention 1415053316 enq: TX - row lock contention 1415053318 enq: TX - allocate ITL entry 1415053316 Copyright 2006 Kyle Hailey P1RAW --------54580004 54580006 54580004 P1 = name | mode P1 (parameter1) same for all locks select distinct parameter1 from v$event_name where name like 'enq:%' PARAMETER1 ---------name|mode select distinct event, p1, mod(p1,16) as "mode" from v$active_session_history where event like 'enq:%‘; EVENT ----------------------------enq: TX - allocate ITL entry enq: TX - row lock contention enq: TX - row lock contention P1 mode ---------- ---1415053316 4 1415053318 6 1415053316 4 Copyright 2006 Kyle Hailey #.18 #.19 Mode Cheat Sheet TX if P1 = 1415053316 then mode = 4 if P1 = 1415053318 then mode = 6 Copyright 2006 Kyle Hailey #.20 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 Transaction Locks (TX) TX = Transaction = Wait on UNDO Mode 6 modification of a row lock Mode 4 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 #.21 Transaction Locks (TX) 9i Only had one TX lock, but 10g has 4: 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 Mode 6 – classic row lock Mode 4 - pk violation, fk violation, bitmap chunk wait Wait for a data file extension, Alter tbs read only 4. enq: TX – contention Copyright 2006 Kyle Hailey #.22 #.23 TX Lock Session B update toto set name = ‘ADAMS’ where id = 1; Undo Segment Header Wait for Tx To commit Undo Segment Table Toto DataBlock Block Data Header Header Transaction 1 Session a update toto set name = ‘SMITH’ where id = 1; Row 1 Delete from toto where id = 2; Delete from toto where id = 9; Copyright 2006 Kyle Hailey #.24 enq: TX - row lock contention Mode 6, row in data block – only one reason User 1 User 2 SQL> delete from toto where id = 1; SQL> delete from toto where id =1; --hang SQL> Delete from titi Where id = 1; Copyright 2006 Kyle Hailey enq: TX - row lock contention if P1 = 1415053318 then mode = 6 Then it is a data block row lock Copyright 2006 Kyle Hailey #.25 #.26 enq: TX - row lock contention TX Mode 6 Table Value ID 1 foo Session 1: delete where id = 1; no commit Session 2: delete where id = 1 ; Enqueue TX 6 Copyright 2006 Kyle Hailey #.27 TX – Mode 4 if P1 = 1415053316 then mode = 4 Not same data but conflicts Copyright 2006 Kyle Hailey #.28 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 #.29 1. enq: TX - row lock contention Mode 4 , unique index User 1 User 2 create table p(n number); create unique index p_i on p(n); insert into p values(3); insert into p values(3); --hang Copyright 2006 Kyle Hailey #.30 1. enq: TX - row lock contention PK Table ID 2? ID Value 2? Mode 4 , unique index Session 1: Insert key no commit Session 2: Insert into same key : Enqueue TX 4 Copyright 2006 Kyle Hailey #.31 2. enq: TX - row lock contention Mode 4, foreign key User 8 create table parent ( id number primary key); create table child ( id number references parent, name varchar2(20)); insert into parent values (2); User 10 insert into child values (2,88); -- hang Copyright 2006 Kyle Hailey 2. enq: TX - row lock contention TX 4 - Foreign Key PK Parent ID 2? ID Value 2? Child ID Name 2 Foreign Key Session 1: Insert key into parent no commit Session 2: Insert into child same key : Enqueue TX 4 Copyright 2006 Kyle Hailey #.32 #.33 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 #.34 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 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 #.35 #.36 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=800 set value 2 Update id=3 set value 2 Copyright 2006 Kyle Hailey #.37 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 #.38 enq: TX - allocate ITL entry Transaction 1 Info DataBlock Block Data Header Header Transaction 2 Info ITL Data Copyright 2006 Kyle Hailey #.39 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 #.40 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; #.41 enq: TX - contention Example Session 1 – start transaction, don’t commit Session 2 – alter tablespace read only Example Data File Extension – waiter waitng for another session to extend file Index Block Split – waiter waiting for another session to split the block Others ? Free Lists Non-ASSM Sessions freeing block If no txs free lists available (min 16, grow up depending on block size) , pick one and wait TX 4 2PC – two phase commit First does a prepare the commit Any read or write access in the intervening time waits TX 4 Copyright 2006 Kyle Hailey TX Further Investigation #.42 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 #.43 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 #.44 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 #.45 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 #.46 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 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 #.47 TM Further Investigation #.48 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 #.49 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 Internal Locks CI – Cross Instance CU – Cursor Bind HW – High Water RO – Reuse Object ST – Space Transaction Copyright 2006 Kyle Hailey #.50 #.51 enq: CI - contention Cross Instance not OPS lock. invoke actions in background processes checkpoints log switches instance is shut down Copyright 2006 Kyle Hailey #.52 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 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 #.53 CI Locks SQL_TEXT P2 P3 ---------------------------------------- --- -alter table XXXXX drop partition YYYYY 1 5 ID1 = 2 " LGWR checkpointing and Hot Backup " ID2 = 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 #.54 #.55 enq: CU - contention cursor bind From Jonathan Lewis: Protects the variable definitions while the cursor is being optimized. 8i one CU per bind 9i one CU per cursor Copyright 2006 Kyle Hailey 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 #.56 HW Use Freelists Cause multiple jumps in High Water Mark Pre-Allocate Extents Alter table XXXX allocate extent; Hidden Parameter _bump_highwater_mark_count alter session set "_bump_highwater_mark_count"=100; Not supported ASSM Automatic segment space management Partition Object Copyright 2006 Kyle Hailey #.57 HW Further Investigation #.58 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 #.59 enq: KO - fast object checkpoint Used when checking the cache for blocks from a table for PQO direct read Copyright 2006 Kyle Hailey enq: RO - fast object reuse Drop or Truncate a table Wait for DBWR to clean cache Solution Smaller cache Tune DBWR using smaller MTTR Use GTT Truncate/drop less often alter system set fast_start_mttr_target=17 scope=both; SELECT TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES FROM V$INSTANCE_RECOVERY Copyright 2006 Kyle Hailey #.60 #.61 enq: ST - contention Space Transaction management lock Space Transaction Lock Allocating/deallocating extents Dictionary Managed Tablespaces Id1, Id2 Combinations: Always 0, Always 0. Solution Got to Locally Managed Tablespaces Copyright 2006 Kyle Hailey #.62 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 Blocking Sessions Copyright 2006 Kyle Hailey #.63 Summary : Internal Locks CI – Cross Instance CU – Cursor Bind HW – High Water Look at object and SQL use LMT, 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 #.64