Problema de bloqueos TM - Contention en Oracle Database 11gR2

En estos ya 15 días después de haber realizado la migración, han salido varios problemas que en la etapa de pruebas no fueron detectadas, una de ellas es que de pronto un simple DML (por ejemplo un INSERT) bloqueaba toda una tabla no permitiendo que otros usuarios tuvieran acceso ni con un SELECT a los registros.

Después de buscar un rato (resolviendo el problema mediante la muerte de la sesión del usuario problemático), la investigación me llevo a esta página http://www.confio.com/blog/solving-waits-on-enq-tm-contention , en la cual describen que para la versión 11g, si las FK's de una tabla no están indexadas, al insertar un valor bloquea todo el objeto y objetos relacionados.

La solución, como pueden deducir, es crear los índices, basado en ello construí este SQL que me permite obtener las instrucciones DDL para crearlos. Es necesario decir que ya con las sentencias me puse a revisar una a una para ver a que tablespace la debía dirigir ya que me heredaron una BD no muy organizada (esa es oooootro de mis proyectos, la reestructuración) y tenía que asignarlos a donde estaban los demás. En el caso de ustedes, si quieren evitarse esa talacha pueden hacer un tablespace nuevo para esos indices y ahí dirigirlos.


-- Solución a TM - Contention (Bloqueos masivos de tablas)
-- Indican en la página que al realizar un INSERT en una tabla con FK's
-- se hace un bloqueo completo de la tabla si estas columnas no están indexadas
-- Este script obtiene las columnas con FK pero que no están indexadas, dandonos
-- la sentencia con el SQL a ejecutar para crearlo
SELECT 'CREATE INDEX '||column_name||'_FK ON '||owner||'.'||table_name||'('||column_name||');'
FROM (
        SELECT c.owner owner, c.table_name table_name, cc.column_name, cc.position column_position
        FROM   dba_constraints c, dba_cons_columns cc
        WHERE  c.constraint_name = cc.constraint_name
        AND    c.constraint_type = 'R'
        and    c.owner='OWNER'
        MINUS
        SELECT i.owner, i.table_name, ic.column_name, ic.column_position
        FROM   dba_indexes i, dba_ind_columns ic
        WHERE  i.index_name = ic.index_name
        AND i.owner='OWNER'
)
ORDER BY table_name, column_position;


Ya con los índices creados, notarán que los bloqueos dejan de ser TM - EXCLUSIVE y pasan a TM - ROW EXCLUSIVE con su respectivo TX.

Se han realizado más modificaciones, y todas las iré incluyendo en este blog.

Actualización:

Esta situación ocurre con clientes no soportados según la matriz de certificación de Oracle, en mi caso el problema se presenta con clientes de la versión 8i. Ya que traté de reproducir el mismo error con clientes 10g - 11g y no se presentaron esos bloqueos aún cuando no se habían creado los índices para las llaves foráneas.

Otro tema es que si las FK corresponden a llaves combinadas, los índices deben crearse también combinados, esta consideración no se resuelve en la consulta mostrada anteriormente, por lo que en mi caso, tuve que realizarla manualmente en las tablas que seguían presentando el caso.