ukoug, oracle transaction locks

78
Enqueue Waits : Locks Enqueue Waits : Locks Thanks to Doug Burns for much Thanks to Doug Burns for much of the of the Row Lock example Row Lock example Kyle Hailey Kylehailey.com [email protected]

Upload: kyle-hailey

Post on 26-Jan-2015

116 views

Category:

Technology


4 download

DESCRIPTION

 

TRANSCRIPT

Page 1: UKOUG, Oracle Transaction Locks

Enqueue Waits : LocksEnqueue Waits : Locks

Thanks to Doug Burns for much of theThanks to Doug Burns for much of theRow Lock example Row Lock example

Kyle Hailey

Kylehailey.com

[email protected]

Page 2: UKOUG, Oracle Transaction Locks

Oaktable World UK

• Mon Dec 2, Tues Dec 3

Page 3: UKOUG, Oracle Transaction Locks
Page 4: UKOUG, Oracle Transaction Locks

1990 Oracle– 90 support– 92 Ported v6– 93 France– 95 Benchmarking – 98 ST Real World Performance

2000 Dot.Com 2001 Quest 2002 Oracle OEM 10g Success!Success!

First successful OEM designFirst successful OEM design

Who is Kyle Who is Kyle HaileyHailey

Page 5: UKOUG, Oracle Transaction Locks

1990 Oracle– 90 support– 92 Ported v6– 93 France– 95 Benchmarking – 98 ST Real World Performance

2000 Dot.Com 2001 Quest 2002 Oracle OEM 10g 2005 Embarcadero

– DB Optimizer

Who is Kyle Who is Kyle HaileyHailey

Page 6: UKOUG, Oracle Transaction Locks

Who is Kyle Hailey

• 1990 Oracle 90 support 92 Ported v6 93 France 95 Benchmarking 98 ST Real World Performance

• 2000 Dot.Com• 2001 Quest • 2002 Oracle OEM 10g• 2005 Embarcadero

DB Optimizer• Delphix

When not being a Geek- Have a little 4 year old boy who takes up all my time

Page 7: UKOUG, Oracle Transaction Locks

ProductionProduction DevelopmentDevelopment QAQA UATUAT

InstanceInstance InstanceInstance InstanceInstance InstanceInstance

DatabaseDatabase

File systemFile systemFile systemFile system

DatabaseDatabase

File systemFile systemFile systemFile system

DatabaseDatabase

File systemFile system

DatabaseDatabase

File systemFile systemFile systemFile systemFile systemFile systemFile systemFile system

Page 8: UKOUG, Oracle Transaction Locks

DatabaseDatabase

ProductionProduction

DatabaseDatabase

DevelopmentDevelopment

DatabaseDatabase

QAQA

DatabaseDatabase

UATUAT

InstanceInstance InstanceInstance InstanceInstance InstanceInstance

File File systemsystem

Page 9: UKOUG, Oracle Transaction Locks

AWR MethodAWR Method

Load profile good for Load profile good for having a feel for the having a feel for the application and application and comparing two comparing two periods for changes periods for changes Efficiency ratios Efficiency ratios misleading carry misleading carry over from version 6 over from version 6 daysdays

Top 5 Timed EventsTop 5 Timed Events~~~~~~~~~~~~~~~~~~ % Total~~~~~~~~~~~~~~~~~~ % TotalEvent Waits Time (s) Call TimeEvent Waits Time (s) Call Time---------------------- ------------ ----------- ------------------------------- ------------ ----------- ---------buffer busy waits 2,748 250 78.72buffer busy waits 2,748 250 78.72CPU time 32 10.16CPU time 32 10.16free buffer waits 1,588 15 4.63free buffer waits 1,588 15 4.63write complete waits 10 8 2.51write complete waits 10 8 2.51log buffer space 306 5 1.51log buffer space 306 5 1.51 --------------------------------------------------------------------------------

SummarySummary

WaitsWaits

Who/WhenWho/When

Big PictureBig Picture

Page 10: UKOUG, Oracle Transaction Locks

Row Locks 10g+Row Locks 10g+Top 5 Timed Events Avg %TotalTop 5 Timed Events Avg %Total~~~~~~~~~~~~~~~~~~ wait Call~~~~~~~~~~~~~~~~~~ wait CallEvent Waits Time (s) (ms) TimeEvent Waits Time (s) (ms) Time----------------------------------------- ------------ ----------- ------ ----------------------------------------------- ------------ ----------- ------ ------enq: TX - row lock contention 59 160 2714 41.8enq: TX - row lock contention 59 160 2714 41.8PL/SQL lock timer 4 117 29291 30.6PL/SQL lock timer 4 117 29291 30.6CPU time 28 7.2CPU time 28 7.2buffer busy waits 1,217 18 15 4.7buffer busy waits 1,217 18 15 4.7log file parallel write 422 11 27 3.0log file parallel write 422 11 27 3.0

Who is waiting?Who is waiting?

Who is blocking?Who is blocking?

What is the SQL?What is the SQL?

What is the row?What is the row?Not in AWR reportNot in AWR report

