Consultas para obtener información de bloqueos de Objetos e Interbloqueos de Usuarios

Hay ocasiones que el abrir tu Console Manager para resolver un bloqueo o un interbloqueo, puedes mejor ejecutar unas sentencias para obtener la información que necesitas y así tomar las medidas que sean pertinentes.

Dejo las consultas que hasta ahora tengo para obtener esa información

-- Consulta de Bloqueo de Objetos por Sesiones
select substr(a.os_user_name,1,8) "OS User"
, substr(b.object_name,1,30) "Object Name"
, substr(b.object_type,1,8) "Type"
, substr(c.segment_name,1,10) "RBS"
, e.process "PROCESS"
, substr(d.used_urec,1,8) "# of Records"
, e.sid
, e.serial#
, e.username
, p.spid
from v$locked_object a
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session e
, v$process p
where a.object_id = b.object_id
and a.xidusn = c.segment_id
and a.xidusn = d.xidusn
and a.xidslot = d.xidslot
and d.addr = e.taddr
and p.addr = e.paddr;

-- Consulta de Objetos Bloqueados por usuarios
select
l.oracle_username,
l.session_id,
l.os_user_name,
o.owner,
o.object_name,
dl.lock_type,
dl.mode_held
from v$locked_object l, dba_objects o, dba_locks dl
where o.object_id = l.object_id
and dl.session_id = l.session_id;

--Consulta para Interbloqueos
select lock1.sid, ' BLOQUEA ', lock2.sid
from v$lock lock1, v$lock lock2
where lock1.block =1 and lock2.request > 0
and lock1.id1=lock2.id1
and lock1.id2=lock2.id2;


-- Consulta de SPID
select v.sid, a.SPID, v.serial#, v.username, v.OSUSER, v.program, v.MACHINE, v.LOGON_TIME
from dba_users u, v$session v, v$process a
where v.user# = u.user_id
and v.paddr = a.addr;

-- Consulta sesiones pendientes de rollback o commit
SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Matar sesiones a nivel Sistema Operativo (Windows)

A veces nos encontramos con que mandamos a matar una sesión y esta queda marcada para "eliminar", y ahí persiste.

Podemos eliminar la sesión a nivel sistema operativo (matar el proceso de SO de la sesión) de la siguiente forma:

Primero, verificar que la sesión no esté haciendo operaciones de rollback

SELECT s.username,
       s.sid,
       s.serial#,
       t.used_ublk,
       t.used_urec,
       rs.segment_name,
       r.rssize,
       r.status
FROM   v$transaction t,
       v$session s,
       v$rollstat r,
       dba_rollback_segs rs
WHERE  s.saddr = t.ses_addr
AND    t.xidusn = r.usn
AND    rs.segment_id = t.xidusn
ORDER BY t.used_ublk DESC;

Si el campo used_urec tiene una cantidad distinta de cero y esta va decreciendo, hay que esperar para que termine de hacer rollback a sus operaciones.

Ya cuando no tenga operaciones de rollback pendientes (no aparecera como resultado del query), se procede a obtener el SPID (Server Process ID) de la sesión con la siguiente consulta:


select v.sid, a.SPID, v.serial#, v.username, v.OSUSER, v.program, v.MACHINE, v.LOGON_TIME
from user_users u, v$session v, v$process a
where v.user# = u.user_id
and v.paddr = a.addr;

Ya con el SPID y el nombre de la instancia de Base de Datos (que se obtiene con esto select sys_context('USERENV','DB_NAME') as Instance from dual;) se procede a ejecutar el comando ORAKILL desde una consola de MSDOS en el servidor.

El comando ORAKILL pide como parametros el nombre de la instancia y el SPID de la sesión a eliminar.

El comando quedaría de la siguiente manera:

C:\>orakill MiBaseDeDatos 7559

Al ejecutarse con éxito, muestra el siguiente mensaje


Kill of thread id  7559 in instance  MiBaseDeDatos  successfully signalled.

Fuentes:
http://cajondesastreoracle.wordpress.com/scripts-sql-curiosos/session_undo-sql/
http://vitocosan.wordpress.com/2009/06/03/orakill-matar-una-conexion-activa-de-oracle/

SQL para conocer el tamaño de los Tablespaces

En las actividades de monitoreo, uno de los puntos es revisar los tablespaces de la Base de Datos, para las BD Oracle, esto es importante ya que todos aquellos que no tienen una asignación automática de espacio, en caso de que se llene, comienzan los errores y en cierto punto una caída de la BD


También es útil conocer este dato, para llevar un estadístico de crecimiento, de forma que pueda uno solicitar los soportes (discos duros) o infraestructura necesaria tomando como referencia dicha estadistica.


El query que nos permitirá conocer estos datos es el siguiente:


SELECT trunc(sysdate) FECHA,
df.tablespace_name TABLESPACE,
df.total_space_mb "TOTAL MB",
(df.total_space_mb - fs.free_space_mb) "UTILIZADO MB",
fs.free_space_mb "LIBRE MB",
ROUND(100 * (fs.free_space / df.total_space),2) "% LIBRE"
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

El primer campo, que es la fecha, es para después poder hacer las comparativas de crecimiento entre periodos

Modificación, este query es el utilizado por el Enterprise Manager

SELECT trunc(sysdate) FECHA, d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
        TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", 
TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0)/1024/1024,'99999999.999') "Used (M)",
        TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
        FROM sys.dba_tablespaces d, (select 
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select 
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE 
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT 
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
        UNION ALL
        SELECT trunc(sysdate) FECHA, d.status "Status", d.tablespace_name "Name", d.contents "Type",
        d.extent_management "Extent Management", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)",
        TO_CHAR(NVL(t.bytes,0)/1024/1024,'99999999.999') ||'/'||TO_CHAR(NVL(a.bytes/1024/1024, 0),'99999999.999') "Used (M)", 
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %" FROM sys.dba_tablespaces d, (select 
tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a, (select 
tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t WHERE 
d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND 
d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY';


Ya si requieren ver a nivel de Datafile, este query es el indicado

SELECT dd.tablespace_name tablespace_name,
dd.file_name file_name,
dd.bytes/1024 TABLESPACE_KB,
SUM(fs.bytes)/1024 KBYTES_FREE,
MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;

Calcular el Caché Hit Ratio o Aciertos de Caché

Uno de los detalles que he tenido como DBA es la dependencia de una herramienta para realizar el monitoreo de mi BD.


Buscando en Internet y con un poco de paciencia, probando los resultados, me encuentro con unos querys que retornan el famodo Cache Hit Ratio:


SELECT (1-(physical_reads/(db_block_gets+consistent_gets)))*100 "Cache Hit Ratio" FROM v$buffer_pool_statistics;


ó



SELECT to_char(sysdate,'DD-MM-YY HH:Mi:SS') as SYS_DATE, Sum(Decode(a.name, 'consistent gets', 
       a.value, 0)) "Consistent Gets",
       Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
       Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
       Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
         Sum(Decode(a.name, 'db block gets', a.value, 0)) -
         Sum(Decode(a.name, 'physical reads', a.value, 0))  )/
           (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
             Sum(Decode(a.name, 'db block gets', a.value, 0))))
             *100,2) "Hit Ratio %"
FROM   v$sysstat a;



Con ellas pueden obtener el Hit Ratio y tomar la decisión necesaria para optimizar su BD