Archivo de la etiqueta: Database

Buscar intervalos – Lead analytic function

¿Cómo encontrar intervalos libres en una secuencia temporal?

Ésta fue la pregunta que surgió en el transcurso de un proyecto en el que los usuarios tenían que dar de alta periodos temporales con una duración variable y que no tenían por qué seguir un orden. Estos periodos temporales se registraron en una tabla general de periodos, en la que se asociaba a cada usuario los periodos que iba dando de alta con las fechas de inicio y final de periodo. Cada vez que un usuario iba a registrar un nuevo periodo el sistema debía comprobar si este periodo existía ya o era necesario darlo de alta. Pero, ¿cómo buscar si el nuevo periodo de tiempo estaba ya comprendido entre los registrados anteriormente por el usuario?

La respuesta que finalmente encontramos fue utilizar las funciones analíticas MAX y LEAD. Gracias a estas dos funciones, una simple SELECT nos podía devolver los intervalos libres entre los periodos ya registrados en la tabla.

Para demostrar su funcionamiento, primero vamos a crear un pequeño escenario de pruebas. En la tabla “lead_test” vamos a insertar una serie de registros con fechas de inicio y final que nos permitan probar la solución:

-- Clean environment
drop table lead_test;

-- Create test environment
create table lead_test (id number, date_start date, date_end date);