Page 11: UKOUG, Oracle Transaction Locks

Power of ASH !

v$active_session_historyv$active_session_history

• 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

Page 12: UKOUG, Oracle Transaction Locks

ContentContentPart I : Row Locks

– enq: TX - row lock contentionenq: TX - row lock contention• Data in ASHData in ASH• OEMOEM• V$lock & v$sessionV$lock & v$session• ASHASH• Find blocking SQL Find blocking SQL

Part II : Row Locks ??– enq: TX - row lock contention, mode 4enq: TX - row lock contention, mode 4

Part III : Other TX locks– enq: TX - allocate ITL entry enq: TX - allocate ITL entry – enq: TX - index contention enq: TX - index contention – enq: TX – contention enq: TX – contention

Part IVPart IV : TM locks : TM locks

Page 13: UKOUG, Oracle Transaction Locks

Lock TypesLock Types• 9i

– One Wait : “enqueue”

• 10g – 208 enqueue waits

– Specific to each type of enqueue

– select name,wait_class from v$event_name where name like 'enq%'

enq: HW - contention enq: HW - contention ConfigurationConfigurationenq: SQ - contention enq: SQ - contention ConfigurationConfigurationenq: SS - contention enq: SS - contention ConfigurationConfigurationenq: ST - contention enq: ST - contention

ConfigurationConfigurationenq: TM - contention enq: TM - contention Application Application enq: TW - contention enq: TW - contention

AdministrativeAdministrativeenq: TX - allocate ITL entry enq: TX - allocate ITL entry

ConfigurationConfigurationenq: TX - index contention enq: TX - index contention ConcurrencyConcurrencyenq: TX - row lock contention enq: TX - row lock contention ApplicationApplicationenq: TX – contention enq: TX – contention ApplicationApplication

TXTXTransactionTransaction

TMTMTable ModificationTable Modification

Page 14: UKOUG, Oracle Transaction Locks

Lock ModesLock Modes

# Type Name

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

1 Null Null

2 SS Sub share

3 SX Sub exclusive

4 S Share

5 SSX Share/sub exclusive

6 X Exclusive

Page 15: UKOUG, Oracle Transaction Locks

Lock Type and ModeLock Type and ModeSelect parameter1 from v$event_name where name like ‘enq%’;

Parameter1---------- Name|mode

P1 P1RAW---------- --------1415053318 54580006

Select p1, p1raw from v$session where event like 'enq%';

Mode: 0006Name: 5458

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

Lock = TX 6

Page 16: UKOUG, Oracle Transaction Locks

Type and ModeType and Mode

SELECT chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1, 16711680)/65535) TYPE, mod(p1,16) LMODEfrom v$sessionwhere event like ‘enq%’;

TY LMODE-- ----------TX 6

SELECT chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1, 16711680)/65535) TYPE, mod(p1,16) LMODEfrom v$sessionwhere event like ‘enq%’;

TY LMODE-- ----------TX 6

Page 17: UKOUG, Oracle Transaction Locks

enq: TX - row lock contentionenq: TX - row lock contention, mode 6, mode 6

To Solve we need, and ASH gives :

1.1. Waiter Waiter 2.2. BlockerBlocker3.3. Blocking ObjectBlocking Object4.4. Lock TypeLock Type

– type type – ModeMode

5.5. Blocking SQL : Blocking SQL : MissingMissing1.1.Log miner?Log miner?2.2.Undo ?Undo ?

Page 18: UKOUG, Oracle Transaction Locks

OEM 10g OEM 10g

if P1 = 1415053318

then mode = 6Then it is a data block row lock

Page 19: UKOUG, Oracle Transaction Locks

enq: TX - row lock contentionenq: TX - row lock contention

User 1

SQL> delete from toto where id = 1;

User 2

SQL> delete from toto where id =1;

Mode 6, row in data block

TableTableValueValueIDID

foofoo11

Page 20: UKOUG, Oracle Transaction Locks

Querying ASH 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 blockerfrom v$active_session_history ash, all_objects owhere event like 'enq: %' and o.object_id (+)= ash.CURRENT_OBJ#/

http://ashmasters.comhttp://ashmasters.com

Meaning variesMeaning varies

Any enqueueAny enqueue

Page 21: UKOUG, Oracle Transaction Locks

Other Data SourcesOther Data Sources

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

Page 22: UKOUG, Oracle Transaction Locks

Step one: get a lock

Session 1Session 1

Session_1> select pk_id from test_tab1 Session_1> select pk_id from test_tab1 where rownum=1 ;where rownum=1 ;

PK_IDPK_ID----------------------------

40514051

select pk_id from test_tab1 select pk_id from test_tab1 where pk_id = where pk_id = 40514051 for updatefor update;;

Page 23: UKOUG, Oracle Transaction Locks

Step 2: create a blocker

Session Session 22 - -

Session_2Session_2> select pk_id from test_tab1 > select pk_id from test_tab1 where pk_id = where pk_id = 40514051 for updatefor update;;

Hangs …Hangs …

