Oracle: Enqueues

Part I: Intro

Information we need to solve locking issues :

1. Waiter

2. Blocker

3. Lock Type

type
mode

4. Object blocking on

5. Blocking SQL is Missing (Possibly with log miner)

ASH data

Waiter

SESSION_ID

SESSION_SERIAL#

USER_ID

Object

CURRENT_OBJ#

CURRENT_FILE#

CURRENT_BLOCK#

SQL Waiting

SQL_ID

Blocker (as of 10gR2)

BLOCKING_SESSION

BLOCKING_SESSION_STATUS

BLOCKING_SESSION_SERIAL#

Lock Type and Mode

Event = Type (name)

P1 = Type | Mode

Lock Mode and Name

Select parameter1 from v$event_name where name=‘enqueue’;

Parameter1

----------

Name|mode

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

P1 P1RAW

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

1415053318 54580006

Type = 5458

Mode = 0006

Hex Decimal ASCII

54 = 84 = "T"

58 = 88 = "X"

Lock = TX 6

Wrapping this information up in one query we get:

SELECT

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

chr(bitand(p1, 16711680)/65535) Type,

mod(p1,16) lmode

from v$active_session_history

where event like 'enq%';

TY	LMODE
HW	6
RO	6
JS	6

Notice that the query uses "v$active_session_history" but could also be used on v$session starting in 10g, or v$session_wait since version 7.

9i (and below)

One Wait : 'enqueue'

had to decode name and mode

10g

208 enqueue waits

Specific to each type of enqueue

No longer need to decode name

but the mode can still be important to decode

For example, what was called an "enqueue" wait before 10g is now broken out like

enq: HW - contention	Configuration
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: TX – contention	Application

Lock 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

In our analysis of locks and waits we are going to talk mainly about share locks, mode 4 and exclusive locks, mode 6. Since version 10g wait event names now have the name of the lock there is no longer a need to translate the name and we can just concentrate on translating the mode:

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

   EVENT                                 P1 mode
   ----------------------------- ---------- ----
   enq: TX - allocate ITL entry  1415053316    4
   enq: TX - row lock contention 1415053318    6
   enq: TX - row lock contention 1415053316    4

Mining ASH

         TX locks

   /*
   col file# for 99999
   col block# for 999999
   col obj for a18
   col type for a9
   col lm for 99
   col bsid for 9999
   */

   select        count(*) cnt, 
                 session_id sid,
                 substr(event,1,30) event, 
                 mod(p1,16)  as lm,
                 sql_id,
                 CURRENT_OBJ# || ' ' || object_name obj
               , o.object_type type
               , CURRENT_FILE# file#
               , CURRENT_BLOCK#  block#
               , blocking_session bsid
    from v$active_session_history ash,
         all_objects o
    where
            event  like 'enq: T%'
      and o.object_id (+)= ash.current_obj#
   group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION
   order by  count(*)
   /

   cnt SID EVENT               LM SQL_ID        OBJ TYPE  F# BLOCK#  BSID
   --- --- ------------------- -- ------------- --- ----- -- ------- ----
   307 127 enq: TX - row lock   6 6yzrt1vdqrfn1 FOO TABLE  6  10541  148
   307 128 enq: TX - row lock   6 6yzrt1vdqrfn1 FOO TABLE  6  10541  148
   307 125 enq: TX - row lock   6 6yzrt1vdqrfn1 FOO TABLE  6  10541  148
   307 126 enq: TX - row lock   6 6yzrt1vdqrfn1 FOO TABLE  6  10541  148

         TXI, like above but get index type as well, if an index is involved in the lock

   /*
   col file# for 99999
   col block# for 999999
   col obj for a18
   col type for a15
   */
   select    substr(event,1,30) event, sql_id,
             CURRENT_OBJ# || ' ' || object_name obj
           , o.object_type type
           , CURRENT_FILE# file#
           , CURRENT_BLOCK#  block#
    from v$active_session_history ash,
             ( select a.object_name, 
                      a.object_id,
                      decode(a.object_type,'INDEX',i.index_type||' '||'INDEX',a.object_type) object_type
               from all_objects a, all_indexes i where 
               a.owner=i.owner(+) and a.object_name=i.index_name(+) ) o
    where
            event  like 'enq: TX%'
      and o.object_id (+)= ash.current_obj#
    order by sample_time
   /

Part II: User Locks

TX - Transaction Lock

Mode 6: Modifying same row

Mode 4: several reasons

TM - Table Modification

Mode 4: Unindexed Foreign Key

UL - User Lock

How a TX lock happens

TX = Transaction = Wait on UNDO

Mode 6 (exclusive)

modification of a row lock

