Introducción
El evento "enq:
TM - contention" es un tipo de contención provocada indirectamente
mediante constraint asociadas a las tablas a las cuales se desea
modificar/agregar/borrar datos. Esta tipo de contención se produce cuando entre
dos tablas TA y TB existe una FK que las asocia
y en ellas se ejecuta una dml en el mismo periodo de tiempo basicamente.
Para minimizar
esta espera las columnas de las FK se recomienda que esten indexadas (Es una
buena practica). En el siguiente ejemplo proporcionado por METALINK se denotara
el comportamiento con y sin indices de sentencias del tipo DML y como funciona
el mecanismo de lockeo para mantener la integridad de los datos.
Marco Teórico de Lock:
- RS = Row Share wait - also known as
sub-share table lock (SS)
- RX = Row Exclusive wait - also known as
sub-exclusive table lock (SX)
- S = Share mode wait.
- SRX = Share Row Exclusive wait - also
known as share-subexclusive table lock (SSX)
- X = eXclusive
mode wait
Ejemplo
1 - Genero las
dos tablas en el esquema SCOTT
CREATE TABLE
DEPT
(
deptno NUMBER CONSTRAINT pk_dept PRIMARY
KEY,
dname
VARCHAR2(10)
);
CREATE TABLE
EMP
(
deptno NUMBER(2) CONSTRAINT fk_deptno
REFERENCES dept(deptno),
ename VARCHAR2(20)
);
Generamos un
Foreing Key en la tabla EMP que apunta a la tabla DEPT
EMP=Child Table (object_id= 13035)
DEPT=Parent Table (object_id= 13033)
2 - Sin Indices
en la columma de la FK
a) DML sobre la Child Table (EMP):
SQL> INSERT INTO DEPT VALUES (1, 'COSTCENTER');
SQL>
COMMIT;
SQL>
INSERT INTO EMP VALUES (1, 'SCOTT');
SQL>
SELECT sid, type, id1,
id2,
lmode,
request, block
FROM
v$lock
WHERE sid
IN
(SELECT sid FROM v$session WHERE audsid =
userenv('SESSIONID')
);
SID TY ID1 ID2 LMODE REQUEST
BLOCK
----------
-- ---------- ---------- ---------- ---------- ----------
15 TX 589839
56552 6 0 0
15 TM 13035 0 3
0 0
15 TM 13033 0 3 0 0
Un lockeo del
tipo "row exclusive" (LMODE=3) es necesario en ambas tablas.
b) DML sobre la Parent Table (DEPT):
SQL> update dept set deptno = 1; <
SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
15 TX 589839 56552 6 0 0
15 TM 13033 0 3 0 0
Un lockeo
"Row Exclusive" (LMODE=3) es necesario en la parent table solamente.
3 - Con Indices en la columma de la FK en la
Child Table
SQL> create index ind_emp on emp (deptno, ename);
a) DML sobre la Child Table (EMP):
SQL> insert into DEPT values (1, 'COSTCENTER');
SQL> commit;
SQL> insert into EMP values (1, 'SCOTT');
SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
15 TX 393232 54853 6 0 0
15 TM 13035 0 3 0 0
15 TM 13033 0 2 0 0
Un lockeo del
tipo "row share"(LMODE=2) es requerido en la parent table mientras
que un lockeo "row exclusive" (LMODE=3)es necesario en la Child Table.
b) DML sobre la Parent Table (DEPT):
SQL> update DEPT set deptno = 1;
1 row updated.
SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));
SID TY ID1 ID2 LMODE REQUEST BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
15 TX 589839 56552 6 0 0
15 TM 13035 0 2 0 0
15 TM 13033 0 3 0 0
Un lockeo
"Row Exclusive" (LMODE=3) es necesario en la parent table mientras
que en la child tables es necesario un lockeo del tipo "row shared".
Como se observa
el mecanismo de lockeo es mas laxo con indices .
Conclusiones/Observaciones
Ahora bien la teoría
y las buenas prácticas no siempre son aplicables segun cada negacio. Por
ejemplo en alguna aplicaciones donde me toco trabajar crear los indices en
todas las FK implicaba hacer crecer el storage de la base un 150% y con esto
caeriamos en lo que llamamos "SobreIndexacion". Entonces debemos
o no aplicar esta recomendación (las columnas de la FK deben estar
indexadas)? Si ,pero siempre y cuando
sea viable.
En las tablas
Core de consultas y donde se ralizan DML online (sentencias cortas y con muchas
ejecuciones por hora) se debe cumplir con la recomendación de Oracle de generar los
indices sobre las FK. Por que pensemos el caso de validación de la
constraint en un insert sobre la tabla
child debe bloquear la tabla PARENT y si
la tabla PARENT esta siendo actualizada en el campo a validar tenemos que esperar
que esta ultima haga commit/rollback para poder continuar por que necesita
mantener la integridad configurada por la FK . Imaginemos esto en un ambiente
donde se empiecen a encolar rapidamente las sentencias mas atomicas (10ms >
Tiempos de ejecucion >1 ms y mas de 300 mil ejecuciones por hora ) esto
provocaria un encolamiento acumulativo que provocara una degradacion de la
aplicacion
Bueno habiendo
dado esta contextualizacion de la nota les comparto script que modifique para chequear los
posibles lockeos por enq:TM - Contention.
script
SET ECHO off
REM NAME:
TFSFKCHLK.SQL
REM
USAGE:"@path/tfsfkchk"
REM
--------------------------------------------------------------------------
REM
REQUIREMENTS:
REM None --
checks only the USER_ views
REM
--------------------------------------------------------------------------
REM This
file checks the current users Foreign Keys to make sure of the
REM
following:
REM
REM 1) All
the FK columns are have indexes to prevent a possible locking
REM problem
that can slow down the database.
REM
REM 2)
Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem
the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If
the script finds and miss match the script reports the correct
REM order of
columns that need to be added to prevent the locking
REM problem.
REM
REM
REM
REM
-------------------------------------------------------------------------
REM Main
text of script follows:
drop table
ck_log;
create table
ck_log (
LineNum
number,
LineMsg
varchar2(2000));
declare
t_CONSTRAINT_TYPE
user_constraints.CONSTRAINT_TYPE%type;
t_CONSTRAINT_NAME
USER_CONSTRAINTS.CONSTRAINT_NAME%type;
t_TABLE_NAME
USER_CONSTRAINTS.TABLE_NAME%type;
t_R_CONSTRAINT_NAME
USER_CONSTRAINTS.R_CONSTRAINT_NAME%type;
tt_CONSTRAINT_NAME
USER_CONS_COLUMNS.CONSTRAINT_NAME%type;
tt_TABLE_NAME
USER_CONS_COLUMNS.TABLE_NAME%type;
tt_COLUMN_NAME
USER_CONS_COLUMNS.COLUMN_NAME%type;
tt_POSITION
USER_CONS_COLUMNS.POSITION%type;
tt_Dummy
number;
tt_dummyChar
varchar2(2000);
l_Cons_Found_Flag
VarChar2(1);
Err_TABLE_NAME
USER_CONSTRAINTS.TABLE_NAME%type;
Err_COLUMN_NAME
USER_CONS_COLUMNS.COLUMN_NAME%type;
Err_POSITION
USER_CONS_COLUMNS.POSITION%type;
tLineNum
number;
cursor UserTabs
is
select
table_name
from
dba_tables where owner='SCOTT'
--and
table_name not in
('ADEADMS','ADEAMX_DEFINITION','ASIGNA_PERSONERIA_TIPO')
order by
table_name;
cursor
TableCons is
select
CONSTRAINT_TYPE,
CONSTRAINT_NAME,
R_CONSTRAINT_NAME
from
dba_constraints
where OWNER
= 'SCOTT'
and
table_name = t_Table_Name
and
CONSTRAINT_TYPE = 'R'
order by
TABLE_NAME, CONSTRAINT_NAME;
cursor
ConColumns is
select
CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
from
dba_cons_columns
where OWNER
= 'SCOTT'
and
CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by
POSITION;
cursor
IndexColumns is
select
TABLE_NAME,
COLUMN_NAME,
POSITION
from
dba_cons_columns
where OWNER
= 'SCOTT'
and
CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by
POSITION;
DebugLevel
number := 99; -- >>> 99 = dump all info`
DebugFlag
varchar(1) := 'N'; -- Turn Debugging on
t_Error_Found
varchar(1);
begin
tLineNum :=
1000;
open
UserTabs;
LOOP
Fetch
UserTabs into t_TABLE_NAME;
t_Error_Found
:= 'N';
exit when
UserTabs%NOTFOUND;
-- Log
current table
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
NULL );
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Checking Table '||t_Table_Name);
l_Cons_Found_Flag
:= 'N';
open
TableCons;
LOOP
FETCH
TableCons INTO t_CONSTRAINT_TYPE,
t_CONSTRAINT_NAME,
t_R_CONSTRAINT_NAME;
exit when
TableCons%NOTFOUND;
if (
DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Found CONSTRAINT_NAME = '|| t_CONSTRAINT_NAME);
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Found CONSTRAINT_TYPE = '|| t_CONSTRAINT_TYPE);
tLineNum := tLineNum
+ 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Found R_CONSTRAINT_NAME = '|| t_R_CONSTRAINT_NAME);
commit;
end;
end if;
open
ConColumns;
LOOP
FETCH
ConColumns INTO
tt_CONSTRAINT_NAME,
tt_TABLE_NAME,
tt_COLUMN_NAME,
tt_POSITION;
exit when
ConColumns%NOTFOUND;
if (
DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
NULL );
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Found CONSTRAINT_NAME = '|| tt_CONSTRAINT_NAME);
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Found TABLE_NAME = '|| tt_TABLE_NAME);
tLineNum :=
tLineNum + 1;
insert into ck_log
( LineNum, LineMsg ) values
( tLineNum,
'Found COLUMN_NAME = '|| tt_COLUMN_NAME);
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Found POSITION = '|| tt_POSITION);
commit;
end;
end if;
begin
select 1
into tt_Dummy
from
dba_ind_columns
where
TABLE_NAME = tt_TABLE_NAME
and
COLUMN_NAME = tt_COLUMN_NAME
and
COLUMN_POSITION = tt_POSITION and table_owner='SCOTT';
if (
DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Row Has matching Index' );
end;
end if;
exception
when
Too_Many_Rows then
if (
DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Row Has matching Index' );
end;
end if;
when
no_data_found then
if (
DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'NO MATCH FOUND' );
commit;
end;
end if;
t_Error_Found
:= 'Y';
select
distinct TABLE_NAME
into
tt_dummyChar
from
dba_cons_columns
where OWNER
= 'SCOTT'
and
CONSTRAINT_NAME = t_R_CONSTRAINT_NAME;
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
( tLineNum,
'Changing data in table '||tt_dummyChar
||' will
lock table ' ||tt_TABLE_NAME);
commit;
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
(
tLineNum,'Create an index on table '||tt_TABLE_NAME
||' with the
following columns to remove lock problem');
open
IndexColumns ;
loop
Fetch
IndexColumns into Err_TABLE_NAME,
Err_COLUMN_NAME,
Err_POSITION;
exit when
IndexColumns%NotFound;
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
(
tLineNum,'Column = '||Err_COLUMN_NAME||' ('||Err_POSITION||')');
end loop;
close
IndexColumns;
end;
end loop;
commit;
close
ConColumns;
end loop;
if (
t_Error_Found = 'N' )
then
begin
tLineNum :=
tLineNum + 1;
insert into
ck_log ( LineNum, LineMsg ) values
(
tLineNum,'No foreign key errors found');
end;
end if;
commit;
close
TableCons;
end loop;
commit;
end;
/
Se debe
reemplazar el esquema 'SCOTT' por el que se quiera analizar
La salida del
mismo puede chequearse con la siguiente sentencia
select
linenum,LineMsg
from ck_log
where
LineMsg NOT LIKE 'Checking%' AND
LineMsg NOT
LIKE 'No foreign key%'
order by LineNum
Para relevar cual
es el objeto con mayores esperas por enq:TM - Contention ejecutar lo siguiente.
select nvl(event,'ON
CPU'),sql_id,object_name,object_type, count(*)
total_wait_time,round((count(*))/(sum(count(*)) over() ) *100,2)
pct,sql_plan_hash_value
from
DBA_HIST_active_sess_history a,dba_objects o
where
a.sample_time >= to_date('01/01/2015 07:30','DD/MM/YYYY HH24:MI')
and
a.sample_time >= to_date('25/01/2016 18:30','DD/MM/YYYY HH24:MI')
and event
like 'enq:%TM%'
and
o.object_id=a.current_obj#
group by
event,sql_id,sql_plan_hash_value,object_name,object_type
order by
total_wait_time desc;
En base a la
salida de la consulta anterior podemos tener el objeto que mas contencion del
tipo TM esta generando para luego buscarlo en la salida del script (recomiendo
tener la salida del script en un excel para facilitar la busqueda).
Espero que la
nota les haya sido de utilidad .
.JPG)