Page 24: UKOUG, Oracle Transaction Locks

Step 3: release the lock and blocking

Session 1Session 1

Session_1> commit;Session_1> commit;

Commit complete.Commit complete.

Session 2Session 2 now continuesnow continues

PK_IDPK_ID-------------------- 40514051

Page 25: UKOUG, Oracle Transaction Locks

Locks in OEM:

One SQLOne SQL One SessionOne Session

ApplicationApplication

Wait Wait clickclick

Page 26: UKOUG, Oracle Transaction Locks

Click on “Application”

ClickClick on on SQL IDSQL ID

Page 27: UKOUG, Oracle Transaction Locks

Click on SQL

SQL waits for lockSQL waits for lock

get’s lock and does I/O to completeget’s lock and does I/O to complete

Page 28: UKOUG, Oracle Transaction Locks

Click on “Session ID”

ClickClick on on Session IDSession ID

Page 29: UKOUG, Oracle Transaction Locks

No info is lock is gone

Limited Use : lock is already releasedLimited Use : lock is already released

Page 30: UKOUG, Oracle Transaction Locks

Session Before Lock released

BlockerBlocker

Holds lockHolds lockWaiterWaiter

ClickClick

Page 31: UKOUG, Oracle Transaction Locks

ASH data in EM, but missing blocking info

BLOCKING_SESSION_STATUSBLOCKING_SESSION_STATUS

BLOCKING_SESSIONBLOCKING_SESSION

BLOCKING_SESSION_SERIAL#BLOCKING_SESSION_SERIAL#

Page 32: UKOUG, Oracle Transaction Locks

v$lock & v$session – during lock wait

SQL> select SQL> select s.sid, s.state, s.event, s.sql_id,s.sid, s.state, s.event, s.sql_id, l.type, l.lmodel.type, l.lmode

2 from 2 from v$session v$session ss, , v$lock v$lock ll

3 where 3 where s.sids.sid = = l.sid (+)l.sid (+)

4 and 4 and s.username='TESTUSER's.username='TESTUSER'

5 order by 5 order by s.sid;s.sid;

SID STATE EVENT SQL_ID SID STATE EVENT SQL_ID TY LMODETY LMODE

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

150 WAITING enq: TX - row lock contention 0ut22yp7mh229150 WAITING enq: TX - row lock contention 0ut22yp7mh229 TM 3TM 3

150 WAITING enq: TX - row lock contention 0ut22yp7mh229 150 WAITING enq: TX - row lock contention 0ut22yp7mh229 TX 0TX 0

157 WAITING SQL*Net message from client 157 WAITING SQL*Net message from client TX 6TX 6

157 WAITING SQL*Net message from client 157 WAITING SQL*Net message from client TM 3TM 3

BlockerBlocker

Idle, holds locksIdle, holds locks

Waiter, wants locksWaiter, wants locks

Page 33: UKOUG, Oracle Transaction Locks

v$lock : after commit by Session 1

SQL> select s.sid, s.state, s.event, s.sql_id, l.type, l.lmodeSQL> select s.sid, s.state, s.event, s.sql_id, l.type, l.lmode

2 from v$session s, v$lock l2 from v$session s, v$lock l

3 where s.sid = l.sid (+)3 where s.sid = l.sid (+)

4 and s.username='TESTUSER'4 and s.username='TESTUSER'

5 order by s.sid;5 order by s.sid;

SID STATE EVENT SQL_ID TY LMODESID STATE EVENT SQL_ID TY LMODE

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

150 WAITING SQL*Net message from client TM 3150 WAITING SQL*Net message from client TM 3

150 WAITING SQL*Net message from client TX 6150 WAITING SQL*Net message from client TX 6

157 WAITING SQL*Net message from client157 WAITING SQL*Net message from client

Non-blocking, idleNon-blocking, idleHolds LocksHolds Locks

Page 34: UKOUG, Oracle Transaction Locks

v$active_session_history – during and afterSQL> select

a.sample_time, a.session_id,

a.event, a.session_state, a.sql_id,

a.blocking_session BSID,

a.blocking_session_status BSTATUS

from v$active_session_history a,

dba_users u

where u.user_id = a.user_id

and u.username = 'TESTUSER';

TIME SID STATE EVENT SQL_ID BSID BSTATUS

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

14.59.37 150 ON_CPU 0ut22yp7mh229 NOT IN WAIT

14.59.36 150 ON_CPU 0ut22yp7mh229 NOT IN WAIT

14.59.35 150 WAITING db file scattered read 0ut22yp7mh229 NO HOLDER

14.59.34 150 WAITING db file scattered read 0ut22yp7mh229 NO HOLDER

14.59.33 150 WAITING enq: TX - row lock contention 0ut22yp7mh229 157 VALID

14.59.32 150 WAITING enq: TX - row lock contention 0ut22yp7mh229 157 VALID

14.59.31 150 WAITING enq: TX - row lock contention 0ut22yp7mh229 157 VALID

14.59.30 150 WAITING enq: TX - row lock contention 0ut22yp7mh229 157 VALID

