Bloqueos en BBDD

[Franky]

Buenas gente, 

Me han pedido que implemente una alarma cuando un proceso lleve bloqueando más de dos horas alguna tabla de BBDD (ha ocurrido esta noche y cuando hemos llegado a la oficina había un follón de procesos bastante importante)

Tengo un proceso montado para ver bloqueos que básicamente se basa en las dos querys que os pongo al final, y que que tiran de 

v$session

v$process

v$locked_object

v$sqlarea

(ya de paso las podéis usar, me son bastante útiles)

El tema es que para visionar online son muy útiles, pero para lo que quiero no demasiado...y estoy convencido de que en Oracle tiene que haber alguna manera bastante más sencilla y rápida de localizar bloqueos y montar una alarma de aviso inmediato.

¿Me echáis una mano, please? (yo por mi parte sigo investigando...por cierto, nosotros no somos administradores de la bd. Es decir, que no tenemos más herramientas que las consultas que podamos lanzar sobre ella. Aunque si hay alguna manera sencilla para el administrador comentadmelo, y les abrimos un ticket para que implemente ellos dicha alarma)

¡Gracias!

Ah, las queries:

       SELECT LTRIM(SUBSTR(NVL(s.username, '(oracle)') ,1 ,11)) as USERNAME,

       LTRIM(SUBSTR(s.osuser ,1 ,8)) as OSUSER,

       LTRIM(SUBSTR(TO_CHAR(s.sid) ,1 ,4)) as SID,

       LTRIM(SUBSTR(TO_CHAR(s.serial#) ,1 ,7)) as SERIAL#,

       LTRIM(SUBSTR(TO_CHAR(p.spid) ,1 ,5)) as SPID,

       LTRIM(SUBSTR(s.lockwait ,1 ,8)) as LOCKWAIT,

       LTRIM(SUBSTR(s.status ,1 ,8)) as STATUS,

       --s.module,

       --s.machine,

       LTRIM(SUBSTR(s.program ,1 ,20)) as PROGRAM,

       LTRIM(SUBSTR(TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') ,1 ,20)) as LOGON_TIME,

       LTRIM(SUBSTR(a.sql_text ,1 ,70)) as SQL

       FROM v$session s, v$process p, v$sqlarea a

       WHERE s.paddr = p.addr

       and s.sql_id=a.sql_id

       and (s.status = 'ACTIVE' or s.status = 'KILLED')

       ORDER BY s.username, s.osuser;

       SELECT LTRIM(SUBSTR(NVL(s.username, '(oracle)') ,1 ,11)) as USERNAME,

       LTRIM(SUBSTR(s.osuser ,1 ,8)) as OSUSER,

       LTRIM(SUBSTR(TO_CHAR(s.sid) ,1 ,4)) as SID,

       LTRIM(SUBSTR(TO_CHAR(s.serial#) ,1 ,7)) as SERIAL#,

       LTRIM(SUBSTR(TO_CHAR(p.spid) ,1 ,5)) as SPID,

       LTRIM(SUBSTR(s.lockwait ,1 ,8)) as LOCKWAIT,

       LTRIM(SUBSTR(s.status ,1 ,8)) as STATUS,

       --s.module,

       --s.machine,

       LTRIM(SUBSTR(s.program ,1 ,20)) as PROGRAM,

       LTRIM(SUBSTR(TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') ,1 ,20)) as LOGON_TIME,

       Decode(b.locked_mode, 0, 'None',

       1, 'Null (NULL)',

       2, 'Row-S (SS)',

       3, 'Row-X (SX)',

       4, 'Share (S)',

       5, 'S/Row-X (SSX)',

       6, 'Exclusive (X)',

       b.locked_mode) as LOCKED_MODE,

       LTRIM(SUBSTR(a.object_name ,1 ,57)) as OBJECT_NAME

       FROM v$session s, v$process p, dba_objects a, v$locked_object b

       WHERE s.paddr = p.addr

       and b.session_id=s.sid

       and a.object_id = b.object_id

       and (s.status = 'ACTIVE' or s.status = 'KILLED')

       ORDER BY s.username, s.osuser;

[Franky]

He visto que en v$session hay un campo "seconds_in_wait", pero no sé si es del todo fiable...

[JAP]

Lo es... del todo... pero te paso un par de consultas que pueden ser de ayuda.

Ahora, los scripts no son nada si no comprendes como funcionan, para poder comprender su funcionamiento te sugiero un test simple que puedes hacer abriendo varias sesiones en la bbdd y forzando tu los bloqueos con update, algo como

create table t (col1 number, col2 number);

insert into t (1,1);

commit;

aqui empiezas a meter sesiones que hagan updates a las dos columnas, otras a una columna, otras un delete, todas ellas sin commit... y vas lanzando los scripts a ver que vas viendo según haces commit o rollback de las cosas...

Suerte.

--
Has recibido este mensaje porque estás suscrito al grupo "FORO_DBA" de Grupos de Google.
Para anular la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a foro_dba+u...@googlegroups.com.
Para acceder a más opciones, visita https://groups.google.com/d/optout.

--

José Antonio de Pablo Jiménez
Principal IT Consultant & CTO
SYSCONFIG Gestión de Sistemas, SLU
www.sysconfig-gs.net
www.linkedin.com/company/sysconfig-gs

[Franky]

Gracias JAP.

Estoy con ellas y estudiando los mensajes de bloqueo que se generan...

Un saludo

[Franky]

Cojonudísmos tus sql JAP. 

Una tontería, (pero que me estaba volviendo loco porque no me daba resultados) he tenido que cambiar 

     

 "WHERE BBB.EVENT LIKE 'enqueu%' "

por 'enqu%', porque mi versión de BBDD se ve que no pone el literal enqueu.

Las voy a juntar en una para tener de una tacada el tiempo de espera del "Culpable".

¡Muchas gracias de nuevo!

[JAP]

De nada.

Lo del literal... en cada versión puede cambiar. Me alegro que te sirvan

Un saludo

[Alvaro I.]

Buenas, al hilo del correo de Franky, os queria comentar lo que nos paso hace poco con esto de los bloqueos de las tablas. El caso es que un dia nos empezaron a llegar alertas de una de las bbdd que se habia llegado al numero maximo de procesos y la carga de la maquina estaba cerca del 100%, esto hizo que el listener empezara da rechazar las conexiones :   

ORA-00020: maximum number of processes 250 exceeded
ORA-20 errors will not be written to the alert log for
 the next minute. Please look at trace files to see all
 the ORA-20 errors.

Nos pusimos a mirar y habia una sesion que estaba bloqueando una tabla, y las demas conexiones se fueron quedando en espera hasta que se llego al limite. Una vez hecho un kill de la sesion, se empezo a recuperar la bbdd y la maquina hasta los niveles normales. En las semanas siguientes, volvio a pasar y lo mismo, asi que ya nos pusimos a mirar mas en profundidad con desarrollo, todos los procesos y al final descubrimos que es por esto :

      Oracle recomienda indexar las FKs (foreign keys o claves foráneas)
      para evitar los bloqueos del tipo ”TM Lock”: si hacemos un DELETE de una fila de
      una tabla A cuya PK está siendo referenciada por alguna FK de otra
      tabla B, Oracle bloquea la tabla B completa (q tiene la FK), de
      modo que si otra sesión intenta actualizar (UPDATE, DELETE o
      INSERT) la tabla B (que tiene la FK), se quedará bloqueada
      (esperando a que la sesión que está haciendo el DELETE en la tabla
      A, que tiene la PK, termine). 

Asi que os recomiendo, no solo monitorizar los bloqueos, sino llegar a la raiz del asunto para que no vuelva a pasar.

Saludos,

[JAP]

Hablas como un DBA... Vamos q hablas con toda la razón del mundo 

[Franky]

En nuestro caso fue más fácil que todo eso. Un ilustre compañero, por algún motivo aún desconocido, hizo un update (y por supuesto sin commit) sobre una tabla de secuencias de proceso. Con lo cuál tooodos los procesos de la noche se quedaron bloqueados.

Como manera de boicotear a una empresa es genial :-)

[Alvaro I.]

...ya voy manejando el lenguaje de un autentico DBA, jejejej. En nuestro caso, una vez que el usuario desencadena el proceso, se ejecutan un monton de pl que hacen muchas operaciones de insert, update y delete.