jueves, 11 de febrero de 2016

Tratamiento del evento enq: TM - contention (por Alejandro Aguirrez)

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 .