14.59.29 150 WAITING enq: TX - row lock contention 0ut22yp7mh229 157 VALID

Where is the BLOCKER, 157, info in ASH ?Where is the BLOCKER, 157, info in ASH ?

NowNow

PastPast

Page 35: UKOUG, Oracle Transaction Locks

V$active_session_historySQL> select

a.sample_time, a.session_id,

a.event, a.session_state, a.sql_id,

a.blocking_session BSID,

a.blocking_session_status BSTATUS

from v$active_session_history a,

dba_users u

where u.user_id = a.user_id

and u.username = 'TESTUSER';

TIME SID STATE EVENT SQL_ID BSID BSTATUS

22.23.59 147 ON CPU 0ut22yp7mh229 NOT IN WAIT

22.23.58 147 WAITING enq: TX - row lock contention 0ut22yp7mh229 148 VALID

22.23.57 147 WAITING enq: TX - row lock contention 0ut22yp7mh229 148 VALID

22.23.56 147 WAITING enq: TX - row lock contention 0ut22yp7mh229 148 VALID

22.23.55 147 WAITING enq: TX - row lock contention 0ut22yp7mh229 148 VALID

22.23.33 148 WAITING db file scattered read cda14zb83bb5u NO HOLDER

22.23.32 148 WAITING db file scattered read cda14zb83bb5u NO HOLDER

22.23.31 148 WAITING db file scattered read cda14zb83bb5u NO HOLDER

22.23.30 148 WAITING db file scattered read cda14zb83bb5u NO HOLDER

SQL> select sql_text from v$sql where sql_id=SQL> select sql_text from v$sql where sql_id='cda14zb83bb5u'cda14zb83bb5u’;’;

SQL_TEXTSQL_TEXT----------------------------------------------------------------------------------------------------------------------------------------------------------------

select count(*) from test_tab1select count(*) from test_tab1

NOTE:NOTE:SESSION_ID + SESSION_SERIAL#SESSION_ID + SESSION_SERIAL#

Is uniqueIs unique

Page 36: UKOUG, Oracle Transaction Locks

How do we find the SQL that took the lock?

Page 37: UKOUG, Oracle Transaction Locks

TX LockTX Lock

update toto set name = ‘SMITH’ where id = 1;

update toto set name = ‘SMITH’ where id = 1;

Session aSession a

Data Block Data Block HeaderHeader

Transaction 1Transaction 1

Data Block Data Block HeaderHeader

Row 1Row 1

Table TotoTable Toto

Undo Undo SegmentSegmentHeaderHeader

Session BSession B

update toto set name = ‘ADAMS’ where id = 1;

update toto set name = ‘ADAMS’ where id = 1;

Wait for TxWait for TxTo commitTo commit

Delete from toto where id = 2;

Delete from toto where id = 2;Delete from toto where id = 9;

Delete from toto where id = 9;

Undo Undo SegmentSegment

RedoRedo

Page 38: UKOUG, Oracle Transaction Locks

Logminer : setupSQL> select group#, thread#, status from v$log;

GROUP# THREAD# STATUS

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

1 1 CURRENT

2 1 ACTIVE

3 1 ACTIVE

SQL> select group#, member from v$logfile;

GROUP# MEMBER

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

1 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\REDO01.LOG

2 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\REDO02.LOG

3 C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\REDO03.LOG

SQL> exec dbms_logmnr.add_logfile('&my_member');

Enter value for my_member: C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\REDO01.LOG

PL/SQL procedure successfully completed.

SQL> exec dbms_logmnr.start_logmnr(options => dbms_logmnr.dict_from_online_catalog);

PL/SQL procedure successfully completed.

@[email protected]

Page 39: UKOUG, Oracle Transaction Locks

Logminer : setup locking

SQL> create table test_tab1

as select object_id pk_id, object_name from all_objects;

SQL> select rowid, object_name from test_tab1 where pk_id=5042;

ROWID OBJECT_NAME

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

AAAN2NAAKAAAaBLAEX SYSTEM_PRIVILEGE_MAP

SESSION_1> update test_tab1 set object_name='UKOUG 1' where pk_id=5042;

SESSION_2> update test_tab1 set object_name='UKOUG 2' where pk_id=5042;

Future Future Reference: Reference: ROWIDROWID

CreateCreate

Lock Lock WaitWait

Page 40: UKOUG, Oracle Transaction Locks
Page 41: UKOUG, Oracle Transaction Locks

Find Transaction

• From v$transaction– Xid of current active transaction

• From v$active_session _history– Calculate XID from p2, p3 – For currently waiting ‘enq: TX row lock contention’

• Use XID to dump redo log for that TX

Page 42: UKOUG, Oracle Transaction Locks

Logminer with XIDSQL> select xid, xidusn, xidslot, xidsqn

from v$transaction order by start_time;

XID XIDUSN XIDSLOT XIDSQN

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

0A00240064180000 10 36 6244

SQL>

select username, session# sid, serial# , sql_redo

from v$logmnr_contents

where XID = '&tx';