insert into lead_test values(1, to_date('01/01/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/02/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(3, to_date('01/03/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/04/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(4, to_date('15/03/2014 22:00:00', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/05/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));
insert into lead_test values(5, to_date('01/05/2014 12:00:01', 'dd/mm/yyyy hh24:mi:ss'), to_date('01/06/2014 12:00:00', 'dd/mm/yyyy hh24:mi:ss'));

commit;

-- Alter default date format
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';

Una vez ejecutadas las sentencias que preparan el entorno de pruebas vamos a comprobar cómo queda nuestra tabla “lead_test:

-- Show test data
select * from lead_test;

        ID DATE_START          DATE_END
---------- ------------------- -------------------
         1 01/01/2014 12:00:01 01/02/2014 12:00:00
         3 01/03/2014 12:00:01 01/04/2014 12:00:00
         4 15/03/2014 22:00:00 01/05/2014 12:00:00
         5 01/05/2014 12:00:01 01/06/2014 12:00:00

Estos datos nos proporcionan una casuística variada:

  • Hemos omitido el registro con ID = 2 para dejar el intervalo libre.
  • Los registros con ID 3 y 4 se solapan en el tiempo.
  • Los registros con ID 4 y 5 empiezan y acaban en el mismo momento, justo uno a continuación del otro pero sin solapamiento.

En una primera aproximación, se elaboró la siguiente consulta que nos permitía obtener los intervalos que no se encontraban cubiertos por ninguno de los periodos registrados en la tabla:

-- Search All Gaps
select *
from (
      select max(l.date_end) over (order by l.date_start) gap_start,
             lead(l.date_start) over (order by l.date_start) gap_end
      from lead_test l
     )
where gap_start < gap_end;

Como resultado obtendremos el intervalo que existe entre los registros con ID 1 y 3. Pero también el intervalo entre los registros con ID 4 y 5, puesto que no hay solapamiento:

GAP_START           GAP_END
------------------- -------------------
01/02/2014 12:00:00 01/03/2014 12:00:01
01/05/2014 12:00:00 01/05/2014 12:00:01

Con una pequeña modificación en la consulta podemos regular la duración mínima de los intervalos que queremos encontrar. Y, de este modo, podemos descartar aquellos intervalos que resulten demasiado pequeños para nuestros requerimientos:

-- Search Gaps > 1 sec.
select *
from (
      select max(l.date_end) over (order by l.date_start) gap_start,
             lead(l.date_start) over (order by l.date_start) gap_end
      from lead_test l
     )
where gap_start < gap_end
      and (gap_end - gap_start) > (1/24/60/60);

Al incluir como condición que el tiempo entre final de un intervalo y el inicio del intervalo que le sigue sea mayor a 1 segundo (la fórmula 1/24/60/60 representa un segundo en operaciones con fechas en las consultas SQL de Oracle) obtendremos solo el primer intervalo, entre los periodos con ID 1 y 3:

GAP_START           GAP_END
------------------- -------------------
01/02/2014 12:00:00 01/03/2014 12:00:01

Generar SH1 de una cadena

El paquete DBMS_CRYPTO nos permite, entre otras cosas, generar SHA1 de cualquier cadena de texto. Admite los siguientes parámetros:

  • src – [RAW|BLOB|CLOB]: datos a los que aplicar el hash
  • typ – PLS_INTEGER: algoritmo hash a aplicar

Los algoritmos disponibles son los siguientes:

  • HASH_MD4 := 1
  • HASH_MD5 := 2
  • HASH_SH1 := 3

Nota: Es necesario conceder permisos de ejecución sobre este paquete a los usuarios que vayan a utilizarlo.

select DBMS_CRYPTO.hash(utl_raw.cast_to_raw ('Hello'),
                        3) "hash_Hello",
       DBMS_CRYPTO.hash(utl_raw.cast_to_raw ('HELLO'),
                        3) "hash_HELLO"
from dual;
hash_Hello
----------------------------------------
F7FF9E8B7BB2E09B70935A5D785E0CC5D9D0ABF0

hash_HELLO
----------------------------------------
C65F99F8C5376ADADDDC46D5CBCF5762F9E55EB7

Ver también Generar el HASH de una cadena

Generar el HASH de una cadena

El paquete DBMS_UTILITY nos permite, entre otras cosas, generar resúmenes HASH de cualquier cadena de texto. Admite los siguientes parámetros:

  • name – VARCHAR2: cadena a la que se aplica la función hash
  • base – NUMBER: valor mínimo que devolverá la función hash
  • hash_size – NUMBER: valor máximo que devolverá la función hash
select DBMS_UTILITY.get_hash_value('Hello',
                                   1,
                                   power(2,16)-1
                                  ) "hash_Hello",
       DBMS_UTILITY.get_hash_value('HELLO',
                                   1,
                                   power(2,16)-1
                                  ) "hash_HELLO"
from dual;
hash_Hello hash_HELLO
---------- ----------
     11511      16086

Ver también Generar SH1 de una cadena

PL/SQL HTTP call function

La función que se muestra a continuación es una implementación ligera de acceso a recursos HTTP que sirve tanto para peticiones GET como POST. Esta función no da soporte a conexiones seguras HTTPS ni al envío de parámetros que requieran un tratamiento especial, como puede ser el envío de ficheros. Estas limitaciones nos permiten contar con una función relativamente pequeña que se puede incluir fácilmente en cualquier proyecto o script de prueba.

La función cuenta con 4 constantes, declaradas en las primeras líneas de código, para definir los siguientes aspectos:

  • cons_max_retries: Número de reintentos que vamos a realizar en caso de error de conexión.
  • cons_result_ok: Mensaje resultado en caso de éxito.
  • cons_result_error: Mensaje resultado en caso de error.
  • cons_result_sharp: Carácter separador para montar los mensajes de respuesta compuestos.

El código completo de la función http_param se muestra a continuación:

create function http_param(
                           p_url         in  varchar2,
                           p_method      in  varchar2,
                           p_param       in  varchar2,
                           p_html_result out varchar2
                          ) return varchar2
is

   cons_max_retries             CONSTANT number := 3; -- Maximum retries number.
   cons_result_ok               CONSTANT varchar2(10) := 'OK';
   cons_result_error            CONSTANT varchar2(10) := 'ERROR';
   cons_result_sharp            CONSTANT varchar2(1)  := '#';

   req                          UTL_HTTP.REQ;
   resp                         UTL_HTTP.RESP;
   v_value                      varchar2(1024);
   v_url                        varchar2(200);
   v_param                      varchar2(500);
   v_param_length               number;
   v_html_result                varchar2(32767);

   retry                        boolean;
   num_retries                  number;

   v_result                     varchar2(1024);

begin

     -- Init variables
     v_url := p_url;
     v_param := p_param;
     v_param_length := length(v_param);

     if p_method = 'GET' then
        v_url := v_url||'?'||v_param;
     end if;
    
     retry := true;
     num_retries := 1;

     -- Retry loop
     while retry and num_retries <= cons_max_retries loop

         v_result := cons_result_ok;

         begin
             req := UTL_HTTP.BEGIN_REQUEST (url=> v_url, method => p_method);
             UTL_HTTP.SET_HEADER (r      =>  req,
                                  name   =>  'Content-Type',
                                  value  =>  'application/x-www-form-urlencoded');
             
             if p_method = 'POST' then
               UTL_HTTP.SET_HEADER (r      =>   req,
                                    name   =>   'Content-Length',
                                    value  =>   v_param_length);
               UTL_HTTP.WRITE_TEXT (r      =>   req,
                                    data   =>   v_param);
             end if;
             
             resp := UTL_HTTP.GET_RESPONSE(req);
             
             -- Communication errors
             if resp.status_code != 200 then

                 dbms_output.put_line('Connection error:');
                 dbms_output.put_line('  Resp.status_code: '||resp.status_code);
                 dbms_output.put_line('  Resp.reason_phrase: '||resp.reason_phrase);
                 dbms_output.put_line('  Resp.http_version: '||resp.http_version);
                 dbms_output.put_line('  Resp.private_hndl: '||resp.private_hndl);
                 v_result := cons_result_error||cons_result_sharp||'Resp.status_code: '||resp.status_code||' - Resp.reason_phrase: '||resp.reason_phrase;

             -- Valid response
             else

                 -- Read response
                 begin
                    loop
                         UTL_HTTP.READ_LINE(resp, v_value, TRUE);
                         v_html_result := v_html_result || v_value;
                    end loop;
                 exception
                    when UTL_HTTP.END_OF_BODY then
                         UTL_HTTP.END_RESPONSE(resp);
                    when others then
                         v_result := cons_result_error||cons_result_sharp||'ERROR -> http_param: error READ_LINE - '||sqlerrm;
                         dbms_output.put_line( v_result );
                 end;

             end if;

             retry := false;
         exception
            when UTL_HTTP.TRANSFER_TIMEOUT then
               v_result := cons_result_error||cons_result_sharp||'WARNING -> http_param: error UTL_HTTP.TRANSFER_TIMEOUT (retry '||num_retries||'/'||cons_max_retries||') - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := true;
               num_retries := num_retries + 1;
            when UTL_HTTP.HTTP_CLIENT_ERROR then
               v_result := cons_result_error||cons_result_sharp||'WARNING -> http_param: error UTL_HTTP.HTTP_CLIENT_ERROR (retry '||num_retries||'/'||cons_max_retries||') - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := true;
               num_retries := num_retries + 1;
            when UTL_HTTP.HTTP_SERVER_ERROR then
               v_result := cons_result_error||cons_result_sharp||'WARNING -> http_param: error UTL_HTTP.HTTP_SERVER_ERROR (retry '||num_retries||'/'||cons_max_retries||') - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := true;
               num_retries := num_retries + 1;
            when UTL_HTTP.REQUEST_FAILED then
               v_result := cons_result_error||cons_result_sharp||'WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry '||num_retries||'/'||cons_max_retries||') - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := true;
               num_retries := num_retries + 1;
            when OTHERS then
               v_result := cons_result_error||cons_result_sharp||'ERROR -> http_param - '||sqlerrm;
               dbms_output.put_line( v_result );
               retry       := false;
               num_retries := num_retries + 1;
         end;

     end loop;

    -- Result
    p_html_result := v_html_result;
    return v_result;

exception
   when others then
      v_result := cons_result_error||cons_result_sharp||'ERROR -> http_param - '||sqlerrm;
      dbms_output.put_line('Error others: '||v_result);
      return v_result;
end http_param;

Para probar su funcionamiento podemos realizar una consulta al servicio de información meteorológica de Yahoo! En este caso para la ciudad de Madrid:

declare
  v_html_result varchar2(32767);
  v_result      varchar2(2048);
begin
  v_result := http_param(
                         p_url => 'http://weather.yahooapis.com/forecastrss',
                         p_method => 'GET',
                         p_param => 'w=12578024&u=c',
                         p_html_result => v_html_result
                        );
  dbms_output.put_line('Result: '||v_result);
  dbms_output.put_line('HTML result: ');
  if length(v_html_result) > 255 then
      for i in 0..ceil(length(v_html_result)/255) loop
          dbms_output.put_line(substr(v_html_result, ((255*i) + 1), 255));
      end loop;
  else
      dbms_output.put_line(v_html_result);
  end if;
end;

El resultado esperado, en caso de que logremos conectar con el servicio de Yahoo! desde nuestra sesión de base de datos, sería algo similar a esto:

Result: OK
HTML result: ...<xml_with_weather_data>...

Oracle 11g

Hay que tener en cuenta que a partir de la versión 11g de Oracle existe un control de acceso para el uso de cualquier protocolo que permita abrir conexiones externas a la base de datos. Este control de acceso se encuentra regulado por listas de acceso o ACL. Si no hemos habilitado el acceso a las URLs necesarias dando de alta las reglas oportunas en las ACL obtendremos un error como el siguiente:

ERROR#WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry 1/3) -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ERROR#WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry 2/3) -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
ERROR#WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry 3/3) -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
Result: ERROR#WARNING -> http_param: error UTL_HTTP.REQUEST_FAILED (retry 3/3) -
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1130
ORA-24247: network access denied by access control list (ACL)
HTML result:

En la entrada “Control de acceso a recursos de red – ACL management” se explica cómo crear la ACL necesaria para completar la prueba de conexión con Yahoo!.

Generar valores RANDOM

El paquete DBMS_RANDOM nos permite, entre otras cosas, generar números aleatorios indicando un rango. Admite los siguientes parámetros, que pueden ser omitidos:

  • low – NUMBER: valor mínimo que devolverá la función random
  • high – NUMBER: valor máximo que devolverá la función random
select DBMS_RANDOM.value nums_decim,
       DBMS_RANDOM.value(1000,
                         9999) nums_range_decim,
       trunc(DBMS_RANDOM.value(1000,
                               9999)) nums_range_int
from dual;
NUMS_DECIM NUMS_RANGE_DECIM NUMS_RANGE_INT
---------- ---------------- --------------
0,76259616 8188,26474538092           3750

Control de acceso a recursos de red – ACL management

Con la salida de Oracle 11g se ha incluido en la base de datos un sistema para controlar el acceso a recursos de red que, por defecto, bloquea cualquier intento de conexión al exterior. Esto nos obliga a crear una serie de listas, o ACLs, para controlar y permitir el acceso a los recursos de red que sean necesarios en nuestros desarrollos.

El paquete DBMS_NETWORK_ACL_ADMIN nos proporciona todos los procesos necesarios para crear y gestionar estas listas. En el siguiente ejemplo se muestran los pasos que debemos seguir para habilitar a dos usuarios el acceso a una URL concreta:

  • Nombre de la lista ACL: network_access_test.xml
  • Usuarios con permisos de conexión: TEST_USER y TEST_USER_2
  • URL de acceso: *.yahooapis.com
-- Create ACL
BEGIN
 DBMS_NETWORK_ACL_ADMIN.CREATE_ACL (
  acl          => 'network_access_test.xml', 
  description  => 'Network access test', 
  principal    => 'TEST_USER', -- Must be in upper case
  is_grant     => TRUE,
  privilege    => 'connect',
  start_date   => NULL,
  end_date     => NULL);
END;
/

 -- Creates the second role privilege
BEGIN
 DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE (
  acl          => 'network_access_test.xml',
  principal    => 'TEST_USER_2',
  is_grant     => TRUE, 
  privilege    => 'connect',
  position     => NULL,
  start_date   => NULL,
  end_date     => NULL);
END;
/

 -- Creates the first target host
BEGIN
 DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL (
  acl          => 'network_access_test.xml',
  host         => '*.yahooapis.com',
  lower_port   => NULL,
  upper_port   => NULL); 
END;
/

Para asegurarnos de que hemos activado el acceso correcto podemos consultar las siguientes vistas:

-- List ACLs
select *
from RESOURCE_VIEW
where any_path like '/sys/acls/%';

-- List ACL privileges
select *
from DBA_NETWORK_ACL_PRIVILEGES;

-- List URLs
select *
from DBA_NETWORK_ACLS;

Por último vamos a mostrar cómo eliminar usuarios, conexiones y la ACL completa que hemos creado:

-- Delete users / roles from ACL
BEGIN
 DBMS_NETWORK_ACL_ADMIN.DELETE_PRIVILEGE (
  acl          => 'network_access_test.xml',
  principal    => 'TEST_USER',
  is_grant     => TRUE, 
  privilege    => 'connect');
END;
/

-- Delete URLs
BEGIN
 DBMS_NETWORK_ACL_ADMIN.UNASSIGN_ACL (
  acl          => 'network_access_test.xml',
  host         => '*.yahooapis.com',
  lower_port   => NULL,
  upper_port   => NULL); 
END;
/

-- Drop ACL
BEGIN
 DBMS_NETWORK_ACL_ADMIN.DROP_ACL (
  acl          => 'network_access_test.xml'); 
END;
/

En este ejemplo se ha creado una ACL básica que permite acceso completo a una URL. Pero las ACL permiten controlar aspectos como los puertos a los que se tiene acceso, definición de subredes o establecer la ventana temporal en la que estará activo el acceso para cada usuario.

Para profundizar en estos aspectos se puede consultar el siguiente enlace:
http://download.oracle.com/docs/cd/B28359_01/network.111/b28531/authorization.htm#DBSEG40012

UTL_FILE test

PL/SQL cuenta con un modo de acceso al sistema de ficheros que abstrae al programador del medio físico. A través de la declaración de directorios virtuales (select * from ALL_DIRECTORIES) hace que sea más sencillo trabajar con recursos del sistema de ficheros. Pero al mismo tiempo dificulta el análisis de cualquier problema con el acceso a estos recursos.

Un pequeño script como el siguiente, que verifique cada una de las operaciones disponibles en PL/SQL para el acceso a un medio físico y nos informe de los errores que se produzcan en cada una de ellas, puede sernos de gran ayuda:

set serveroutput on;
DECLARE

 -- Configure procedure
 v_dir varchar2(255) := 'LOG_FILE_DIR';

 -- Variables
 f_file utl_file.file_type;
 v_file varchar2(255);
 v_file_dest varchar2(255);
 v_file_copy varchar2(255);

 v_msg_line varchar2(32767);
 v_buffer varchar2(32767);

 v_write_error varchar2(2048);
 v_read_error varchar2(2048);
 v_copy_error varchar2(2048);
 v_remove_error varchar2(2048);
 v_rename_error varchar2(2048);

BEGIN

 -- Filename.
 v_file := 'utl_test.tmp';
 v_file_dest := 'utl_test_moved.tmp';
 v_file_copy := 'utl_test_copied.tmp';

 -- Write test.
 begin
 v_msg_line := 'Write test line.';
 f_file := utl_file.FOPEN( v_dir, v_file, 'A', 32767 );
 utl_file.PUT_LINE( f_file, v_msg_line, true );
 utl_file.FCLOSE( f_file );
 exception
 when others then
 v_write_error := sqlerrm;
 end;

 -- Read test.
 begin
 f_file := utl_file.FOPEN( v_dir, v_file, 'R', 32767 );
 utl_file.GET_LINE( f_file, v_buffer, 32767 );
 utl_file.FCLOSE( f_file );
 exception
 when others then
 v_read_error := sqlerrm;
 end;

 -- Copy test.
 begin
 utl_file.FCOPY( v_dir, v_file, v_dir, v_file_copy );
 exception
 when others then
 v_copy_error := sqlerrm;
 end;

 -- Remove test.
 begin
 utl_file.FREMOVE( v_dir, v_file );
 exception
 when others then
 v_remove_error := sqlerrm;
 end;

 -- Rename test.
 begin
 utl_file.FRENAME( v_dir, v_file_copy, v_dir, v_file_dest );
 exception
 when others then
 v_rename_error := sqlerrm;
 end;

 -- Test Result
 dbms_output.put_line('UTL_FILE Test results at '||to_char(sysdate, 'dd mon yyyy hh24:mi:ss'));
 dbms_output.put_line('');
 dbms_output.put_line('DB_NAME.......'||sys_context('userenv','db_name'));
 dbms_output.put_line('SERVER_HOST...'||sys_context('userenv','server_host'));
 dbms_output.put_line('');
 dbms_output.put_line('Write test....'||nvl(v_write_error, 'OK'));
 dbms_output.put_line('Read test.....'||nvl(v_read_error, 'OK'));
 dbms_output.put_line('Copy test.....'||nvl(v_copy_error, 'OK'));
 dbms_output.put_line('Remove test...'||nvl(v_remove_error, 'OK'));
 dbms_output.put_line('Rename test...'||nvl(v_rename_error, 'OK'));

 -- Clean test file.
 utl_file.FREMOVE( v_dir, v_file_dest );

EXCEPTION
 WHEN OTHERS THEN
 utl_file.FCLOSE( f_file );
 dbms_output.put_line('Error utl_file_test: '||sqlerrm);
END;

Si todo es correcto, deberíamos obtener un resultado como el siguiente:

UTL_FILE Test results at 25 jun 2014 22:37:23

DB_NAME.......ora_test
SERVER_HOST...vmtest

Write test....OK
Read test.....OK
Copy test.....OK
Remove test...OK
Rename test...OK

Búsquedas en columnas de tipo LONG

Cuando intentamos hacer una búsqueda sobre una columna de tipo LONG del mismo modo que operamos con columnas VARCHAR nos encontramos con el problema de que no se comportan del mismo modo. Y el problema es más común de lo que a priori se puede esperar dado que varias vistas del diccionario de datos utilizan este tipo de datos para almacenar información bastante interesante.

En el caso que nos ocupa, estábamos intentando localizar el uso de una columna concreta en cualquier vista accesible por el usuario. Cuando intentamos consultar la vista ALL_VIEWS para localizar en ALL_VIEWS.TEXT las menciones a la columna que buscábamos nos encontramos con el siguiente error:

select *
from all_views
where upper(text) like '%TABLE_NAME%';
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

La función UPPER() es una más de las muchas que no se pueden aplicar sobre tipos de datos LONG.

Después de darle varias vueltas al problema, e intentar distintas alternativas, logramos escribir un pequeño bloque anónimo que nos permitía realizar la búsqueda:

-- Search in LONG datatype columns
set serveroutput on;
begin
    for i in (select * from all_views) loop
        if instr(upper(i.text), 'TABLE_NAME') > 0 then
            dbms_output.put_line('View: '||i.view_name);
        end if;
    end loop;
end;

Y por fin obtuvimos el resultado esperado:

View: ALL_ALL_TABLES
View: ALL_TAB_COLUMNS
View: ALL_TAB_COL_STATISTICS
...
...
...

ORA-00060 Deadlock – Index on Foreign Key (FK) Constraint

Los errores por ORA-00060 Deadlock, como siempre nos recuerdan desde soporte de Oracle, no son errores de la base de datos propiamente dichos. Son errores provocados por una mala implementación de código.

El propio soporte de Oracle ofrece bastantes recursos y documentación para localizar el origen de estos errores y poder aplicar las correcciones necesarias a nuestra base de datos.

En el caso que me ocupa, se detectó una disminución del rendimiento de una base de datos por interbloqueos. Es decir: ORA-00060 Deadlock.

Después de consultar distintos documentos en soporte Oracle y varias fuentes en Internet, se localizó el origen de los interbloqueos y se aplicaron las medidas correctivas pertinentes. Los pasos que seguimos fueron los siguientes.

A través de soporte Oracle se localizó el documento HOWTO “How to Identify ORA-00060 Deadlock Types Using Deadlock Graphs in Trace” (Doc ID 1507093.1). En este documento nos ayudan a interpretar las trazas que se generan cada vez que Oracle detecta un interbloqueo o deadlock. La tipología que se presentaba en nuestro caso era la siguiente:

deadlock_graph

Este extracto contiene la información más importante del fichero de traza a la hora de catalogar el deadlock y buscar la solución más conveniente. Hay que prestar especial atención a las columnas marcadas en amarillo:

deadlock_graph-particular_characteristics

Como puede apreciarse, nuestro caso correspondía a la tipología “TM SX SSX SX SSX” que indica la necesidad de generar índices para apoyar la verificación de claves externas. Especialmente cuando se producen muchas operaciones de inserción y actualización en lotes.

Bueno, pues una vez identificado el problema teníamos que buscar la solución más conveniente. ¡Manos a la obra!. En primer lugar teníamos que localizar los índices que era necesario crear y lanzar una batería de pruebas que confirmara el cese de los interbloqueos. Gracias al blog de Tom Kyte pudimos ahorrarnos el trabajo de generar una consulta que nos localizara estos índices. La consulta que nos ofrece Tom Kyte es la siguiente:

select *
from (
select table_name, constraint_name,
     cname1 || nvl2(cname2,','||cname2,null) ||
     nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
     nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
     nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
            columns
  from ( select b.table_name,
                b.constraint_name,
                max(decode( position, 1, column_name, null )) cname1,
                max(decode( position, 2, column_name, null )) cname2,
                max(decode( position, 3, column_name, null )) cname3,
                max(decode( position, 4, column_name, null )) cname4,
                max(decode( position, 5, column_name, null )) cname5,
                max(decode( position, 6, column_name, null )) cname6,
                max(decode( position, 7, column_name, null )) cname7,
                max(decode( position, 8, column_name, null )) cname8,
                count(*) col_cnt
           from (select substr(table_name,1,30) table_name,
                        substr(constraint_name,1,30) constraint_name,
                        substr(column_name,1,30) column_name,
                        position
                   from user_cons_columns ) a,
                user_constraints b
          where a.constraint_name = b.constraint_name
            and b.constraint_type = 'R'
          group by b.table_name, b.constraint_name
       ) cons
 where col_cnt > ALL
         ( select count(*)
             from user_ind_columns i
            where i.table_name = cons.table_name
              and i.column_name in (cname1, cname2, cname3, cname4,
                                    cname5, cname6, cname7, cname8 )
              and i.column_position < = cons.col_cnt
            group by i.index_name
         )
);

Esta consulta devuelve todas las claves externas que no disponen de un índice de apoyo, dentro de las constraints pertenecientes al usuario conectado. La consulta se encuentra en la siguiente entrada de su blog:

Tom Kyte – Thanks for the question regarding “Rows locks from select for update clause”

Sobre estos datos, hay que seleccionar las claves externas para la que nos interesa crear índices. No siempre es bueno crear índices para claves externas, como indica Tom Kyte en otra entrada de su blog:

Tom Kyte – Thanks for the question regarding “Indexes on foreign keys”

Una vez seleccionados los índices que consideremos necesarios en cada caso, se pueden generar volviendo a lanzar la sentencia de Tom Kyte con la siguiente cláusula select:

select 'create index '||constraint_name||' on '||table_name||' ('||columns||');' script

La tarea de seleccionar los índices pasa por identificar las tablas afectadas por los interbloqueos. Gracias a los Deadlock Graphs de las trazas capturadas y con una simple consulta al diccionario de datos, podemos obtener este dato. Los datos en hexadecimal que siguen al tipo de bloqueo dentro de la traza corresponden al identificador del objeto en la base de datos. En nuestro ejemplo el dato a verificar es el siguiente:

TM-0000cc6c-00000000

Y con la siguiente consulta, obtenemos el nombre del objeto:

select *
from dba_objects
where object_id in (TO_NUMBER('0000cc6c', 'XXXXXXXX'))