enq: TM Contention Wait Events

Hi all, Missing index on the Foreign Key column of the Child table is the first cause of TM Contention Wait Events. Other one is may be Parallel DML.

We first look at the first “missing index” case. Lets create environment for the test case. We need one parent table called “Cities” and a child one called “Districts”.

CREATE TABLE CITIES
(
CITY_ID     NUMBER,
CITY_NAME   VARCHAR2 (20),
CONSTRAINT CITIES_PK PRIMARY KEY (CITY_ID)
);

CREATE TABLE DISTRICTS
(
DISTRICT_ID     NUMBER,
CITY_ID         NUMBER,
DISTRICT_NAME   VARCHAR2 (30),
CONSTRAINT DISTRICTS_PK PRIMARY KEY (DISTRICT_ID),
CONSTRAINT CITY_FK FOREIGN KEY (CITY_ID) REFERENCES CITIES (CITY_ID)
);

Lets INSERT SOME rows.

INSERT INTO CITIES
VALUES (1, 'Istanbul');

INSERT INTO CITIES
VALUES (2, 'Hatay');

INSERT INTO CITIES
VALUES (3, 'Ankara');

COMMIT;

INSERT INTO DISTRICTS
VALUES (1, 1, 'Kadikoy');

INSERT INTO DISTRICTS
VALUES (2, 1, 'Basiktas');

INSERT INTO DISTRICTS
VALUES (3, 2, 'Antakya');

INSERT INTO DISTRICTS
VALUES (4, 2, 'Defne');

COMMIT;

Now we can run some DMLs for reproduce this wait event. Session 1 inserts a new district into the DISTRICTS table running this dml :

SQL> insert into districts values (6,1,'Sisli');

1 row created.

Session 2 updates Primary Key column of the CITIES table running this dml :

SQL> update cities set city_id=4,city_name='Adana' where city_id=3;

Lets have a look to the blockers :

SQL> set lines 500

SQL> r

1  SELECT sid,

2        sql_id,

3        blocking_session,

4        event,

5        p2

6    FROM v$session

7   WHERE blocking_session IS NOT NULL

8  UNION ALL

9  SELECT sid,

10        sql_id,

11        blocking_session,

12        event,

13        p2

14    FROM v$session

15*  WHERE sid IN (SELECT blocking_session FROM v$session)

SID SQL_ID         BLOCKING_SESSION  EVENT                 P2

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

147 6tadxuk2a86fn  26                 enq: TM - contention 

58382 26                              SQL*Net msg frm clie  1

As you see these two tables are not huge tables. I mean it is size independent. Problem is uncommitted transaction and missing index as well. Lets create an index for solving problem on DISTRICTS table column (CITY_ID)

CREATE INDEX city_id_ndx ON DISTRICTS (CITY_ID);

Conclusion : Bad design (Missing index on the FK column) and uncommitted transactions are the main cause of this wait.

For more reading :

Doc ID 1905174.1

http://www.confio.com/logicalread/solving-oracle-enq-tm-contention-wait-events/#.U8ZIyqjjcsk

http://www.dbspecialists.com/blog/database-maintenance/unindexed-foreign-keys-enqtm-contention/

User Based Redo Generation Stats

Hi,

V$SESSTAT is a powerful view that has more than 600 counters for every sessions. Class 2 counters are about Redo in this view and you can query which session is writing huge Redo.

There is a query which shows the Number of times a redo entry is copied into the redo log buffer.

select sum(s.value) NumberOfTimesCoppiedLogBuffer,
sn.username,
sq.sql_id
from v$sesstat s,
v$session sn,
v$statname n,
v$sql sq
where     n.statistic# = s.statistic#
and sn.sid = s.sid
and n.name like '%redo entries%'
and sn.type = 'USER'
and sn.sql_id = sq.sql_id
and sq.parsing_schema_name = sn.username
group by sq.sql_id,sn.username
order by NumberOfTimesCoppiedLogBuffer desc;

If you want to use these counters in your performance analysis maybe you want to have a look

Statistics Descriptions.

Have a nice day.