USERNAME SID SERIAL# SQL_REDO

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

UNKNOWN 0 0 set transaction read write;

UNKNOWN 0 0 update "SYS"."TEST_TAB1" set "OBJECT_NAME" =

'SESSION 1' where "OBJECT_NAME" =

'CLUSTER_NODES' and ROWID = 'AAAT5XAABAAAW7hAEr’;

UNKNOWN 0 0 commit;

Only one transaction active on system, ours

@[email protected]

@[email protected]

Page 43: UKOUG, Oracle Transaction Locks

Finding XID from ASHNormal use P2, P3 from wait:WAIT TYPE = 'enq: TX - Row Lock Contention’Then P2=usn<<16||slot P3=Sequence => XID

select

distinct

lpad(ltrim(to_char(p2,'XXXXXX')),6,'0')||'00'||

ltrim(to_char(mod(p3,256),'XX'))||

ltrim(to_char(trunc(p3/256),'XX'))||'0000'

block_xid,

to_char(p2,'XXXXXXXX') p2hex,

to_char(p3,'XXXXXXXX') p3hex,

trunc(p2/65536) usn,

mod(p2,65536) slot,

p3 sqn, xid wait_xid

from v$active_session_history

where event like 'enq: T%'

and sample_time > sysdate - &v_minutes/(60*24)

;

select username, session# sid, select username, session# sid, serial# , sql_redoserial# , sql_redofrom v$logmnr_contentsfrom v$logmnr_contentswhere XID = 'where XID = '&tx&tx';';

@[email protected]

@[email protected]

Page 44: UKOUG, Oracle Transaction Locks

Logminer by USN and SLOTSQL> select xid, xidusn, xidslot, xidsqn

from v$transaction order by start_time;

XID XIDUSN XIDSLOT XIDSQN

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

0A00240064180000 10 36 6244

SYS@TEST1020> select distinct

username, session# sid, serial# , sql_redo

from v$logmnr_contents

where timestamp > sysdate- &minutes/(60*24)

and xidusn=10

and xidslt=36;

Enter value for minutes: 10

USERNAME SID SERIAL# SQL_REDO

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

UNKNOWN 0 0 set transaction read write;

UNKNOWN 0 0 update "SYS"."TEST_TAB1" set "OBJECT_NAME" =

'SESSION 1' where "OBJECT_NAME" =

'CLUSTER_NODES' and ROWID = 'AAAT5XAABAAAW7hAEr’;

UNKNOWN 0 0 commit;

Page 45: UKOUG, Oracle Transaction Locks

Dumping Redo Directly

SQL> select rowid, object_name from test_tab1 where pk_id=5041;

ROWID OBJECT_NAME

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

AAAN2NAAKAAAaBLAEX SYSTEM_PRIVILEGE_MAP

SYS@TEST1020> select

dbms_rowid.rowid_block_number('&my_rowid') block_number,

dbms_rowid.rowid_to_absolute_fno('&my_rowid','SYS','TEST_TAB1') file_id

from dual;

Enter value for my_rowid: AAAN2NAAKAAAaBLAEX

FILE_ID BLOCK_NUMBER

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

10 106571

Page 46: UKOUG, Oracle Transaction Locks

Data block row updateSYS@TEST1020> alter system dump logfile '&&my_member'  

     dba min 10 106571 dba max 10 106571;

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\REDO01.LOG

System altered

DUMP OF REDO FROM FILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST1020\REDO01.LOG'  Opcodes

