Sesiones Bloqueadas en Oracle

REVISAR SESIONES BLOQUEDAS EN ORACLE

Muchas veces debido a la codificación de nuestras aplicaciones se presentan casos en los que un segundo usuario se queda esperando mucho tiempo a que finalice una transacción porque un primer usuario ya tiene bloqueada una o varias tablas que el segundo requiere.

¿Cómo saber que sesiones están bloqueando y cuales están esperando?

1. Qué usuarios están creando bloqueos?

SELECT Session_Id, Lock_Type, Mode_Held

  FROM Dba_Locks

 WHERE Blocking_Others = 'Blocking';

Con esta sentencia sabré el Id de la sesión que me está ocasionando un bloqueo. Pero quizás quiera saber también quienes están esperando. Los resultado de esta consulta son algo así:

Esto quiere decir que la sesión 171 tiene elementos bloqueados

2. Qué usuarios están esperando a que otra sesion desbloquee recuersos?

SELECT * FROM Dba_Waiters;

Los resultado de esta consulta son como este:

Me muestra que la sesión 69 está esperando a que la 171 libere algún recurso.

Sin embargo estos resultados me dicen poco, tendría que ingresar a ver las sesiones para saber quiénes son estos usuarios y qué están haciendo, pero podemos mejorar el query

3. Qué recursos están usando quienes bloquean y esperan?

SELECT l.Sid, l.Id1, l.Id2, l.Lmode, o.Owner Object_Owner, o.Object_Name,

       s.Username, s.Osuser, s.Machine, s.Wait_Class, s.Seconds_In_Wait

  FROM Sys.All_Objects o, V$lock l, V$session s, Dba_Waiters d

 WHERE (l.Sid = d.Waiting_Session OR l.Sid = d.Holding_Session)

   AND l.Type = 'TM'

   AND o.Object_Id = l.Id1

   AND l.Sid = s.Sid

   ORDER BY l.Id1;

El query nos dará este resultado:

En la columna Object_Name nos dice cual es el recurso usado o esperado, como está ordenado por el número de apariciones en el resultado, podemos saber cuáles son las que están bloqueadas. El OSUSER es el usuario de sistema operativo y Machine el nombre del equipo con el que el usuario se logueó, así podremos identificar mejor a los actores en este asunto. Seconds_in_Wait me dice el tiempo que lleva esperando (el que está en espera) y la última columna es simplemente la cuenta con la que estoy ordenando el resultado.

Una vez finalizada alguna de las dos sesiones en conflicto el resultado de los 3 querys debe estar vacio.

Espero les ayude.