Enq: TM – Contention

This morning we held locks in a database, as detailed below;

SID            EVENT
_______ ________________
282           SQL*Net message to client
163           enq: TM – contention
284           enq: TM – contention
217           enq: TM – contention
237           enq: TM – contention
286           enq: TM – contention
181           db file sequential read
428          db file sequential read
219          db file sequential read
192          enq: TX – row lock conten
231          enq: TM – contention
404          enq: TM – contention

 

The locks are the type enq: TM – contention that, normally, are about referential integrity, about indexing in the child table columns that go against the father. I do not agree with create indexes on FK, of course, many authors and publications that recommend always create indexes on FK. I think in large or huge databases with thousands of tables is costly in CPU time, complexity of management and space, although hardware is becoming cheaper. I preffer just create index on FK when they start giving problems.

 

In the present case shows that the following statement wait in the event enq: TM – contention;

INSERT INTO LIBRO
(
COD_EJEMPLAR,
COD_MEDIO_ALMACENAMIENTO,
COD_ISBN,
TITULO,
TCIN,
TCOUT )
VALUES
(
:V001 ,
:V002 ,
:V003 ,
:V004 ,
:V005 ,
:V006 )

 

If we see the FK for this table;

select constraint_name, constraint_type, r_constraint_name
from dba_constraints where table_name =’LIBRO’
and constraint_type =’R’;

CONSTRAINT_NAME         C  R_CONSTRAINT_NAME
____________________ _ __________________
LIBRO_EDITORIAL_FK      R EDITORIAL_PK

 

Now, on parent table we look for indexed columns;

select column_name, column_position
from dba_ind_columns where index_name =’EDITORIAL_PK’;

COLUMN_NAME                                COLUMN_POSITION
__________________________ _______________
COD_EJEMPLAR                                 1
COD_MEDIO_ALMACENAMIENTO  2

 

 

The solution will be create an index on child table about these columns;

create index fast_fk
on libro (cod_ejemplar, cod_medio_almacenamiento)
tablespace editorial_assm_inx
nologging
parallel 5
/

 

HTH – Antonio NAVARRO

 

 

 

 

 

 

 

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s