*.*  DBAs: (file # 10, block # 106571) thru (file # 10, block # 106571)  RBAs:

0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff  SCNs: scn: 0x0000.00000000 thru

scn: 0xffff.ffffffff

REDO RECORD - Thread:1 RBA: 0x0000b3.00000009.0010 LEN: 0x0210 VLD: 0x0d SCN:

0x0000.008a42e6 SUBSCN:  1 04/20/2009 20:46:57 CHANGE #1 TYP:2 CLS: 1 AFN:10

DBA:0x0281a04b OBJ:56717 SCN:0x0000.008a4239 SEQ:  2 OP:11.19 KTB Redo op:

0x11  ver: 0x01  op: F  xid:  0x000a.024.00001864    uba: 0x0080009d.031a.27

0A00240064180000

CLS: 1 - Data Class BlockCLS: 1 - Data Class BlockOP:11.19 - Operation Update Multiple Rows.OP:11.19 - Operation Update Multiple Rows.

FILE_ID BLOCK_NUMBER FILE_ID BLOCK_NUMBER

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

10 106571 10 106571

Page 47: UKOUG, Oracle Transaction Locks

Verify DBA and Table REDO RECORD - Thread:1 RBA: 0x0000b3.00000009.0010 LEN: 0x0210 VLD: 0x0d SCN:

0x0000.008a42e6 SUBSCN:  1 04/20/2009 20:46:57 CHANGE #1 TYP:2 CLS: 1 AFN:10

DBA:0x0281a04b OBJ:56717 SCN:0x0000.008a4239 SEQ:  2 OP:11.19 KTB Redo op:

0x11  ver: 0x01  op: F  xid:  0x000a.024.00001864    uba: 0x0080009d.031a.27

TESTUSER@TEST1020> select object_name, object_type  

  from user_objects  

 where object_id=56717;

OBJECT_NAME OBJECT_TYPE

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

TEST_TAB1 TABLE

SYS@TEST1020> select to_char(dbms_utility.make_data_block_address(10,106571),  

                          'XXXXXXXX') DBA from dual;

DBA

---------  

281A04B

Page 48: UKOUG, Oracle Transaction Locks

Update Value

Array Update of 1 rows:

col 1: [ 9] 53 45 53 53 49 4f 4e 20 30

CHANGE #3 TYP:2 CLS:1 AFN:1 DBA:0x00416ee1 OBJ:81495 SCN:0x0000.01052c55 SEQ:1 OP:11.19 ENC:0 RBL:0

col 1: [ 9] 53 45 53 53 49 4f 4e 20 32

““session 1session 1””

““session 2session 2””

Page 49: UKOUG, Oracle Transaction Locks

Last Try: flashback

SELECT VERSIONS_XID

, VERSIONS_STARTTIME

, VERSIONS_ENDTIME

, VERSIONS_STARTSCN

, VERSIONS_ENDSCN

, VERSIONS_OPERATION

, object_name

FROM SYS.TEST_TAB1

VERSIONS BETWEEN

TIMESTAMP MINVALUE AND MAXVALUE

where VERSIONS_XID is not null

ORDER BY VERSIONS_STARTTIME

/

Page 50: UKOUG, Oracle Transaction Locks

From ASH

BLOCK_XID P2HEX P3HEX USN SLOT SQN WAIT_XID

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

0A00010072640000 A0001 6472 10 1 25714

From Flashback

VERSIONS_XID VERSIONS_STARTTIME ENDTIME STARTSCN ENDSCN V OBJECT_NAME

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

0A00010072640000 15-OCT-13 06.46.30 PM 17042888 U SESSION 1

Page 51: UKOUG, Oracle Transaction Locks

enq: TX - row lock contention – Mode 4enq: TX - row lock contention – Mode 4

Page 52: UKOUG, Oracle Transaction Locks

P1 = name | modeP1 = name | modeselect distinct parameter1 from v$event_name select distinct parameter1 from v$event_name where name like 'enq:%'where name like 'enq:%'

selectselect event, event, mod(p1,16) as "mode"mod(p1,16) as "mode" from v$active_session_historyfrom v$active_session_history where event like 'enq:%where event like 'enq:%‘‘;;

EVENT modeEVENT mode

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

enq: TX - row lock contention 6enq: TX - row lock contention 6enq: TX - row lock contention 4enq: TX - row lock contention 4

PARAMETER1PARAMETER1

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

name|modename|mode

Page 53: UKOUG, Oracle Transaction Locks

enq: TX - row lock contentionenq: TX - row lock contention

• Mode 6 (exclusive)– modification of a row lock

• Mode 4 (share)– ??

Page 54: UKOUG, Oracle Transaction Locks

enq: TX - row lock contentionenq: TX - row lock contention

if P1 = 1415053318

then mode = 6Then it is a data block row lock

Page 55: UKOUG, Oracle Transaction Locks

TX – Mode 4TX – Mode 4

if P1 = 1415053316

then mode = 4Not same data but conflicts

Page 56: UKOUG, Oracle Transaction Locks

enq: TX - row lock contentionenq: TX - row lock contention

Mode 4, happens for 3 reasons1. Unique key contention2. Foreign Key contention3. Bitmap index contention (others?)

Page 57: UKOUG, Oracle Transaction Locks

1.1. enq: TX - row lock contentionenq: TX - row lock contention

User 1

create table p(n number);

create unique index p_i on p(n);insert into p values(2);

User 2

insert into p values(2);

Mode 4 , unique index

TableTable

IDID ValueValueIDID

PKPK

2 ? 2 ? 2 ? 2 ? 22

Page 58: UKOUG, Oracle Transaction Locks

2.2. enq: TX - row lock contentionenq: TX - row lock contention

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

Mode 4, foreign key

ParentParent ChildChildIDID IDID NameNameValueValueIDID

PKPK

2 ? 2 ? 2 ? 2 ? 2 2

Page 59: UKOUG, Oracle Transaction Locks

3.3. enq: TX - row lock contentionenq: TX - row lock contention

• Bitmaps are compressed• Changes to the same bitmap cause locks

ValueValue Start Start RowidRowid

EndEndRowidRowid

Bitmap Bitmap

11 000.000.0000000.000.0000

000.000.000000.000.000

01010000111000011100001100 01010000111000011100001100 22

000.000.0000000.000.0000

000.000.000000.000.000

01010000111000011100001100 01010000111000011100001100

Mode 4, bitmap

Page 60: UKOUG, Oracle Transaction Locks

3.3. enq: TX - row lock contentionenq: TX - row lock contention

update t1 set update t1 set value = 2value = 2 where row= 13;where row= 13;

update t1 set update t1 set value = 2value = 2 where row= 13;where row= 13;

Session 2Session 2

Different rows but Different rows but same key value same key value

create table t1 (create table t1 (valuevalue number(4),number(4),rowrow number(4));number(4));

insert into t1insert into t1 select 1, rownumselect 1, rownum from all_objectsfrom all_objects where rownum <= 400;where rownum <= 400;commit;commit;create bitmap index i1 on t1(value);create bitmap index i1 on t1(value);

create table t1 (create table t1 (valuevalue number(4),number(4),rowrow number(4));number(4));

insert into t1insert into t1 select 1, rownumselect 1, rownum from all_objectsfrom all_objects where rownum <= 400;where rownum <= 400;commit;commit;create bitmap index i1 on t1(value);create bitmap index i1 on t1(value);

update t1 set update t1 set value = 2value = 2 where row= 12;where row= 12;

update t1 set update t1 set value = 2value = 2 where row= 12;where row= 12;

Session 1Session 1

Mode 4, foreign key

Page 61: UKOUG, Oracle Transaction Locks

3.3. enq: TX - row lock contentionenq: TX - row lock contention

• Bitmaps are compressed• Changes to the same bitmap chunk cause locks

ValueValue Start Start RowidRowid

EndEndRowidRowid

Bitmap Bitmap

11 200.0200.0

204.7204.7

01010000111000011100001100 01010000111000011100001100 11

205.0205.0 210.3210.3

01010000111000011100001100 01010000111000011100001100 22

200.0200.0 205.6205.6

01010000111000011100001100 01010000111000011100001100

blockblock rowrow

Mode 4, foreign key

Page 62: UKOUG, Oracle Transaction Locks

3.3. enq: TX - row lock contentionenq: TX - row lock contention

ValueValue Start Start RowidRowid

EndEndRowidRowid

Bitmap Bitmap

11 200.0200.0

204.7204.7

01010000111000011100001100 01010000111000011100001100 22

205.0205.0 210.3210.3

01010000111000011100001100 01010000111000011100001100 33

200.0200.0 205.6205.6

01010000111000011100001100 01010000111000011100001100

Session 1Session 1 Session 2Session 2

Update row=12 Update row=12 set value 2set value 2

Update row=13Update row=13set value 2set value 2

Mode 4, foreign key

Page 63: UKOUG, Oracle Transaction Locks

Summary: TX 4 from ASHSummary: TX 4 from ASHuniq index

ST EVENT SID LM P2 P3 OBJ OTYPE FN BLOCKN SQL_ID BSID

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

10:39 enq: TX - row lock c 141 4 655406 6672 -1 0 0 bjvx94vnxtxgv 158

10:39 enq: TX - row lock c 141 4 655406 6672 -1 0 0 bjvx94vnxtxgv 158

10:39 enq: TX - row lock c 141 4 655406 6672 -1 0 0 bjvx94vnxtxgv 158

10:39 enq: TX - row lock c 141 4 655406 6672 -1 0 0 bjvx94vnxtxgv 158

FK (10.2.0.3)

ST EVENT SID LM P2 P3 OBJ OTYPE FN BLOCKN SQL_ID BSID

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

10:41 enq: TX - row lock c 144 4 179681 7074 CHILD TABLE 1 60954 ahm7c9rupbz9r 1

10:41 enq: TX - row lock c 144 4 179681 7074 CHILD TABLE 1 60954 ahm7c9rupbz9r 1

10:41 enq: TX - row lock c 144 4 179681 7074 CHILD TABLE 1 60954 ahm7c9rupbz9r 1

bitmap

ST EVENT SID LM P2 P3 OBJ OTYPE FN BLOCKN SQL_ID BSID

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

10:41 enq: TX - row lock c 143 4 966081 4598 I1 INDEX 0 0 azav296xxqcjx 144

10:41 enq: TX - row lock c 143 4 966081 4598 I1 INDEX 0 0 azav296xxqcjx 144

10:41 enq: TX - row lock c 143 4 966081 4598 I1 INDEX 0 0 azav296xxqcjx 144

10:41 enq: TX - row lock c 143 4 966081 4598 I1 INDEX 0 0 azav296xxqcjx 144

Copyright 2006 Kyle Hailey

Mode 4, foreign key

Mode 4, unique key

Mode 4, bitmap

Page 64: UKOUG, Oracle Transaction Locks

SpecificSpecific ‘enq: TX’ ‘enq: TX’ wait events wait events • enq: TX - allocate ITL entryenq: TX - allocate ITL entry

– ITL space waits • enq: TX - index contention ?enq: TX - index contention ?

– Index block split • enq: TX – contention enq: TX – contention Unusual

– Alter tablespace … read only;– Free Lists slot waits– Possible with two phase commit

Page 65: UKOUG, Oracle Transaction Locks

enq: TX - allocate ITL entryenq: TX - allocate ITL entry

Data Block Data Block HeaderHeader

ITLITL

DataData

Data Block Data Block HeaderHeader

Transaction 1 InfoTransaction 1 Info

Transaction 2 InfoTransaction 2 Info

Page 66: UKOUG, Oracle Transaction Locks

enq: TX - allocate ITL entryenq: TX - allocate ITL entry

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 67: UKOUG, Oracle Transaction Locks

enq: TX - allocate ITL entryenq: 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 1: update itl set data=data where id=1; session 2: update itl set data=data where id=2; session 3: update itl set data=data where id=3; session 4: update itl set data=data where id=4; session 5: update itl set data=data where id=5;

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 1: update itl set data=data where id=1; session 2: update itl set data=data where id=2; session 3: update itl set data=data where id=3; session 4: update itl set data=data where id=4; session 5: update itl set data=data where id=5;

Page 68: UKOUG, Oracle Transaction Locks

enq: TX - contentionenq: TX - contention

1. Altering tablespace read only with open transactionExample– 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

Page 69: UKOUG, Oracle Transaction Locks

enq: TM - contentionenq: TM - contention

• TX locks have a corresponding TM lock• TM locks the structure from change

LOCK Parmeter1 Parmeter2(ID1) Parameter3(ID2)------- --------- ------------- --------------- enq: TM name|mode object # table/partition

LOCK Parmeter1 Parmeter2(ID1) Parameter3(ID2)------- --------- ------------- --------------- enq: TM name|mode object # table/partition

Parameter1 = object id

Page 70: UKOUG, Oracle Transaction Locks

enq: TM - contentionenq: TM - contention

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 parent values (2); commit;

delete from parent where id=2;

User 2

insert into child values (1,’a’);

Exclusive Row Level Lock

No commitNo commit

Page 71: UKOUG, Oracle Transaction Locks

enq: TM - contentionenq: TM - contentionParentParent ChildChild

IDID IDID NameNameValueValueIDID

PKPK

Delete from Parent where ID=2 :Delete from Parent where ID=2 :

11 11 11

Insert into Child ID=1Insert into Child ID=1

Enqueue TM 4 Enqueue TM 4

Session 2 doesnSession 2 doesn’’t know the value Session 1 insertedt know the value Session 1 insertedSession 2 only knows there is an outstanding changeSession 2 only knows there is an outstanding change

XX XX

Session 1Session 1

Session 2Session 2

22 22

Page 72: UKOUG, Oracle Transaction Locks

enq: TM – contention Solutionenq: TM – contention 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=2OK – can verify quickly in the child indexOK – can verify quickly in the child index

IDID

IndexIndex

Foreign KeyForeign Key

112211

Page 73: UKOUG, Oracle Transaction Locks

TM Further InvestigationTM Further Investigation

EVENT SQL_ID mode OBJEVENT SQL_ID mode OBJ

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

enq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILDenq: TM - contention 8zw36yw3fq4yy 4 53372 CHILD

selectselect event,event, sql_id,sql_id, mod(p1,16) as "mode",mod(p1,16) as "mode", p2|| ' ' || o.name objp2|| ' ' || o.name obj from v$active_session_history ash,from v$active_session_history ash, obj$ oobj$ o wherewhere event like 'enq: TM%'event like 'enq: TM%' and o.obj# (+)= ash.p2and o.obj# (+)= ash.p2 order by sample_time;order by sample_time;

Page 74: UKOUG, Oracle Transaction Locks

Other ResourcesOther Resources

@?/rdbms/admin/utllockt@?/rdbms/admin/utllockt

WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2WAITING_SESSION LOCK_TYPE MODE_REQUESTED MODE_HELD LOCK_ID1 LOCK_ID2

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

144 None144 None

139 Transaction Share Exclusive 131113 7507139 Transaction Share Exclusive 131113 7507

Page 75: UKOUG, Oracle Transaction Locks

Blocking SessionsBlocking Sessions

Page 76: UKOUG, Oracle Transaction Locks

ContentContentPart I : Row Locks

– enq: TX - row lock contentionenq: TX - row lock contention• Data in ASHData in ASH• OEMOEM• V$lock & v$sessionV$lock & v$session• ASHASH• Find blocking SQL Find blocking SQL

Part II : Row Locks ??– enq: TX - row lock contention, mode 4enq: TX - row lock contention, mode 4

Part III : Other TX locks– enq: TX - allocate ITL entry enq: TX - allocate ITL entry – enq: TX - index contention enq: TX - index contention – enq: TX – contention enq: TX – contention

Part IVPart IV : TM locks : TM locks

Page 77: UKOUG, Oracle Transaction Locks
Page 78: UKOUG, Oracle Transaction Locks

Without TM Locks

ChildChild

11223344……1000010000

IDID NameName

ChildChild

11223344……1000010000

IDID NameName

Delete Delete ParentParent

Delete Delete ParentParent

Delete Delete ChildChild

Could scan Child table for value “2” and Could scan Child table for value “2” and someone could come in behind and delete someone could come in behind and delete value 2 in an area that is already scanned value 2 in an area that is already scanned but before the scan has finished. The TM but before the scan has finished. The TM lock prevents this lock prevents this