Mode 4 (share)

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

TX mode 6 is straight forward, just two sessions trying to change the same data

TX mode 4 has many reasons and historically has been hard to diagnose, but now in 10g Oracle had broken down TX locks in to these sub types:

enq: TX - row lock contention

o pk or unique index violation

o fk violation

o bitmap chunk wait

o others ?

enq: TX - allocate ITL entry

o Wait on an ITL slot

enq: TX - index contention

o Index block split

enq: TX - contention

o Wait for a data file extension

o Alter tbs read only

o others?

(NOTE: that "enq: TX - row lock contention" can happen in both mode 6, the typical case, or mode 4 which is shown above)

enq: TX - row lock contention , mode 6

User1	User 2
delete from toto where id =1;
	delete from toto where id=1; --> wait till Use 1 commits or rollbacks

enq: TX - row lock contention , mode 4

Mode 4, happens for 3 reasons

1. Unique key contention

2. Foreign Key contention

3. Bitmap index contention

(others?)

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(2);
	insert into p values (2);

enq: TX - row lock contention, mode 4, foreign key

user 1	user 2
create table parent ( id number primary key); create table child ( id number references parent, name varchar2(20));
insert into p values(2);
	insert into child values (2,88);

enq: TX - row lock contention, mode 4, bitmap indexes

Bitmaps are compressed

Changes to the same bitmap cause locks

user 1	user 2
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);
update t1 set n1 = 2 where n2 = 12;
	update t1 set n1 = 2 where n2 = 13;

Different rows but same key value.

Three types of TX row contention mode 4 as seen from ASH data:

   Unique 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

   Foreign Key  (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 Index
   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

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 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;

enq: TX - index contention

   Seems to be caused by index block splits.
   see http://sites.google.com/site/embtdbo/wait-event-documentation/oracle-enq-tx---index-contention
   for some ideas on how to generate this wait to look at it more

enq: TX - contention

"enq: TX - contention" seems to be the trash bucket for all other TX contention issues and seems to be used when sessions are waiting on other sessions who are blocked some issues:

Example

Data File Extension – waiter waiting for another session to extend file

Index Block Split – waiter waiting for another session to split the block

Possibilities

Setting Table space read only

- Session 1 – start transaction, don’t commit
- Session 2 – alter tablespace read only

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

enq: TM - contention

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

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

(1) To many truncate requests

(2) Unindexed FKs

(3) Bitmap indexes on the table (low possibility; but possibility nonetheless)

- Anup Nanda

(4) direct mode insert, or insert /*+ append */ which will take TM enqueue in exclusive mode. If the transaction doesn't commit, you could see a whole bunch of sessions stack up behind it. - Mark Bobak

enq: UL - contention

Part III: Internal locks

.

enq: CF - contention

see bug 7516169 - SLOW INSERT TO PARENT TABLE WHEN CHILD IS REFERENCE PARTITIONED base bug 7147650

Mark Bobak: Event 10359, set to level 1, stops update of the control file with invalidation redo is stopped. See MetaLink Doc ID 1058851.6 for more details. (And yes, Steve's website also discusses it.) . from http://www.saptechies.com/faq-oracle-enqueues/ :

TYPE = CF

CF enqueues are control file enqueues, which occur during parallel access to the control file. Actions that make it necessary to access the control file can be for example, BEGIN BACKUP, redolog archiving by an ARCH process, or a logfile switch by the LGWR process. If a CF enqueue is requested within a period of 15 minutes without success, ORA-00600 [2103] occurs, and the Oracle instance may terminate (see Note 658744)..

In "checkpoint not complete" situations (Note 79341), the LGWR process may wait for a long time for the CF enqueue. This is a follow-on problem. The "checkpoint not complete" situations that cause this problem must be corrected (Note 793113).

See Note 658744, which contains possible causes and solutions for CF enqueues and the ORA-00600 [2103] that are triggered by it.

CF enqueue waits may also occur during an Archiver stuck (Note 391) since in such cases the ARCH process may hold the CF enqueue for a long time. In this case, CF enqueue are only a follow-on problem; after you solve the Archiver stuck situation, the enqueue waits will also disappear

enq: CI - Cross Instance

Indicative of high incremental checkpointing and waiting on blocks being checkpinted - increase fast_start_mttr_target

enq: HW - contention

. High Water- contention on moving up the high water mark - move object to ASSM or add freelists . .

enq: KO - fast object checkpoint

. used by PQO to clear out any relavent information from the cache before doing a direct read on the objects . .

enq: RO - fast object reuse

reusable object- clearing cache for object drop/reuse, use gtt if possible, tune DBWR

enq: SQ - contention

. . . .

enq: ST - contention