Programación PL/SQL y bases de datos Oracle

El paquete PL/SQL DBMS_SCHEDULER para programación de trabajos

2009-06-10T18:36:00.014+02:00

DBMS_SCHEDULER es el paquete PLSQL que reemplazó en la versión de la base de datos Oracle 10g al paquete DBMS_JOB. Aunque el paquete DBMS_JOB sigue existiendo por razones de compatibilidad, no debe utilizarse ya que es muy probable que deje de existir en futuras versiones de la base de datos Oracle. El paquete DBMS_SCHEDULER permite programar la ejecución, en los instantes que deseemos, de bloques PLSQL, así como de procedimientos y funciones PL/SQL. Por otro lado, también permite programar la ejecución de binarios y shell-scripts.

Permisos necesarios

Con permisos de DBA se tiene acceso a todas las funciones del paquete DBMS_SCHEDULER. Para administrar la programación de procesos se necesita tener la rol (role) de SCHEDULER_ADMIN. Y finalmente, para crear y ejecutar procesos bajo tu propia identidad, se necesita tener el privilegio CREATE JOB. Por otro lado, aprovecho para mencionar que el paquete DBMS_JOB requería la inicialización de un parámetro del sistema, tras lo cual se lanzaba un proceso en background encargado de coordinar la ejecución de los distintos procesos programados, pero esto ya no es necesario si utilizamos Oracle 10g y el paquete DBMS_SCHEDULER.

Programación rápida de ejecución de un bloque PL/SQL

Para programar la ejecución de un bloque PLSQL podemos utilizar directamente el siguiente código. Sin duda es la forma más rápida y sencilla de programar la ejecución de un trabajo.

BEGIN
  DBMS_SCHEDULER.create_job(
     job_name        => 'DEMO_BLOQUE_PROGRAMADO'
    ,job_type        => 'PLSQL_BLOCK'
    ,job_action      => 
      'begin 
       nom_paq.nom_proced(''valor_param'');
       end;'
    ,start_date      => '06/11/2009 02:00 AM'
    ,repeat_interval => 'FREQ=DAILY'
    ,enabled         => TRUE
    ,comments        => 'Bloque PLSQL programado');
  COMMIT;
END;
/

En el código de arriba lo que hemos hecho es programar un bloque PL/SQL para que se ejecute diariamente a las dos de la mañana a partir del día 11 de junio de 2009.

Ejemplo completo de programación de un trabajo

Lo primero que debemos hacer el definir el programa que se va a ejecutar.

BEGIN
  DBMS_SCHEDULER.create_program (
     program_name        => 'DEMO_PROC_PROGRAMADO'
    ,program_type        => 'STORED_PROCEDURE'
    ,program_action      => 'nom_paq.nom_proced'
    ,number_of_arguments => 1
    ,enabled             => FALSE
    ,comments            => 'Proc. PLSQL programado');
  DBMS_SCHEDULER.define_program_argument (
     program_name      => 'DEMO_PROC_PROGRAMADO'
    ,argument_position => 1
    ,argument_name     => 'valor1'
    ,argument_type     => 'VARCHAR2'
    ,default_value     => 'valor por defecto');
  DBMS_SCHEDULER.enable (
     name => 'DEMO_PROC_PROGRAMADO');
  COMMIT;
END;
/

El parámetro program_type puede tomar los siguiente valores: PLSQL_BLOCK, STORED_PROCEDURE o EXECUTABLE.

A continuación debemos definir cuando y con que frecuencia queremos ejecutar nuestro programa, es decir, debemos definir la schedule propiamente dicha.

BEGIN
  DBMS_SCHEDULER.create_schedule(  
     schedule_name   => 'DEMO_SCHEDULE'
    ,start_date      =>  '01/01/2009 01:00:00'
    ,repeat_interval => 'FREQ=WEEKLY'
    ,comments        => 'Semanalmente a las 01:00');
  COMMIT;
END;
/

Si queremos deshabilitar la schedule anteriormente definida utilizaremos el siguiente código:

BEGIN
  DBMS_SCHEDULER.drop_schedule(
     schedule_name => 'DEMO_SCHEDULE'
    ,force         => TRUE );
  COMMIT;
END;
/

Para establecer la frecuencia (repeat_interval) podemos utilizar las siguientes expresiones: YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY y SECONDELY.

A continuación debemos definir el proceso o trabajo que queremos programar. Esto se realiza mediante la asignación de un programa a una o más schedules.

BEGIN
  DBMS_SCHEDULER.create_job(
     job_name      => 'DEMO_TRABAJO_PLSQL'
    ,program_name  =>'DEMO_PROC_PROGRAMADO'
    ,schedule_name =>'DEMO_SCHEDULE'
    ,enabled       => FALSE
    ,comments      => 'Semanal a las 01:00');
  DBMS_SCHEDULER.set_job_argument_value(
     job_name          => 'DEMO_TRABAJO_PLSQL'
    ,argument_position => 1
    ,argument_value    => 'valorparametro1');
  DBMS_SCHEDULER.enable('DEMO_TRABAJO_PLSQL');
  COMMIT;
END;
/

Por otro lado y según ya he comentado, con el paquete DBMS_SCHEDULER también podemos hacer correr directamente un ejecutable o una shell script:

BEGIN
   DBMS_SCHEDULER.create_job
   (
      job_name      => 'RUN_SHELL_SCRIPT_DEMO',
      schedule_name => 'DEMO_SCHEDULE',
      job_type      => 'EXECUTABLE',
      job_action    => '/home/shell/demo_script.sh',
      enabled       => TRUE,
      comments      => 'Ejecución de un shell script'
   );
  COMMIT;
END;
/

Otros paquetes estándares del PLSQL: DBMS_LOCK, DBMS_SQL y DBMS_OUTPUT.

Como evitar el uso de constantes fijas (hard-coded) en PL/SQL

2009-05-06T18:07:00.007+02:00

Esta claro que la mayoría de los programas y aplicaciones PLSQL tienen su propio conjunto de constantes que determinan las características de dicha aplicación. Por lo general, estos valores constantes tienen que ser utilizados en distintos lugares del código. En muchas ocasiones estos valores permanecerán invariables durante todo el ciclo de vida de la aplicación pero, en muchos otros casos, cambiarán de forma periódica (por ejemplo, una vez al año).

Pongamos un ejemplo, supongamos que en un programa PL/SQL establecemos que el salario anual de un empleado no puede superar los 50.000 euros. Podemos codificar esta norma utilizando la siguiente subrutina:

IF l_salario_anual > 50000
THEN
RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior
  a 50000 euros');
END IF;

La mayoría de los programadores sabemos que referenciar un valor constante de esta manera no es muy recomendable. En inglés se dice que la constante está "hard-coded" directamente en la rutina. Resulta obvio señalar que este tipo de prácticas suele derivar en problemas, ya que el valor de 50.000 euros cambiará muy probablemente de año en año y, cuando esto ocurra, deberemos revisar el código PLSQL y cambiar el antiguo valor por el nuevo en cada uno de los sitios donde sea utilizado.

En PL/SQL disponemos de tres formas bastante evidentes de evitar tener que utilizar constantes directamente en el código:

Crear un paquete PLSQL que asigne el valor a esa constante
Crear un función PLSQL que devuelva dicho valor
Almacenar y gestionar el valor de dicha constante en una tabla de la base de datos Oracle

A continuación pondré un ejemplo de cada una de las opciones mencionadas.

Paquete PL/SQL de constantes

CREATE PACKAGE c_constantes IS
 c_salario_maximo CONSTANT NUMBER := 50000;
END c_constantes;

Una vez definido el paquete ya sólo tendríamos que referirnos al mismo de la siguiente manera:

IF l_salario_anual > 
   c_constantes.c_salario_maximo
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || c_constantes.c_salario_maximo
  || ' euros');
END IF;

Función PL/SQL que devuelve el valor de la constante

CREATE PACKAGE f_constantes IS
FUNCTION f_salario_maximo RETURN NUMBER;
END f_constantes;

CREATE PACKAGE BODY f_constantes IS
FUNCTION f_salario_maximo RETURN NUMBER IS
BEGIN
  RETURN 50000;
END;
END f_constantes;

Y ahora para referirnos a la constante utilizaríamos el siguiente código:

IF l_salario_anual > 
   f_constantes.f_salario_maximo()
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || f_constantes.f_salario_maximo()
  || ' euros');
END IF;

Almacenar la constante en una tabla de la base de datos

Si en nuestra aplicación debemos gestionar un buen número de constantes, la opción más recomendable sería crear una tabla con los campos, por ejemplo, "nombre", "descripción" y "valor", y sacar de ella los valores constantes correspondientes. En nuestro caso, y suponiendo que el campo "valor" es un VARCHAR2, podríamos escribir la siguiente función PLSQL:

CREATE PACKAGE t_constantes IS
FUNCTION t_numero 
  (t_nombre IN tabla_constantes.valor%TYPE) 
  RETURN NUMBER;
END t_constantes;

CREATE PACKAGE BODY t_constantes IS
FUNCTION t_numero 
  (p_nombre IN tabla_constantes.valor%TYPE) 
  RETURN NUMBER IS
BEGIN
  v_valor tabla_constantes.valor%TYPE;
  SELECT valor
  INTO v_valor
  FROM tabla_constantes
  WHERE nombre = p_nombre;
  RETURN TO_NUMBER(v_valor);
END;
END t_constantes;

Y para referirnos a la constante utilizaríamos algo semejante a:

IF l_salario_anual >
   t_constantes.t_numero('salario maximo')
THEN
  RAISE_APPLICATION_ERROR (
  -20001, 
  'El salario anual no puede ser superior a '
  || t_constantes.t_numero('salario maximo')
  || ' euros');
END IF;

En este ejemplo he definido una función para devolver valores de tipo numérico (NUMBER), pero igualmente podríamos definir funciones semejantes que devuelvan valores de tipo VARCHAR2, DATE, etcétera.

Artículos relacionados:
Tipos de datos en PLSQL.
Procedimientos y funciones en PLSQL.

La funcionalidad de muestreo dinámico o Dynamic Sampling

2009-03-30T18:16:00.008+02:00

La funcionalidad de muestreo dinámico (Dynamic Sampling) estuvo por primera vez disponible para la release 2 de la bases de datos Oracle 9i. Esta funcionalidad posibilita que el optimizador SQL y PL/SQL basado en costes (CBO) muestree las tablas que utiliza una consulta (query) durante la fase de parsing duro, para mejorar los valores estadísticos que utiliza el optimizador al incluir en dichas estadísticas los segmentos no analizados con anterioridad. Como ya he indicado, este muestro sólo ocurre durante la fase de parsing duro y se utiliza para mejorar las estadísticas utilizadas por el optimizador PLSQL, de ahí el nombre de muestro dinámico.

El optimizador de las bases de datos Oracle usa una gran variedad de datos de entradas para generar los planes de ejecución. Utiliza todos los índices y restricciones (constraints) definidos en las tablas, las estadísticas del sistema (velocidad de entrada/salida de los servidores, velocidad de CPU), y las estadísticas recogidas de los segmentos involucrados en la ejecución de la consulta. El optimizador utiliza estas estadísticas para estimar el número de registros involucrados en cada uno de los pasos de los que consta un plan de ejecución específico, siendo este volumen de registros la variable principal a la hora de calcular el coste de ejecución de una consulta. Cuando el optimizador calcula incorrectamente el volumen de registros, probablemente escogerá un plan de ejecución ineficiente causando problemas de rendimiento en la base de datos. De hecho, la principal razón por la que el optimizador Oracle puede decidirse por un plan de ejecución incorrecto, es precisamente por esta causa, es decir, una estimación incorrecta del volumen de registros a procesar en cada paso del plan de ejecución.

En conclusión, el principal motivo que existe detrás de utilizar la funcionalidad del muestreo dinámico, es ayudar al optimizador a estimar correctamente el volumen de registros a procesar, alimentándole con mayor cantidad de datos y una información más exacta relativa a la ejecución de la consulta que se necesita ejecutar en ese mismo momento.

Formas de trabajar del muestreo dinámico o Dynamic Sampling

El tipo de muestreo dinámico puede establecerse de dos formas diferentes:

Con el parámetro OPTIMIZER_DYNAMIC_SAMPLING puede establecerse el tipo de muestreo dinámico, bien a nivel de instancia de la base de datos, bien a nivel de sesión mediante la utilización del comando ALTER SESSION.
También se puede utilizar el hint de SQL o PL/SQL denominado DYNAMIC_SAMPLING para establecer es tipo de muestreo a nivel de consulta

A continuación os mostraré un ejemplo en el que podréis ver algunas diferencias entre un plan de ejecución generado por el optimizador Oracle sin utilizar muestreo dinámico, y otro plan en el que si que se utiliza dicha funcionalidad. En el ejemplo debemos considerar que la tabla mitabla consta de 60.000 registros:

SQL> set autotrace traceonly explain
SQL> select /*+ dynamic_sampling(mit 0) */ * 
SQL> from mitabla mit;

Execution Plan
------------------------------------------------------
Plan hash value: 1601036213
------------------------------------------------------
|Id|Operation        |Name|Rows |Cost (%CPU)|Time    |
------------------------------------------------------
| 0|SELECT STATEMENT |    |15034| 52     (0)|00:00:01|
| 1|TABLE ACCESS FULL|T   |15034| 52     (0)|00:00:01|
------------------------------------------------------

SQL> select * from mitabla mit;

Execution Plan
------------------------------------------------------
Plan hash value: 1601036213
------------------------------------------------------
|Id|Operation        |Name|Rows |Cost (%CPU)|Time    |
------------------------------------------------------
| 0|SELECT STATEMENT |    |64222| 54     (2)|00:00:01|
| 1|TABLE ACCESS FULL|T   |64222| 54     (2)|00:00:01|
------------------------------------------------------

En el ejemplo vemos un primer caso de plan de ejecución en el que hemos deshabilitado el muestreo dinámico utilizando el hint DYNAMIC_SAMPLING, mientras que el segundo plan de ejecución, puesto que por defecto dicha funcionalidad está habilitada y no hemos utilizado ningún hint en la consulta, se ha generado utilizando el muestro dinámico. Como podemos ver, los planes de ejecución arrojan datos bastante dispares. Al deshabilitar el muestreo dinámico, el optimizador utiliza los valores estadísticos por defecto que típicamente no son muy fiables, ya que el número de registros se estima en base a número de bloques de datos en la tabla y al tamaño medio de los registros de dicha tabla. Así, al deshabilitar el muestreo dinámico, el optimizador deduce que la tabla cuenta con tan sólo 15.034 registros frente a los 60.000 que tiene en realidad. Por otro lado, al activar el muestreo dinámico, el optimizador da un valor mucho más cercano a la realidad, 64.222 registros.

La inexactitud se hace todavía más patente si borramos totalmente el contenido de la tabla mitabla. En este caso, el plan de ejecución si deshabilitamos el muestreo dinámico seguirá devolviéndonos el valor de 15.034 registros, mientras que el plan de ejecución con el dynamic sampling habilitado no dirá que el número de registros a procesar es tan sólo 1.

El muestreo dinámico es más eficaz cuando en una consulta estamos utilizando una tabla que ha sido creada y poblada con datos, pero que todavía no ha sido analizada mediante el paquete estándar PLSQL DBMS_STATS, paquete que se utiliza para generar los datos estadísticos de las tablas. Hay que considerar que desde la primera release de la base de datos Oracle 10g, ya sólo esta soportado el optimizador basado en costes, y que dicho optimizador necesita datos estadísticos exactos para funcionar correctamente. Por lo tanto, si una tabla existe y sus estadísticas no han sido recogidas todavía, el muestreo dinámico ayuda sobremanera a que el optimizador genere planes de ejecución adecuados.

Por último terminaré indicando que la funcionalidad de muestreo dinámico ofrece once niveles de dynamic sampling, de 0 (deshabilitado) a 10. En la release 2 de Oracle 9i el valor por defecto es 1, mientras que en la release 2 de Oracle 10g y superiores, el valor por defecto es 2. Si queréis informaros acerca del significado e implicaciones que supone utilizar un nivel de muestreo dinámico u otro, os emplazo a que visitéis este enlace: Dynamic Sampling Levels.

Artículos relacionados:
Hints PLSQL para el modo de optimización.
Planes de ejecución Oracle.

El paquete estándar DBMS_LOCK para sincronizar procesos

2009-02-24T18:54:00.014+01:00

Hace unas semanas alguien me preguntó como se podía determinar si un procedimiento PL/SQL (procedure PLSQL) estaba siendo ejecutado para evitar tener dos instancias del mismo proceso corriendo simultáneamente. Para manejar este tipo de situaciones, así como situaciones en las que queramos ejecutar procedimientos y funciones de forma secuencial y sincronizar diferentes procesos, Oracle dispone de el paquete de funciones y procedimientos DBMS_LOCK.

Con el paquete DBMS_LOCK podemos establecer bloqueos de usuario (PL/SQL User Locks (UL)) con los que podremos parar la ejecución de un procedure si al ejecutarlo nos encontramos con que un determinado bloqueo o lock está en proceso.

A continuación os describo los tres procedimientos y funciones del paquete DBMS_LOCK más importantes:

DBMS_LOCK.allocate_unique (nombre_lock, id_lock, expira_seg): Este procedimiento lo que hace es asignar un identificador único (id_lock) para el nombre de bloqueo (nombre_lock) que hemos utilizado como parámetro de entrada. Por lo tanto, id_lock se trata de un parámetro de salida, y los valores que puede tomar están comprendidos en el rango de 1073741824 a 1999999999. Por otro lado, el parámetro expira_seg es opcional e indica el número de segundos que se mantendrá el bloqueo una vez que haya sido solicitado, si no se especifica, el valor por defecto es de 864000 segundos (10 días).

DBMS_LOCK.request (id_lock, tipo_lock, timeout, unlock_on): Con esta función lo que hacemos es solicitar el establecimiento de un bloqueo de usuario.

Veamos el uso de los distintos parámetros:

id_lock: Valor del identificador único generado anteriormente mediante la ejecución del procedimiento DBMS_LOCK.allocate_unique.

tipo_lock: Permite definir el tipo de bloqueo que queremos establecer. Puede tomar los siguientes valores: 2 - Row Share (SS), 3 - Row Exclusive (SX), 4 - Share (S), 5 - Share Row Exclusive (SSX) y 6 - Exclusive (X). En la mayoría de los casos utilizaremos el valor de 6 para establecer un bloqueo exclusivo.

timeout: Número de segundos que queremos permanecer esperando a que se libere el bloqueo si nos encontramos con que otro idéntico ha sido solicitado anteriormente y está todavía en marcha. Si no especificamos ningún valor, la función permanecerá en espera hasta que el bloqueo se libere (el valor por defecto que toma la variable timeout es muy alto).

unlock_on: Indica si el bloqueo se debe liberar al ejecutar los comandos COMMIT o ROLLBACK (valor TRUE) o si debe mantenerse aunque ejecutemos alguno de los comandos mencionados (valor FALSE). El valor por defecto es FALSE.

Los valores que puede devolver esta función son los siguientes:

0: Bloqueo establecido satisfactoriamente.

1: El bloqueo no se pudo establecer por superarse el tiempo de espera (timeout).

2: Bloqueo multiple (deadlock). Aparece cuando se ha excedido el límite de peticiones de bloqueo permitidas sobre el mismo identificador.

3: Parámetro erróneo o inválido.

4: Aparece cuando en una misma sesión se solicita el mismo bloqueo por segunda vez sin haber liberado la primera petición.

5: Identificador de lock ilegal.

DBMS_LOCK.release (id_lock): Esta función se utiliza para liberar un bloqueo previamente establecido con la función DBMS_LOCK.request. La función puede devolver los valores 0, 3, 4 ó 5. El significado de dichos valores es exactamente el mismo que en la función DBMS_LOCK.request salvo el valor 4, que en este caso indica que no somos los dueños del lock y no podemos liberarlo.

Ejemplo práctico del uso del paquete DBMS_LOCK

En una primera sesión ejecutamos:

DECLARE
  v_lockhandle VARCHAR2(200);
BEGIN
  DBMS_LOCK.allocate_unique
    ('Mi_Procedure', v_lockhandle);
  DBMS_OUTPUT.put_line
    ('id_lock = '||v_lockhandle);
  DBMS_OUTPUT.put_line
    ('request status = ' ||
     DBMS_LOCK.request(v_lockhandle, 6));
END;

La salida por pantalla tras la ejecución del processo sería:

id_lock = 1074484814107448481486
request status = 0

A continuación en la tabla V$LOCK podemos identificar el bloqueo en curso de la siguiente manera (ojo, sólo hay que utilizar los diez primeros caracteres del id_lock):

SELECT sid, type, id1, lmode, 
       request, ctime, block
FROM   v$lock 
WHERE  id1 = '1074484814'

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
117 UL   1074484814     6       0   201     0

Si ejecutamos el mismo proceso sobre una segunda sesión, observaremos que dicho proceso se queda esperando a que el bloqueo establecido en la primera sesión se libere. En la tabla V$LOCK veremos que aparece una nueva línea en espera de establecer un bloqueo exclusivo (lmode = 0, request = 6):

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
117 UL   1074484814     6       0   790     1
 97 UL   1074484814     0       6     9     0

Ahora si abrimos una tercera sesión y ejecutamos:

DECLARE
  v_lockhandle VARCHAR2(200);
BEGIN
  DBMS_LOCK.allocate_unique
    ('Mi_Procedure', v_lockhandle);
  DBMS_OUTPUT.put_line
    ('id_lock = '||v_lockhandle);
  DBMS_OUTPUT.put_line
    ('request status = ' ||
    DBMS_LOCK.request(v_lockhandle, 6, 20));
END;

El proceso se quedará en espera durante 20 segundos (observad que he utilizado el parámetro de timeout con el valor de 20). Transcurrido este tiempo el proceso terminará y obtendremos la siguiente salida:

id_lock = 1074484814107448481486
request status = 1

Es decir, el proceso DBMS_LOCK.request devuelve el valor "1" indicando que el establecimiento del bloqueo falló por timeout. Por otro lado, durante esos 20 segundos de espera, aparecerá una tercera línea en la tabla V$LOCK (lmode = 0, request = 6), que no considero necesario mostrar.

Ahora, si en la primera sesión liberamos el bloqueo ejecutando:

DECLARE
  v_lockhandle VARCHAR2(200) := '1074484814';
BEGIN
  DBMS_LOCK.release(v_lockhandle);
END;

Entonces observaremos que, al liberarse el bloqueo, la ejecución del proceso que estaba en espera en la segunda sesión termina. En la tabla V$LOCK nos encontraremos con lo siguiente:

SID TYPE ID1        LMODE REQUEST CTIME BLOCK
 97 UL   1074484814     6       0     3     0

Es decir, ahora el bloqueo exclusivo (lmode = 6) está establecido por la segunda sesión.

Artículos relacionados:
El paquete estándar PLSQL DBMS_SQL.
El paquete estándar PLSQL DBMS_OUTPUT.

Tablas Oracle: Claves naturales o claves sustitutivas

2009-01-26T08:08:00.006+01:00

En algunas ocasiones me he encontrado con bases de datos en las que se aplica la norma de, a la hora de diseñar una tabla nueva, utilizar siempre una clave sustitutiva (surrogate key) , incluso existiendo una clave natural perfectamente aplicable. Cuando he preguntado por el motivo de crear tales claves sustitutivas, la razón ha sido casi siempre la de aumentar la eficiencia de la base de datos eliminando la posibilidad de tener que enlazar dos tablas utilizando más de una columna.

Primero aclararé, para aquellos que no lo tengan claro, el concepto de clave sustitutiva o surrogate key. Una clave sustitutiva no es más que una clave interna, un identificador único, que no tiene significado para el negocio, y que identifica de forma única un registro de una tabla de la base de datos.

En mi opinión, si al definir una tabla disponemos de una clave natural, que obviamente debe ser única e inmutable, debemos utilizarla en detrimento de la clave sustitutiva. El argumento de que la utilización de claves sustitutivas va a mejorar el rendimiento de la base de datos es erróneo, y las claves naturales hacen más sencilla la escritura de nuestras consultas SQL o del código PL/SQL.

Por ejemplo, si disponemos de un par de tablas, la tabla PRODUCTOS y la tabla VERSION_PRODUCTOS, lo lógico sería utilizar para la primera tabla un campo producto_id como clave primaria (primary key) , campo que podría tratarse de una clave sustitutiva poblada por una secuencia, y usar como clave primaria de la segunda tabla una combinación de producto_id y de numero_version. Las tablas podrían definirse como sigue:

CREATE TABLE productos
( producto_id NUMBER PRIMARY KEY,
  otros_datos... );

CREATE TABLE version_productos
( producto_id REFERENCES productos,
  numero_version NUMBER,
  otros_datos...
  CONSTRAINT ver_pro_pk PRIMARY KEY 
  (producto_id, numero_version));

En nuestro ejemplo, el uso de una clave sustitutiva añadiría un trabajo extra a cualquier operación de INSERT que debería generar la clave sustitutiva, además de hacer lo propio para la clave natural. Mi opinión es que si la clave natural es inmutable y razonable, entonces debemos utilizarla. En este caso concreto, con razonable quiero significar que la clave no necesite utilizar quince campos para ser generada, sino que sólo hagan falta de dos a cinco columnas.

Problemas con los triggers SQL

2008-12-15T18:15:00.007+01:00

Mucha gente piensa que los triggers PL/SQL son una de las más potentes herramientas de las bases de datos Oracle. De hecho lo son, pero existen dos razones fundamentales por las que, personalmente, trato de evitar la utilización de triggers a la hora de implementar mis proyectos en PL/SQL.

Problemas de mantenimiento

A largo plazo, la utilización de triggers suele causar grandes dolores de cabeza a la hora de pensar en el mantenimiento. Al ser piezas del código que sólo ocurren como consecuencia de que se ha realizado otra operación, es muy frecuente que la gente se olvide de que los triggers están allí, y revisar el código pensando en todas las piezas de la base de datos que pueden afectarle, se hace poco menos que imposible.

Este problema es muy sencillo de comprender. Una persona experta en PL/SQL que no conoce una aplicación realizada en código PL/SQL y SQL, puede comprender fácilmente lo que realiza un procedimiento almacenado, pero si nuestro sistema utiliza triggers de manera rutinaria, entonces ese procedimiento puede desencadenar centenares de otro tipo de procesos y cambios en la base de datos que no son detectables a simple vista.

En conclusión, los triggers hacen que la comprensión de un sistema sea complicada, causan que su mantenimiento sea más difícil de lo normal y crean confusión al permanecer ocultos en el esquema la base de datos. Mucha gente considera que los triggers son como sentencias DDL y que, al igual que no hay necesidad de revisar una sentencia CREATE TABLE, tampoco hay necesidad de revisar el código de un trigger PL/SQL. En realidad, los triggers son subrutinas que son llamadas una y otra vez, cuyo código debe revisarse de igual manera que se revisan paquetes y procedimientos almacenados.

Uso incorrecto de los triggers

En muchos casos de los que yo he analizado, los triggers PL/SQL se han implementado de forma incorrecta. El código de los triggers suele incluir enormes errores que el programador no fue capaz de prever o anticipar. Veamos algunos ejemplos de este tipo de implementaciones incorrectas.

En alguna ocasión me he encontrado con triggers que realizan operaciones como esta:

:new.nombre_completo := 
:new.nombre||' '||:new.apellidos;

Lo primero que se me viene a la cabeza al ver una línea de código como la de arriba, es que la columna nombre_completo debería ser una columna de una vista (view) o, en Oracle 11g, una columna virtual. La columna nombre_completo es el resultado de una función trivial, con casi ningún coste de procesamiento y que, de ser necesario, podría hasta ser indexada. No existe ninguna razón para almacenar el resultado de la función en la tabla física.

Además, imaginemos que por alguna razón queremos cambiar el valor de la columna nombre_completo para algunos registros, simplemente el trigger no nos dejará hacerlo y, si no conocemos la existencia del mismo, nos preguntaremos una y mil veces por qué el campo no se actualiza al ejecutar el comando UPDATE. El trigger creará, cuando menos, confusión, y, con suerte, nos daremos cuenta de su existencia y de que debemos deshabilitarlo para poder realizar la deseada actualización del campo.

Otro de los usos incorrectos de los triggers es utilizarlos para enviar emails a los usuarios cuando se inserta o se actualiza un registro de una tabla. Veamos el siguiente trigger:

SQL> CREATE TRIGGER enviar_correo
 2   AFTER INSERT ON pedidos
 3   FOR EACH ROW
 4   BEGIN
 5   UTL_MAIL.send
 6   (sender=>'app@miempresa.com',
 7   recipients=>'pedidos@miempresa.com',
 8   subject=>'Nuevo pedido '||:new.num_pedido,
 9   message=>'Nuevo pedido recibido');
 10  END;
 11  /

Trigger created.

El trigger es sintácticamente correcto, compilará sin problemas y, si se inserta un nuevo pedido en la tabla "pedidos", nuestros usuarios recibirán un email indicándoselo. Sin embargo, el trigger tiene un grave problema. Alguien se ha preguntado qué ocurrirá si insertamos cien registros en la mencionada tabla y, por cualquier razón, decidimos deshacer las inserciones ejecutando el comando ROLLBACK. Pues simplemente lo que ocurrirá es que habremos enviado cien emails a los usuarios indicándoles que existen cien nuevos pedidos que en realidad no existen.

En este sentido conviene señalar que, para que este tipo de situaciones ocurran, no es necesario que uno mismo invoque el ROLLBACK, sino que continuamente la base de datos Oracle deshace operaciones INSERT, UPDATE, MERGE o DELETE, sin necesidad de que uno se lo indique personalmente.

Podemos decir que prácticamente cualquier trigger PL/SQL que llame a funciones UTL_ (como UTL_FILE, UTL_HTTP, UTL_MAIL, UTL_SMTP, UTL_TCP, etcétera) estará incorrectamente implementado. En conclusión, con los triggers no se debe realizar ninguna operación que no pueda ser deshecha con posterioridad.

Los triggers también pueden causar problemas cuando los programadores no entienden debidamente una de las características más interesantes de las bases de datos Oracle, el hecho de que las lecturas no bloquean las escrituras, y de que las escrituras no bloquean las lecturas. Esto se torna especialmente crítico cuando los triggers se utilizan para forzar que se cumpla alguna regla determinada. Todo esto será tema de otro artículo posterior.

Acceso remoto mediante DBLINK a una base de datos Oracle

2008-11-04T18:26:00.008+01:00

La manera más sencilla de acceder desde una base de datos Oracle a tablas y vistas (views) de otra base de datos Oracle es mediante el uso de un DBLink (enlace a base de datos). No obstante, en muchos casos puede no ser recomendable la utilización de DBLinks, ya que el acceso a tablas y vistas remotas puede provocar importantes problemas de rendimiento en ambas bases de datos, tanto la remota como la local. En la mayoría de los casos estos problemas de rendimiento se deben a la imposibilidad de utilizar los índices de las tablas remotas.

Los DBLinks se crean en la base de datos local utilizando el comando PL/SQL o SQL CREATE DATABASE LINK. Obviamente, el usuario que ejecute dicho comando debe tener los permisos necesarios para poder hacerlo. La sintaxis del comando SQL mencionado es como sigue:

CREATE [PUBLIC] DATABASE LINK Nombre_dblink
  CONNECT TO Nombre_usuario INDENTIFIED BY Contraseña
  USING 'Cadena_conexion';

En donde:

Nombre_dblink es el nombre del DBLink.

Nombre_usuario y Contraseña son los identificadores que utilizará el DBLink para conectarse a la base de datos remota. Por lo tanto, el acceso a las tablas y vistas de la base de datos remota se hará con los permisos que dicho usuario tenga sobre ellas en dicha base de datos. Si se omiten estos valores, entonces el acceso a la base de datos remota se hará con el mismo nombre de usuario y contraseña con el que el usuario en cuestión esté conectado a la base de datos local.

Cadena_conexion identifica a la base de datos remota. Puede ser el nombre de la instancia, si ésta está definida en el archivo tnsnames.ora de la base de datos origen, o también, para la versión 8 de la base de datos Oracle o posteriores, podemos utilizar directamente la definición TNS (esto posibilita que no tengamos que modificar el fichero tnsnames.ora).

Por otro lado, si definimos el DBLink como PUBLIC, todos los usuarios tendrán acceso al mismo.

A continuación os pongo algunos ejemplos del comando CREATE DATABASE LINK:

CREATE DATABASE LINK dbl_bd001
  CONNECT TO repadmin IDENTIFIED BY reppass1
  USING 'bd001';

CREATE PUBLIC DATABASE LINK dbl_bd001
  CONNECT TO repadmin IDENTIFIED BY reppass1
  USING '(description = 
           (address = 
             (protocol = tcp)
             (host = bd.miempresa.com)
             (Port = 1521) )
           (connect_data = 
             (sid = bd001) )
         )';

Una vez creado el DBLink, para referenciar una tabla o vista de la base de datos remota se ha de indicar el nombre de la tabla o vista, concatenado con el carácter "@" y el nombre que se le ha dado al DBLink. Las tablas y vistas remotas podrán usarse en consultas (sentencias SELECT) y, si la base de datos Oracle utiliza el sistema de bases de datos distribuidas, entonces se podrán ejecutar también sentencias SQL o PLSQL del tipo DELETE, INSERT, UPDATE o LOCK TABLE. Asimismo, se pueden crear sinónimos para abreviar la forma de referenciar a las tablas y vistas remotas. A continuación os pongo algunos ejemplos de sentencias:

SELECT * FROM Nombre_tabla_remota@Nombre_dblink;

INSERT INTO Nombre_tabla_remota@Nombre_dblink (campo1)
  VALUES ('prueba');

UPDATE Nombre_tabla_remota@Nombre_dblink;
  SET campo1 = 'actualizado'
  WHERE campo1 = 'prueba';

CREATE SYNONYM tremota
  FOR Nombre_tabla_remota@Nombre_dblink;

SELECT * FROM tremota;

Nota: Existe otro concepto que no he explicado aquí y es el de los DBLinks compartidos (comando CREATE SHARED DATABASE LINK), el que necesite más información sobre este concepto puede mirar en el manual de Oracle sobre sistemas de base de datos distribuidos (Oracle Distributed Database Systems). Por otro lado, también es posible acceder mediante DBLink a bases de datos remotas que no sean Oracle. Para crear un DBLink de este tipo, y antes de que nadie me pregunte, podéis realizar una búsqueda en Internet de las palabras: "Oracle Heterogeneous Services". Quizá más adelante escriba algún artículo con referencia a estos dos temas.

Generador de números aleatorios en PL/SQL

2008-09-26T00:38:00.009+02:00

Es posible que en alguna ocasión necesitéis utilizar un generador de números aleatorios en un programa PL/SQL. Oracle proporciona el paquete estándar DBMS_RANDOM para este propósito. Obviamente podemos escribir nuestra propia rutina PLSQL que genere números aleatorios, pero paquete estándar de Oracle DBMS_RANDOM es más rápido ya que llama al generador de números aleatorios interno de la base de datos Oracle.

Los procedimientos y funciones que incluye este paquete son:

INITIALIZE: Inicializa el valor de la semilla del generador de números aleatorios.

EXEC dbms_random.initialize(12345678);

NORMAL: Devuelve números aleatorios siguiendo un distribución normal.

SELECT dbms_random.normal
FROM dual;
-- Sólo valores positivos
SELECT ABS(dbms_random.normal)
FROM   dual;

RANDOM: Genera números aleatorios.

SET SERVEROUTPUT ON
DECLARE
 aleator PLS_INTEGER;
 semilla NUMBER(20); 
BEGIN
 SELECT hsecs
 INTO   semilla
 FROM   gv$timer;
 dbms_random.initialize(semilla);
 FOR i IN 1..10
 LOOP
   aleator := dbms_random.random;
   dbms_output.put_line(aleator);
   semilla := aleator; 
 END LOOP;
 dbms_random.terminate;
END;
-- Genera números positivos entre 1 y 100
SELECT (1+ABS(MOD(dbms_random.random,100)))
FROM   dual;

SEED: Reinicializa el valor de la semilla

EXEC dbms_random.seed(87654321);

STRING: Crea una cadena de caracteres aleatoria. La definición de la función es como sigue, dbms_random.string(opcion IN CHAR, longitud IN NUMBER), donde opción puede tomar los valores:

'a' o 'A': Genera una cadena aleatoria con sólo letras íncluyendo mayúsculas y minúsculas.
'l' o 'L': Genera una cadena aleatoria con sólo letras minúsculas.
'p' o 'P': Genera una cadena aleatoria con cualquier tipo de carácter imprimible.
'u' o 'U': Genera una cadena aleatoria con sólo letras mayúsculas.
'x' o 'X': Genera una cadena aleatoria con caracteres alfa numéricos en mayúsculas.

-- Genera una cadena de 10 caracteres formada
-- por letras mayúsculas y minúsculas
SELECT dbms_random.string('A', 10)
FROM   dual;

TERMINATE: Indica que hemos terminado de usar el paquete DBMS_RANDOM.

dbms_random.terminate;

VALUE: Sin parámetros devuelve un valor aleatorio entre 0 y 1 con un número fijo de decimales, pero también puede usarse con parametros para que devuelva números aleatorios entre un rango de numeros.

SELECT dbms_random.value
FROM   dual;
-- Develve números aleatorios entre 10 y 20
SELECT dbms_random.value(10, 20)
FROM dual;

A continuación podéis ver un ejemplo de programa PL/SQL que genera números aleatorios entre 0 y 1 y que no utiliza el paquete DBMS_RANDOM

DECLARE
 semilla NUMBER;
BEGIN
 semilla := EXP(TO_NUMBER(TO_CHAR(SYSDATE,'ss'))/59);
 FOR i IN 1..10
 LOOP
   semilla := 1/(semilla - TRUNC(semilla));
   semilla := semilla - TRUNC(semilla);
   dbms_output.put_line(semilla);
 END LOOP;
END;

Paquete DBMS_SQL para utilizar SQL dinámico (Dynamic SQL)

2008-08-18T08:30:00.004+02:00

El grupo de paquetes DBMS se trata de un conjunto de funciones y procedimientos que el PL/SQL de Oracle incorpora de forma estándar. Estos paquetes DBMS pueden ser de mucha utilidad cuando estamos programando en PLSQL. Hoy voy a hablar sobre el paquete DBMS_SQL que permite utilizar SQL dinámico en procedimientos almacenados y bloques PL/SQL.

Las sentencias de SQL dinámico tienen la característica de que no forman parte del código fuente PL/SQL, sino que están almacenadas dentro de cadenas de caracteres que, bien forman parte de los parámetros de entrada, o bien son construidas durante la ejecución del programa PLSQL. Esto posibilita la creación de programas de propósito mucho más general. Por ejemplo, se pueden crear procedimientos que operen sobre una tabla cuyo nombre no se conoce hasta el momento de la ejecución, ya que se trata de un parámetro de entrada de dicho procedimiento.

Además, con el paquete DBMS_SQL se pueden ejecutar cualquier tipo de sentencias, tanto DML (Data Manipulation Language - Lenguaje de manipulación de datos), como DDL (Data Definition Language - Lenguaje de definición de datos). Por lo tanto, también permite directamente desde PL/SQL crear, modificar o borrar: tablas, índices, o cualquier objeto DDL (incluso procedimientos).

En el siguiente ejemplo os muestro un procedimiento almacenado en el que, dependiendo de los parámetros de entrada, se actualiza, bien el sueldo (campo salario), bien el departamento (campo dpto), de un determinado empleado.

CREATE OR REPLACE PROCEDURE upd_tabla_empleados
(   p_campo  IN VARCHAR2
  , p_emp_id IN VARCHAR2
  , p_valor  IN VARCHAR2 ) IS

  l_cursor        INTEGER;
  -- Sentencia a ejecutar
  l_sql           VARCHAR2(500);
  -- Número de rows actualizadas
  l_updated_rows  INTEGER;
BEGIN

  l_sql := 
  'UPDATE empleados SET ' || p_campo || ' = ' ||
  p_valor || ' WHERE empleado_id = ' || p_emp_id;

  l_cursor := dbms_sql.open_cursor;
  dbms_sql.parse (l_cursor, l_sql, dbms_sql.native);
  l_update_rows := dbms_sql.execute (l_cursor);
  dbms_sql.close_cursor (l_cursor);

END;

Por lo tanto si ejecutamos el comando:

SQL> EXEC upd_tabla_empleados ('dpto', '1234', 'Compras');

Estaremos asignando el empleado con ID 1234 al departamento de compras.

Y si ejecutamos:

SQL> EXEC upd_tabla_empleados ('salario', '1122', '3000');

Estaremos asignando al empleado con ID 1122 un salario de 3.000 euros.

Integridad virtual con la base de datos Oracle 11g

2008-06-30T08:30:00.001+02:00

Supongamos que nuestra base de datos Oracle dispone de una tabla en la que uno de los campos está formado realmente por muchos subcampos concatenados. El campo en cuestión no puede modificarse pero necesitamos validar y forzar la integridad de uno de los subcampos. Por ejemplo, queremos que los valores devueltos al ejecutar la función SUBSTR(column,7,3) sobre dicho campo estén incluidos en una tabla de referencia o lookup table.

Para versiones de la base de datos Oracle anteriores a la 11g no existía una forma sencilla de lidiar con este tipo de requerimientos ya que la validación de valores almacenados en una determinada columna, obligaba a que dichos valores estuvieran almacenados en una columna real que apuntase a la tabla de referencia o lookup table. En nuestro problema no existe una columna real sino que estamos tratando con una función aplicada sobre una columna. En la versión de la base de datos Oracle 10g y anteriores, lo más razonable sería crear una columna adicional en la tabla y, mediante el uso de un trigger PL/SQL, insertar o actualizar el valor retornado por la función SUBSTR en dicha nueva columna. A continuación podéis ver el código correspondiente a la implementación de esta solución:

SQL > CREATE TABLE tabla_referencia
  2   ( validacion VARCHAR2(3) PRIMARY KEY,
  3     descripcion VARCHAR2(20) );
Table created.

SQL > CREATE TABLE tabla_datos
  2   ( concatenados VARCHAR2(20) PRIMARY KEY,
  3     validacion VARCHAR2(3) NOT NULL
  4     REFERENCES tabla_referencia );
Table created.

SQL > CREATE TRIGGER tabla_datos
  2   BEFORE INSERT OR UPDATE 
  3   OF concatenados ON tabla_datos
  4   FOR EACH ROW
  5   BEGIN
  6     :new.validacion := 
  7       SUBSTR(:new.concatenados,7,3);
  8   END;
  9   /
Trigger created.

SQL > INSERT INTO tabla_referencia (validacion)
  2   VALUES ('XYZ');
1 row created.

SQL > INSERT INTO tabla_datos (concatenados)
      VALUES ('123456XYZ012345');
1 row created.

SQL > INSERT INTO tabla_datos (concatenados)
  2   VALUES ('123456ABC012345');
INSERT INTO .....
*
ERROR at line 2:
ORA-02291: Integrity constraint violated - 
parent key not found

La solución propuesta conlleva un par de problemas. El primero se trata de un obvio problema de almacenamiento ya que estamos almacenando el mismo dato dos veces, una vez en el campo concatenados y otra en el campo validacion. El segundo problema viene dado por la necesidad de utilizar un trigger PL/SQL y las implicaciones que este hecho puede conllevar sobre el rendimiento y los tiempos de ejecución de los comandos INSERT y UPDATE.

El caso es que con la versión de la base de datos Oracle 11g podemos forzar la integridad utilizando funciones aplicadas sobre una determinada columna; es decir, no estamos limitados a utilizar columnas reales, podemos utilizar una columna virtual. Así pues, con Oracle 11g podemos prescindir del trigger PL/SQL. Veamos a continuación como debemos crear nuestra tabla de datos para conseguir que esto ocurra:

SQL > CREATE TABLE tabla_datos
  2   ( concatenados VARCHAR2(20) PRIMARY KEY,
  3     validacion AS 
  4       (SUBSTR(concatenados,7,3))
  5     REFERENCES tabla_referencia );
Table created.

En este caso hemos definido una columna virtual con el nombre concatenados, columna que es simplemente un metadato, que no se almacena de manera redundante, y que no requiere de un trigger PL/SQL para su mantenimiento, pero que si que se puede utilizar para forzar la integridad de nuestra base de datos cruzando dicho valor con los valores almacenados en nuestra tabla de referencia o lookup table.

Artículos relacionados: Oracle lanza la versión 11g de su base de datos.

Oracle 11g y la cláusula PIVOT: Como girar columnas en SQL y PL/SQL

2008-05-20T21:14:00.008+02:00

Una tarea que puede resultarnos útil en determinadas circunstancias es conseguir girar (en inglés pivot) los resultados entregados por una consulta SQL o PLSQL. Por ejemplo, supongamos que escribimos la siguiente consulta para calcular el precio medio de los productos por almacen:

SQL> SELECT almacen, producto, AVG(precio) pmedio
 2  FROM  productos
 3  GROUP BY almacen, producto
 4  /

ALMACEN PRODUCTO PMEDIO
------- -------- ------
Ba      ABC123       95
Ba      DEF456       75
Ba      XYZ987      160
Ma      ABC123      100
Ma      XYZ987      150
Va      DEF456       80

6 rows selected.

Basándonos en esta consulta, lo que queremos conseguir es que los distintos productos (ABC123, DEF456 y XYZ987) lleguen a ser las cabeceras de la consulta. Esto se puede conseguir modificando la consulta SELECT de la siguiente manera:

SQL> SELECT almacen,
 2     AVG(DECODE(producto,'ABC123',precio)) ABC123,
 3     AVG(DECODE(producto,'DEF456',precio)) DEF456,
 4     AVG(DECODE(producto,'XYZ987',precio)) XYZ987
 5   FROM productos
 6   GROUP BY almacen
 7   ORDER BY almacen
 8   /

ALMACEN ABC123 DEF456 XYZ987
------- ------ ------ ------
Ba          95     75    160
Ma         100           150
Va                 80

No obstante, aunque la consulta tal y como está escrita funciona, no podemos decir que el llegar a ella sea fácil o intuitivo. Para poder escribir de una manera más sencilla y entendible este tipo de consultas, la versión 11g de las bases de datos Oracle incorpora la nueva cláusula PIVOT. Veamos como quedaría nuestra consulta utilizando la cláusula PIVOT:

SQL> SELECT *
 2   FROM (SELECT almacen, producto, precio
 3         FROM   productos) prod
 4   PIVOT (AVG(precio) FOR producto IN
 5   ('ABC123','DEF456','XYZ987'))
 6   ORDER BY almacen
 7   /

ALMACEN 'ABC123' 'DEF456' 'XYZ987'
------- -------- -------- --------
Ba            95       75      160
Ma           100               150
Va                     80

Otro artículo del tutorial PL/SQL: La nueva sentencia SQL MERGE.

Vistas materializadas y la funcionalidad "Query Rewrite"

2008-04-14T19:46:00.010+02:00

Ya he escrito anteriormente un par de artículos sobre vistas materializadas (materialized views): uno sobre los aspectos generales de las vistas materializadas en SQL y PLSQL y otro sobre el refresco de las vistas materializadas en SQL y PL/SQL. En este artículo voy a tratar una de las funcionalidades soportadas por las vistas materializadas, funcionalidad conocida como QUERY REWRITE.

Funcionalidad de reescritura de una consulta

Esta claro que acceder a una vista materializada puede ser significativamente más rápido que acceder a todas las tablas base utilizadas al crear dicha vista materializada. Es por esta causa por la que, si así lo hemos indicado al crear la vista materializada, el optimizador Oracle, si la consulta o query lo permite, puede reescribir el plan de ejecución de dicha consulta para acceder a la vista en lugar de a las tablas base. Obviamente, la reescritura de la consulta es transparente a las aplicaciones que la estén utilizando. Así pues, de alguna manera, el uso del QUERY REWRITE es similar al uso de un índice.

Los usuarios no necesitan tener privilegios especiales sobre la vista materializada para poder utilizar la reescritura de una consulta. Cualquier consulta ejecutada por un usuario que tenga permisos sobre las tablas base involucradas, podrá hacer uso de la reescritura de la consulta mediante el acceso a la vista materializada en lugar de acceder a las tablas base directamente. Esto sólo dependerá de lo que decida el optimizador.

Por otro lado, la funcionalidad de reescritura de una consulta puede ser habilitada o deshabilitada a la hora de crear o modificar una vista materializada:

CREATE MATERIALIZED VIEW nombre_vistam
.....
[{ENABLE|DISABLE} QUERY REWRITE]
.....
AS SELECT ... FROM ... WHERE ...

Cómo determinar si el optimizador usa el QUERY REWRITE

La mejor forma para saber si el optimizador está utilizando la funcionalidad de reescritura de una consulta es utilizar el comando EXPLAIN PLAN o la funcionalidad AUTOTRACE. Otro aspecto a considerar es que, si el optimizador utiliza una vista materializada, entonces se debe observar una mejora en el tiempo de respuesta de la consulta.

Control de la funcionalidad QUERY REWRITE

La funcionalidad de reescritura de una consulta sólo puede utilizarse cuando se utiliza el optimizador Oracle basado en costes.

Por otro lado, el parámetro dinámico QUERY_REWRITE_ENABLED se utiliza para configurar una sesión o instancia de una base de datos Oracle, indicando si permitimos al optimizador el uso o no de la funcionalidad de reescritura de una consulta. Si ponemos este parámetro a FALSE, entonces habremos desactivado la funcionalidad de QUERY REWRITE para dicha sesión o instancia.

QUERY_REWRITE_INTEGRITY es otro parámetro dinámico que nos permite configurar la funcionalidad de reescritura de una consulta para una sesión o instancia. Dicho parámetro acepta los siguientes valores:

ENFORCED: Se trata del valor por defecto. Si el parámetro toma este valor, entonces el optimizador reescribirá las consultas sólo si puede garantizar la consistencia de los datos entregados. Es decir, sólo las vistas materializadas que han sido actualizadas pueden ser utilizadas por la funcionalidad.

TRUSTED: El optimizador reescribirá las consultas basándose en relaciones declaradas previamente sin necesidad de que estas hayan sido impuestas (ENFORCED). Es decir, todas las vistas materializadas que estén debidamente actualizadas serán utilizadas por la funcionalidad de reescritura de consultas.

STALE_TOLERATED: Siempre que sea posible el optimizador utilizará las vistas materializadas aunque éstas no estén actualizadas. Obviamente esto puede causar que la consulta devuelva valores incorrectos.

También existen hints que permiten influir al optimizador en la decisión de utilizar o no la funcionalidad de QUERY REWRITE al elaborar el plan de ejcución de una consulta, estos hints son REWRITE y NOREWRITE.

Nueva wiki oficial de Oracle

2008-02-28T17:52:00.004+01:00

Desde 1990, el sitio web Oracle Technology Network (OTN), ha proporcionado a la comunidad de usuarios de las bases de datos Oracle un medio informal de comunicarse e intercambiar conocimientos entre ellos mismos, así como con empleados de Oracle. En 2005, esta fórmula fue mejorada al surgir una gran colección de blogs oficiales de Oracle. No obstante, esto recursos contienen fundamentalmente información proporcionada por Oracle.

Ahora, por primera vez, clientes y asociados pueden complementar este contenido oficial con el que ha generado la comunidad OTN, todo gracias a la nueva wiki de Oracle. Para los que no sepan que es una wiki, diré que se trata de un software que permite a los usuarios editar, crear y enlazar páginas web de una manera muy sencilla. En esencia se trata de un sistema de gestión de contenidos muy fácil de usar y que facilita el cruce de enlaces y la publicación de artículos en colaboración con otros usuarios.

Con la nueva wiki de Oracle, puedes crear tu propio contenido web, incluyendo trucos y guías de instalación, documentación de proyectos, notas técnicas o cualquier otra cosa que capture tu imaginación. También se pueden publicar comentarios en las páginas web creadas por otros miembros de la comunidad Oracle. Sin duda, este mundo paralelo de Oracle formado por clientes y asociados, se trata del complemento perfecto al sitio web OTN y a los foros de discusión y blogs de Oracle.

Otro hecho que quiero mencionar es la existencia del llamado Oracle ACE Program, los Oracle ACEs son profesionales conocidos por sus profundos conocimientos en aplicaciones Oracle y por sus credenciales como entusiastas defensores de la comunidad Oracle. Los candidatos a ser Oracle ACEs pueden ser nominados por cualquier miembro de comunidad de aplicaciones y tecnología Oracle. Para poder acceder al programa Oracle ACE, los candidatos deben ser expertos que pongan su conocimiento de las aplicaciones Oracle al alcance de todos, a través de blogs, podcasts, conferencias, artículos o foros.

A continuación os pongo algunos enlaces a sitios de la Oracle Technology Network que no he mencionado anteriormente en el contenido de este artículo:

Notas de prensa
Descargas de software gratis
Documentación
Centros de tecnología
Podcasts
Artículos técnicos
Foros de discusión
Calendario de eventos para desarrolladores

Cambios de rendimiento en una sentencia SQL al activar el trazado

2008-01-30T23:54:00.002+01:00

Hace unos días un lector del blog me enviaba un email contándome un "extraño" problema de rendimiento que tenía con una sentencia SQL o PL/SQL. Dicha sentencia SQL tardaba mucho tiempo en devolver resultados y, tras activar la utilidad de trazado SQL (SQL_TRACE=TRUE), el problema desaparecía y la respuesta de la sentencia SQL era inmediata.

La verdad es que el fenómeno no es tan extraño una vez que se conoce la causa. Cuando se activa el trazado haciendo SQL_TRACE=TRUE, lo que ocurre es que la sesión Oracle utiliza una nueva área de SQL compartido. Esto supone que el parsing (ver artículo sobre las fases durante el procesamiento de una sentencia SQL) de cualquier sentencia SQL que se ejecute después de activar el trazado vuelva a tener lugar o que, de existir una versión de dicha sentencia SQL ya parseada en la nueva área de SQL compartido, dicha versión no coincida con la versión existente cuando el trazado no está activo. Esto causa que, cuando la sentencia SQL utiliza variables (bind variables), puesto que los valores reales de dichas variables son tomados en el momento del parsing, muy probablemente, los planes de ejecución de la misma sentencia SQL sean diferentes antes y después de activar el trazado al haberse generado utilizando valores de variable distintos.

Veamos un ejemplo:

SQL> CREATE TABLE pepe AS
 2   SELECT CASE WHEN rownum = 1 THEN 1
 3   ELSE 0 END pepe_id, all_tab_columns.*
 4   FROM all_tab_columns
 5   /
Table created.

SQL> CREATE INDEX pepe_idx ON pepe(pepe_id);
Index created.

SQL> BEGIN
 2   DBMS_STATS.gather_table_stats (
 3   user, 'T', method_opt=>
 4   'for all'||
 5   'indexed columns'||
 6   'size 254' );
 7   END;
 8   /
PL/SQL procedure successfully completed.

De esta manera habremos generado la tabla pepe que contendrá un registro con la columna pepe_id=1, mientras que para el resto de registros (digamos que alrededor de 75.000), dicha columna tomará el valor de 0.

Resulta pues evidente que, si utilizamos el optimizador basado en costes, al haber generado los correspondientes histogramas con el procedimiento PL/SQL gather_table_stats, los planes de ejecución serán muy diferentes si ejecutamos SELECT * FROM pepe WHERE pepe_id=1, sentencia SQL para la cual el optimizador elegirá utilizar el índice pepe_idx, o si, por contra, ejecutamos SELECT * FROM pepe WHERE pepe_id=0, sentencia para la cual el optimizador elegirá realizar un full scan de la tabla.

Pero que ocurriría si utilizamos una variable a la hora de ejecutar la sentencia SQL, es decir, si ejecutamos SELECT * FROM pepe WHERE pepe_id=:id. En este caso la respuesta varía dependiendo de cual es la versión de la base de datos Oracle que estemos utilizando:

Base de datos Oracle 8i Release 3 y anteriores

En este caso el optimizador dispone de la siguiente información, la columna pepe_id puede tomar dos valores (1 y 0) y, además, la tabla tiene unos 75.000 registros. Debido al hecho de que la columna puede tomar sólo dos valores, el viejo optimizador de la versión 8i supondrá que para cualquier valor de la variable :id, la consulta SELECT asociada devolverá aproximadamente la mitad de los registros de la tabla. Así que, la decisión más probable del optimizador será realizar un escaneado completo (full scan) de la tabla.

Bases de datos Oracle 9i y 10g

Si disponemos de una versión de la base de datos Oracle incluida entre las versiones 9i y 10g, entonces la base de datos Oracle esperará a que se suministre el valor de la variable, antes de que el optimizador decida cual es el mejor plan de ejecución. Esto es lo que se conoce como bind variable peeking (que traducido vendría a ser algo como echar un vistazo a la variable). Así pues, en lo que se refiere a nuestra consulta SELECT, el optimizador elegirá bien realizar un full scan (:id=0), o bien utilizar el índice pepe_idx (:id=1), dependiendo del valor de la variable :id.

Y aquí es donde nos podemos encontrar con un problema de rendimiento ya que, el plan de ejecución que el optimizador utilice por primera vez, será el plan de ejecución que se almacene en el área de SQL compartido, y dicho plan será el que se utilice para ejecutar nuestra sentencia SQL SELECT * FROM pepe WHERE pepe_id=:id, independientemente del valor que demos a la variable :id con posterioridad.

Entonces ya sabemos que es lo que le pasó al lector que inspiró este artículo:

a) Alguien ejecuto la consulta en cuestión utilizando un determinado valor para la variable :id.

b) El plan de ejecución correspondiente a dicho valor de la variable se almacenó en el área de SQL compartido.

c) Nuestro lector ejecuto la misma consulta pero utilizando otro valor para la variable :id. El optimizador reutilizó el plan de ejecución almacenado en el área de SQL compartido. Dicho plan no era el más eficiente para el nuevo valor de la variable, de hecho se trataba de un plan de ejecución bastante pobre. Como resultado la consulta tardó en responder más tiempo del esperado.

d) Posteriormente nuestro lector activó el trazado (SQL_TRACE=TRUE) y volvió a ejecutar la consulta pero, en este caso, al estar el trazado activo, el plan de ejecución no se sacó del área de SQL compartido, sino que se elaboró un nuevo plan de ejecución mucho más eficiente, dando como resultado que la sentencia SQL se ejecutase de forma rápida.

Posibles soluciones

Existen algunas soluciones que nos permitirán evitar este tipo de situaciones provocadas por el bind variable peeking:

a) No utilizar variables (bind variables). En el ejemplo que he puesto es bastante evidente que no es necesario utilizar variables, simplemente podemos utilizar constantes (valores literales) en nuestra cláusula WHERE. Esta es, sin duda, la mejor solución.

b) No generar estadísticas que puedan hacer variar los planes de ejecución. Obviamente, si no hubiéramos generado los histogramas de la tabla pepe, el optimizador siempre generaría el mismo plan de ejecución con independencia del valor de la variable. A veces esto puede ser recomendable, pero si no generamos estadísticas, entonces perderemos algunas de las ventajas derivadas de utilizar el optimizador basado en costes.

c) Cambiar el valor del parámetro CURSOR_SHARING para que en vez de FORCE sea SIMILAR. Si utilizamos el valor SIMILAR evitaremos que el bind variable peeking entre en juego.

d) Actualizar nuestra base de datos Oracle a la versión 11g, ya que esta versión está dotada de una nueva funcionalidad conocida como intelligent cursor sharing (compartición de cursores inteligente). Así pues, para nuestra consulta ejemplo, el optimizador de la versión 11g es capaz de identificar que un plan de ejecución no es suficiente para todos los valores posibles de la variable :id, permitiendo que se generen varios planes de ejecución distintos para una misma sentencia SQL.

Optimización SQL y PL/SQL - Código compartido

2008-01-10T18:39:00.001+01:00

Cuando alguien solicita la ejecución de algún código SQL o PL/SQL, Oracle busca dicho código o sentencia en el área de SQL compartido. Si la sentencia y su correspondiente análisis sintáctico (parsing) existen en la librería caché de la base de datos, entonces Oracle reutiliza el ejecutable correspondiente. Dentro de este área de SQL compartido, cada sentencia SQL ocupa un espacio determinado conocido como área contextual o, en terminología inglesa, cursor (ojo, esto no tiene nada que ver con los cursores PL/SQL). Cada cursor localizado en el área de SQL compartido contiene la siguiente información:

- El análisis sintáctico de la sentencia SQL (ver artículo sobre las fases del procesamiento de una sentencia SQL).

- El plan de ejecución.

- La lista de objetos de la base de datos que son referenciados por la sentencia.

Si dos usuarios ejecutan la misma sentencia SQL o PL/SQL, entonces ambos utilizaran el mismo cursor. Los beneficios de los cursores compartidos son evidentes:

- Reducción del tiempo de parsing o análisis sintáctico de la sentencia SQL o PLSQL.

- Las necesidades de memoria se ajustan dinámicamente y el uso de la misma puede mejorar de forma importante.

Los cursores sólo pueden ser compartidos por sentencias SQL que tienen las siguientes características:

- Se trata de sentencias SQL idénticas.

- El texto de las sentencias SQL debe ser exactamente el mismo, incluyendo mayúsculas, espacios, tabuladores, retornos de carro y comentarios.

- Los objetos de la base de datos Oracle a los que hace referencia la sentencia SQL deben ser también idénticos. Por ejemplo, dos tablas pueden recibir el mismo nombre pero pertenecer a diferentes usuarios o esquemas y, por tanto, tratarse a nivel de base de datos de tablas diferentes.

- Los tipos de las variables usadas en la sentencia SQL deben ser iguales (ver tipos de datos en PL/SQL). No es necesario que los nombres de las variables sean idénticos, es decir, sentencias SQL como SELECT * FROM clientes WHERE cliente_id = :c y SELECT * FROM clientes WHERE cliente_id = :d pueden ser sentencias idénticas ya que las variables c: y d: son renombradas internamente (la primera variable de una sentencia SQL es siempre renombrada como :b1, la segunda como :b2 y así sucesivamente).

Con respecto a este punto debo mencionar que la mayoría de las herramientas Oracle, tales como PL/SQL, precompiladores y Oracle Developer, realizan un preprocesado de las sentencias SQL para hacerlas tan idénticas como sea posible mediante la eliminación de comentarios y de espacios innecesarios, así como convirtiendo mayúsculas y minúsculas cuando esto es posible. SQL*Plus es la excepción y envía las sentencias SQL tal y como son escritas por el usuario.

Por esta razón es muy importante que, cuando escribamos código SQL y PLSQL, creemos código lo más genérico posible mediante:

- La utilización de procedimientos, funciones y paquetes PL/SQL almacenados.

- El uso de triggers PL/SQL.

- Hacer llamadas a triggers y procedimientos almacenados en la base de datos cuando utilicemos Oracle Developer.

- Escribir librerías de rutinas y procedimientos.

También es crucial que sigamos unos estándares a la hora de escribir código SQL:

- Seguir unos estándares para todo tipo de sentencias, incluyendo las sentencias en código PLSQL.

- Desarrollar normas en cuanto al uso de mayúsculas y minúsculas.

- Desarrollar normas en cuanto a la utilización de espacios, tabuladores y retornos de carro.

- Seguir unos estándares para los comentarios. Preferiblemente los comentarios deben mantenerse fuera de las sentencias SQL o PL/SQL.

- Utilizar los mismos nombres a la hora de referirse a objetos de la base de datos idénticos. Por ejemplo, aunque incomodo puede resultar interesante preceder a los nombres de las tablas con el nombre del esquema (o usuario) al que pertenecen.

El refresco de las vistas materializadas en SQL y PL/SQL

2007-12-18T17:58:00.001+01:00

Ya he hablado en otro artículo acerca del funcionamiento básico de las vistas materializadas (materialized views), en éste voy a exponer los distintos tipos de refresco en SQL y PLSQL que se pueden utilizar para actualizar una vista materializada con los cambios provocados por las actualizaciones en las tablas base utilizadas en la misma. El tipo de refresco que debemos elegir dependerá de la frecuencia de actualización de las tablas base y de las necesidades que tengamos de disponer de datos exactos.

Tipos de refresco

COMPLETE: Este tipo de refresco implica el borrado de los datos existentes y la reinserción de todos los datos mediante la reejecución de la consulta SELECT que define la vista materializada.

FAST: El refresco aplica sólo a los cambios realizados sobre las tablas base desde el último refresco. Puede ser de dos tipos:

- Utilizando los logs de la vista materializada: En este caso todos los cambios sobre las tablas base se almacenan en dichos logs, aplicándose en el momento del refresco a la vista materializada. Estos logs deben ser creados sobre todas las tablas base de la vista utilizando el comando CREATE MATERIALIZED VIEW LOG, es decir, necesitaremos crear un log por cada tabla base que se utilice en la vista materializada.

- Utilizando rangos ROWID: Este tipo de refresco necesita de otro tipo de logs, los llamados logs de carga directa (direct loader logs).

Conviene puntualizar que no todas las vistas materializadas pueden soportar el refresco FAST. Por ejemplo, el uso de funciones SQL como SUM, AVG, MAX, MIN o COUNT no son admitidas por este tipo de refresco.

FORCE: Es la opción por defecto si no se selecciona ningún tipo de refresco a la hora de crear la vista materializada. Este tipo de refresco funciona de la siguiente manera, si es posible la vista se refrescará utilizando el mecanismo FAST, en caso contrario se empleará la opción COMPLETE.

NEVER: Esta opción suprime todos los refrescos de la vista materializada.

Formas de refresco

Refresco manual: Los refrescos manuales de las vistas materializadas se realizan utilizando el paquete PL/SQL estándar DBMS_MVIEW. Este paquete incluye un buen número de funciones y procedimientos PLSQL que permiten gestionar las vistas materializadas. Entre ellos cabe destacar:

DBMS_MVIEW.REFRESH ('nombre_vista_materializada') - Refresca una vista materializada específica.

DBMS_MVIEW.REFRESH_DEPENDENT ('nom_tab1, nom_tab2, ...') - Refresca todas las vistas materializadas que utilicen como tabla base alguna de las tablas o vistas materializadas indicadas en la lista (los nombres de las tablas o vistas materializadas deben separarse mediante comas).

DBMS_MVIEW.REFRESH_ALL_MVIEWS (n) - Refresca todas las vistas materializadas del sistema devolviendo un entero (n) que indica el número de registros que se han refrescado.

Todos estos procedimientos y funciones admiten parámetros adicionales entre los que cabe mencionar: el tipo de refresco (? - force, f - fast, c - complete), el segmento de rollback que se debe usar durante el refresco, si se continúa (true) o no (false) realizando el refresco tras detectar un error, si el refresco de todas las vistas materializadas se realiza en una sola transacción de manera que si falla el refresco de una vista falla el refresco de todas (true) o de si cada vista materializada se refresca en transacciones separadas (false).

Los refrescos manuales requieren que los parámetros del sistema JOB_QUEUE_PROCESSES y JOB_QUEUE_INTERVAL estén configurados para permitir la ejecución de trabajos encolados.

Refresco automático: Esta forma de refresco puede realizarse de dos formas:

ON COMMIT: La vista materializada se refresca cada vez que se ejecuta un COMMIT sobre alguna de las tablas base de la vista. Esto significa que la ejecución del COMMIT tomará más tiempo lo que puede afectar seriamente al rendimiento. Yo personalmente no recomiendo este tipo de refresco.

Refresco programado: El refresco se programa para que ocurra a una hora o tiempo determinado. Por ejemplo, una vista se podría programar para que se refresque todos los días a una determinada hora mediante el uso de las cláusulas START WITH (seguido de la hora en formato datetime del primer refresco automático) y NEXT (seguido de una expresión en formato datetime que se utilizará para calcular el intervalo entre refrescos automáticos). En este caso también es necesario configurar correctamente el parámetro del sistema JOB_QUEUE_PROCESSES.

Ejemplo:

CREATE MATERIALIZED VIEW nombre_vm
...
REFRESH START WITH ROUND(SYSDATE + 1) + 9/24
NEXT NEXT_DAY(TRUNC(SYSDATE), 'TUESDAY') + 16/24
AS SELECT ...;

En caso del ejemplo, la base de datos Oracle refrescará automáticamente la vista materializada mañana a la 9:00 AM y posteriormente todos los martes a la 4:00 PM.

SQL y PL/SQL - La nueva sentencia MERGE

2007-11-26T18:49:00.001+01:00

La sentencia MERGE, a la que muchos denominan UPSERT debido a su funcionalidad, está disponible desde la aparición de la versión 9i de la base de datos Oracle. Se trata de una de las funcionalidades del kernel de Oracle más utiles a la hora de permitir el uso de la tecnología ETL (Extract, Transform and Load - Extraer, Transformar y Cargar) con las bases de datos Oracle. Este tipo de tecnología puede utilizarse y está especialmente enfocada para ser usada en aplicaciones de data warehousing (almacen de datos). Básicamente, lo que permite la sentencia SQL MERGE es, dependiendo de una condición lógica, actualizar registros (UPDATE) cuando la condición se cumple, o insertar registros (INSERT) cuando dicha condición no se cumple, de ahí surge la denominación de UPSERT.

Anteriormente a la versión 9i de Oracle, la alternativa en SQL era ejecutar dos sentencias DML, un UPDATE y un INSERT, cada una utilizando condiciones lógicas opuestas. En cuanto a las alternativas en PL/SQL eran: bien intentar insertar un registro y si la sentencia INSERT fallaba debido a una excepción PLSQL del tipo DUP_VAL_ON_INDEX, entonces realizar un UPDATE del registro en cuestión; bien intentar actualizar un registro y si la sentencia UPDATE devolvía SQL%NOTFOUND, entonces ejecutar la correspondiente sentencia INSERT.

Las ventajas de la sentencia SQL MERGE son claras:

- Permite con una misma sentencia realizar un UPDATE si el registro existe, o un INSERT si se trata de un nuevo registro.

- La sentencia puede paralelizarse de forma transparente.

- Se evita la necesidad de realizar actualizaciones múltiples.

- Es especialmente útil para realizar operaciones en masa y, como ya he mencionado, en aplicaciones de data warehousing.

- El rendimiento de la base de datos mejora ya que, al necesitarse menos sentencias SQL para realizar las mismas operaciones, también se necesitan menos accesos a las tablas fuente.

Ejemplo de sentencia MERGE

En el presente ejemplo voy a partir del hecho de que tenemos dos tablas en un data warehouse, una de ellas con muchos registros denominada clientes y otra más pequeña denominada datos_cli cuyos registros deben insertarse en la tabla clientes. En pocas palabras, estoy suponiendo que la tabla datos_cli contiene los cambios a realizar sobre la tabla clientes. La sentencia SQL MERGE que debemos utilizar para actualizar la tabla cliente podría ser la siguiente:

MERGE INTO clientes cli USING datos_cli dac
  ON (cli.cliente_id = dac.cliente_id)
  WHEN MATCHED THEN
    UPDATE SET 
      cli.nombre = dac.nombre,
      cli.direccion = dac.direccion
  WHEN NOT MATCHED THEN
    INSERT (cliente_id, nombre, direccion)
    VALUES (dac.cliente_id, dac.nombre, dac.direccion);

Así pues, la sentencia MERGE del ejemplo realizará las siguientes operaciones:

- Si existe un registro en datos_cli con el mismo ID de otro registro en la tabla clientes, entoces actualizará el valor de los campos nombre y dirección de la tabla clientes con los valores correspondientes de la tabla datos_cli.

- Si no existe un registro en clientes con el ID del registro de datos_cli que está siendo procesado, entonces se insertará dicho registro en la tabla clientes.

En resumen, la sintaxis de la sentencia MERGE debe incorporar:

Una cláusula INTO, que especifica la tabla destino donde los registros serán actualizados o insertados.

Una cláusula USING, especificando el origen de los datos que van a ser insertados o que van a servir para actualizar la tabla destino. El origen de los datos puede tratarse de una tabla, una vista, o del resultado de la ejecución de una consulta SELECT.

Una cláusula ON, que especifica la condición bajo la cual se realizará, bien la operación UPDATE (si la condición se cumple), bien la operación INSERT (si la condición no se cumple).

Las cláusulas WHEN MATCHED | NOT MATCHED, que son las que indican a la base de datos Oracle que acción realizar si se cumple o no se cumple la condición del ON. Estas dos cláusulas se pueden poner en cualquier orden.

Artículos relacionados:
La sentencia INSERT multitabla de la versión 9i de Oracle.
La cláusula WITH de la versión 9i de la base de datos Oracle.

SQL y PL/SQL - La sentencia INSERT multitabla de Oracle 9i

2007-11-06T17:57:00.001+01:00

La versión 9i de las bases de datos Oracle ha introducido la posibilidad de utilizar sentencias INSERT multitabla. Así pues, la sentencia SQL o PLSQL INSERT... SELECT ha cambiado ligeramente su sintaxis, de manera que ahora permite la inserción de datos en más de una tabla de la base de datos de forma paralela. Existen dos formas de utilizar el comando INSERT multitabla: no condicional y condicional. En la forma no condicional, una cláusula compuesta INTO se ejecuta cada vez que la consulta SELECT devuelve un registro. En la forma condicional, las cláusulas compuestas INTO figuran dentro de cláusulas WHEN a partir de las que se determina si la correspondiente cláusula compuesta INTO se ejecuta o no.

Una claúsula compuesta INTO consiste de una o más cláusulas INTO. Una cláusula INTO debe especificar la tabla de la base de datos sobre la que se van a insertar los datos. Esta cláusula no admite alias. La cláusula INTO tambien proporciona el valor del los campos a ser insertados mediante la cláusula VALUES. La expresiones usadas en la cláusula VALUE pueden tratarse de cualquier expresión permitida, pero siempre debe hacer referencia a columnas devueltas por la consulta SELECT incluida en el INSERT.

Veamos un ejemplo de la forma no condicional:

INSERT ALL
 INTO productos
 VALUES (producto_id, producto, cantidad)
 INTO ventas
 VALUES (cliente_id, producto_id, cantidad, total)
SELECT cliente_id,
      producto_id,
      producto,
      SUM(cantidad) cantidad,
      SUM(precio) total
FROM   pedidos, lineas_pedidos
WHERE  pedidos.pedido_id = lineas_pedidos.pedido_id
GROUP BY cliente_id, producto_id, producto;

En el ejemplo vemos como la sentencia INSERT se utiliza para insertar los valores producto_id, producto y cantidad en la tabla productos y los valores cliente_id, producto_id, cantidad y total en la tabla ventas, todo ello utilizando una sola sentencia SQL o PL/SQL. Resulta obvio comentar que la sentencia que he utilizado como ejemplo, podría escribirse de forma alternativa mediante el empleo de dos sentencias INSERT.

La forma condicional de las sentencias SQL o PLSQL INSERT multitabla dispone, a su vez, de dos opciones:

1) Forma condicional FIRST, sólo se insertan los valores especificados en la primera condicion verdadera.

2) Forma condicional ALL, se insertan los valores especificados en todas las condiciones verdaderas.

La sintaxis sería:

INSERT [ALL/FIRST]
WHEN  THEN
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
WHEN  THEN
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
ELSE
 INTO  (col, col, ...) VALUES (val, val, ...)
 .....
 INTO  (col, col, ...) VALUES (val, val, ...)
SELECT ...;

Ventajas de las sentencias INSERT multitabla en SQL y PL/SQL

1) Eliminan la necesidad de utilizar múltiples sentencias INSERT ... SELECT para añadir registros en varias tablas de la base de datos.

2) Elimina la necesidad de utilizar un procedimiento PL/SQL para realizar diferentes inserciones de registros dependiendo de diferentes condiciones lógicas. Esto ya se puede realizar con una única sentencia SQL del tipo INSERT ... SELECT, mediante el uso de la cláusula WHEN.

3) Mejora el rendimiento significativamente ya que la consulta SELECT correspondiente, al existir un sólo comando INSERT, sólo tienen que ejecutarse una vez, en lugar de tener que repetirse su ejecución en cada INSERT.

Artículos relacionados: La nueva cláusula WITH de la versión 9i de la base de datos Oracle.

Oracle lanza la base de datos Oracle 11g

2007-10-12T14:48:00.002+02:00

Oracle ha lanzado la base de datos Oracle 11g, disponible globalmente sobre plataforma Linux, y que ha sido especialmente desarrollada para ayudar a los clientes a abordar los retos derivados de un entorno empresarial cada vez más cambiante y competitivo, la necesidad de manejar grandes volúmenes de datos y la posibilidad de ofrecer una mayor calidad de servicio y reducir a la vez los costes tecnológicos asociados.

La base de datos Oracle Database 11g proporciona nuevas funcionalidades que garantizan un alto rendimiento, una alta escalabilidad, fiabilidad y seguridad, permitiendo el uso de plataformas grid y asegurando altos niveles de calidad de servicio e incrementos de la flexibilidad de negocio reduciendo además los costes de explotación. Con la base de datos Oracle 11g los clientes pueden resolver los problema más exigentes en todas las áreas, incluyendo aplicaciones transaccionales, de inteligencia de negocio y de gestión de contenidos.

Nuevas funcionalidades de la base de datos Oracle 11g

Secure Files: gestión eficiente y segura de todo tipo de datos. Permite la gestión de todo tipo de datos, incluyendo imágenes, ficheros de texto o tipos avanzados de datos, como XML, imágenes médicas y objetos en 3D.

Information Lifecycle Management y Oracle Advanced Compression: permite realizar una gestión eficaz del ciclo de vida de los datos y reduccir los costes de almacenamiento. Oracle Database 11g ha mejorado significativamente las funcionalidades de particionamiento y compresión de datos, con importantes novedades para mejorar la gestión del almacenamiento y del ciclo de vida de la información. La base de datos Oracle 11g también dispone de funcionalidades avanzadas de compresión de datos para información estructurada y no estructurada (LOBs), consiguiendo ratios de compresión del orden de 2 ó 3.

Oracle Real Application Testing: permite reducir el tiempo, el riesgo y los costes derivados de la realización de cambios y mejoras. Esto es debido a que Oracle 11g permite realizar pruebas simulando entornos de producción con numerosos usuarios interactuando con la base de datos.

Oracle Active Data Guard: facilita el incremento del ROI de las plataformas utilizadas para recuperación de desastres. Con Oracle 11g se puede utilizar las bases de datos en modo standby. Las bases de datos en standby pueden utilizarse para generar informes, realizar backups y testear cambios y mejoras realizados en los sistemas de producción, proporcionando por lo tanto un ROI mayor cuando se invierte en sistemas para la recuperación de desastres (Disaster Recovery).

Oracle Total Recall: permite realizar un rastreo eficaz de cambios, asegurando el cumplimiento de normativas de seguridad. Esta funcionalidad permite el acceso transparente a los datos en momentos del tiempo configurados por el administrador. Oracle Total Recall permite la realización sencilla de operaciones de rastreo de cambios, auditoría de modificaciones y análisis para el cumplimiento de regulaciones de seguridad.

Arquitectura de máxima disponibilidad: Oracle Database 11g ofrece importantes innovaciones para garantizar la disponibilidad de los datos ante paradas planificadas y no planificadas. Se incluyen mejoras para conseguir mejorar los tiempos de backup y recuperación de grandes volúmenes de información y funcionalidades de hot patching que mejoran la disponibilidad de los sistemas al permitir la aplicación de parches sin necesidad de apagar las bases de datos. El nuevo asistente Data Recovery Advisor ayuda a los administradores a reducir de manera muy significativa los tiempos de parada ofreciendo la automatización de muchas de las tareas asociadas, incluyendo tanto el diagnóstico de los problemas, como el establecimiento del plan de recuperación.

Transparent Encryption: encriptación transparente de los datos. Con la base de datos Oracle 11g aumentan las funcionalidades de encriptación de datos, incluyendo no sólo la encriptación de columnas, sino también la encriptación de tablas completas, índices y tipos de dato LOB. Todo esto implica una seguridad de los datos mayor.

Gestión de datos XML: Oracle Database 11g mejora de manera significativa las funcionalidades de XML DB. Se incluye el soporte a formatos binarios de XML (Binary XML), una importante innovación que proporciona un rendimiento superior en la gestión de datos XML.

Cubos OLAP embebidos: Oracle Database 11g también proporciona innovaciones importantes en el ámbito de los Data Warehouses. Los cubos OLAP ahora pueden comportarse como vistas materializadas en la base de datos, permitiendo que los desarrolladores puedan utilizar el estándar SQL y PLSQL para realizar operaciones de consulta, pero manteniendo los beneficios en el rendimiento derivados del uso de cubos OLAP.

Mejoras en los pool de conexiones y en los caches de de resultados: la base de datos Oracle 11g ofrece nuevas funcionalidades para mejorar los tiempos de respuesta de las aplicaciones, mejorando el rendimiento. Con Connection Pooling y Query Results Cache los resultados de las queries más utilizadas son almacenadas y reutilizadas de manera transparente a las aplicaciones, mejorando los tiempos de respuesta y haciendo un uso más efectivo de los recursos hardware disponibles.

Mejoras en la automatización y en la auto administración de la base de datos: Con el objetivo de reducir los costes de gestión de los sistemas e incrementar el rendimiento, la disponibilidad, la escalabilidad y la seguridad, la base de datos Oracle 11g introduce un conjunto de nuevas funcionalidades en la línea de la auto administración de la base de datos. Estas funcionalides incluyen asistentes para reducir las tareas de particionamiento y ciclo de vida de los datos, funcionalidades que permiten la optimización automática de sentencias SQL y PL/SQL o un nuevo Support Workbench que proporciona una interfaz simple para visualizar incidencias en la base de datos.

Facilita el desarrollo de aplicaciones: Oracle Database 11g incorpora un nuevo compilador Java just-in-time que proporciona un alto rendimiento en la ejecución de procedimientos almacenados en Java sin necesidad de utilizar compiladores de terceros. También se incorpora una integración nativa con Visual Studio 2005 para facilitar el desarrollo de aplicaciones .NET con Oracle.

La claúsula WITH en SQL y PL/SQL

2007-09-25T18:35:00.001+02:00

La versión 9i de las bases de datos Oracle permite el uso de la claúsula WITH en SQL y PLSQL. Este comando permite reusar una consulta SELECT cuando esta hay que utilizarla más de una vez en una sentencia o consulta SQL compleja. Los resultados de la consulta definida en la claúsula WITH son almacenados en una tabla temporal pudiendo de esta forma mejorar el rendimiento de la sentencia principal.

Aunque no siempre conseguiremos mejorar el rendimiento utilizando la claúsula WITH, lo que sin duda facilitaremos es la lectura y el mantenimiento del código PL/SQL o SQL. Dentro de la claúsula WITH daremos un nombre a las consultas SELECT a reutilizar (WITH admite la definición de múltiples consultas con sólo separarlas por comas), dicho nombre será visible para todas las consultas definidas posteriormente dentro del mismo WITH. Obviamente, también será visible para la sentencia o consulta principal.

Uso de la claúsula WITH

En el siguiente ejemplo encontraremos todos las divisiones de una empresa cuyos empleados tienen un salario medio un 10 por ciento por encima del salario medio de la empresa:

WITH salario_division AS (
SELECT division,
         AVG(salario) salario_medio
FROM   empleados
GROUP BY division )
SELECT division,
       salario_medio
FROM   salario_division
WHERE salario_medio > (
       SELECT AVG(salario_medio) * 1.1
       FROM   salario_division )
ORDER BY salario_medio;

Como se puede observar en la consulta existen dos bloques, una consulta principal y una subconsulta. Ambas necesitan realizar operaciones agrupando datos. Reescribamos la sentencia sin utilizar la claúsula WITH:

SELECT division,
       AVG(salario) salario_medio
FROM   empleados
GROUP BY division
HAVING AVG(salario) > (
       SELECT AVG(salario) * 1.1
       FROM   empleados )
ORDER BY AVG(salario);

Comparando ambas sentencias podemos concluir que al utilizar la claúsula SQL WITH y almacenar en una tabla temporal la consulta que hace el GROUP BY, evitamos que se tengan que agrupar los datos más de una vez. Este hecho debe hacernos pensar que el rendimiento de la sentencia que utiliza la claúsula WITH debe ser mejor que el de la sentencia que no lo utiliza.

Características de la claúsula WITH

a) Sólo se puede usar en sentencias SELECT.

b) Cuando se define una consulta con el mismo nombre de una tabla existente en la base de datos Oracle, puesto que el analizador sintáctico o parser (ver fases en la ejecución de una sentencia SQL) de las sentencias SQL o PLSQL busca de dentro a fuera, el nombre dentro de la claúsula WITH tendrá prioridad frente al nombre de la tabla.

c) Puede contener más de una consulta. Cada consulta se separa mediante comas. Las consultas definidas después de otras consultas pueden utilizar las definiciones previas.

Artículos relacionados:
Vistas materializadas o materializaed views.
Cursores PL/SQL.
Tablas externas en el SQL de Oracle.

Hints avanzados en PL/SQL para forzar la forma de acceder a una tabla

2007-08-31T15:14:00.003+02:00

Ya he hablado anteriormente acerca de los hints PLSQL más comunes (FULL, ROWID, INDEX, NO_INDEX) para forzar el método de accesso a una tabla Oracle. En este artículo voy a presentar algunos hints más de este tipo, que se utilizan menos frecuentemente, pero no por ello menos útiles.

CLUSTER (nombre_de_tabla): Fuerza el accesso a la tabla indicada utilizando un índice de tipo cluster. Los índices de este tipo se utilizan para localizar registros que comparten valores comunes de una forma rápida. La clave del índice cluster puede estar constituida por una o más columnas de la tabla. Los registros de la tabla son agrupados según la clave y almacenados físicamente juntos en el disco duro.

Es decir, suponiendo que tenemos una tabla de empleados, si definimos un índice cluster basado en el país del empleado, cuando un usuario inserta un nuevo empleado, el índice cluster asegura que el nuevo empleado se almacene en el mismo bloque de datos donde figuran los empleados correspondientes a ese país.

Nota: Pueden definirse clusters en los que se almacene más de una tabla. Obviamente la única limitación es que dicho cluster se defina sobre columnas con valores comunes.

HASH (nombre_de_tabla): Fuerza a que la tabla se combine utilizando un algoritmo de hash. Sólo aplica a los clusters tipo hash que utilizan una función hash para calcular la localización de los registros de una tabla Oracle. En este caso, los registros son almacenados en el disco duro según sea el valor devuelto por la función hash. La función se utiliza para localizar los registros sin necesidad de utilizar el tiempo de entrada/salida o de CPU que requiere la búsqueda a través de un índice normal.

La unión de tablas a través de un cluster hush es recomendable para tablas con un gran número de registros ya que, para dichas tablas, los índices pueden estar estructurados en mucho niveles, siendo el acceso al índice más lento.

HASH_AJ (nombre_de_tabla): Transforma una subquery NOT IN en una anti-unión de tipo hash.

Una anti-únión (anti-join) es una forma de unir tablas que utiliza una lógica inversa. En vez de devolver registros cuando ambas partes cumplen con una condición, una anti-unión devuelve aquellos registros de una de las partes que no están en la otra (exactamente lo que hace una subquery NOT IN).

Nota explicativa: Una subquery no es más que una sentencia SELECT que está dentro de otra sentencia SQL o PL/SQL.

El optimizador PL/SQL basado en normas (Rule-Based Optimizer)

2007-07-26T19:06:00.001+02:00

En este artículo voy a mencionar algunas de las características del optimizador PL/SQL basado en normas (Rule-Based Optimizer). Lo primero que quiero mencionar es que Oracle recomienda utilizar el optimizador PLSQL basado en costes (cost-based optimizer), no obstante, en algunos casos, el hecho de tener que activar las estadísticas de la base de datos para poder utilizar este último optimizador, puede hacer que resulte interesante utilizar el optimizador basado en normas y dejar las estadísticas desactivadas para no afectar al rendimiento de la base de datos.

El optimizador PLSQL basado en normas utiliza siempre que puede los índices, incluso cuando las tablas son pequeñas o cuando el número de registros que devuelve la sentencia SELECT es un porcentaje elevado con respecto al número total de registros de la tabla, casos para los que es mejor realizar un escaneado total (full scan) ya que la respuesta es más rápida (mejora el rendimiento). Esto es debido a que el optimizador basado en normas no hace uso de valores estadísticos, tales como el número total de registros de una tabla.

El optimizador PL/SQL basado en normas hace uso del siguiente orden de prioridades para determinar cual va a ser la forma de acceder a las tablas y determinar finalmente cual va a ser el plan de ejecución:

Prio Forma de acceso
   1 Single row by ROWID
   2 Single row by cluster join
   3 Single row by hash cluster key with unique or
      primary key
   4 Single row by unique or primary key
   5 Cluster join
   6 Hash cluster key
   7 Indexed cluster key
   8 Composite index
   9 Single-column index
  10 Bounded range search on indexed column
  11 Unbounded range search on indexed column
  12 Sort-merge join
  13 MAX or MIN of indexed column
  14 ORDER BY on indexed column
  15 Full table scan

Los distintos métodos de acceso los he dejado en inglés, ya que es bastante complicado traducir esta terminología. En el presente artículo no voy a explicar cuales son las diferencias existentes entre las distintas formas de acceso. No obstante, en sucesivos artículos pondré algunos ejemplos que permitirán diferenciar estos conceptos.

Siguiendo con el tema que concierne a este post, el optimizador basado en normas analiza la sintaxis de la sentencia SQL para establecer los distintos métodos de acceso a las tablas. Básicamente lo que hace es determinar todas las formas de acceso posibles y escoger aquella que tiene una prioridad menor.

Este esquema siempre asume que un escaneado total (full scan) es el peor método de accesso (prioridad 15). Sin embargo, ya he mencionado al principio del artículo que esto no siempre es verdad.

Estos métodos de acceso, así como otros adicionales, están también disponibles para el optimizador PL/SQL basado en costes. Sin embargo, este optimizador ignora las prioridades, y determina el coste esperado de ejecución de la sentencia SQL para cada uno de las formas de acceso posibles basándose en las estadísticas, escogiendo después aquella forma de acceso con el menor coste estimado. Muchas funcionalidades del Oracle, como los hash joins, star queries e histogramas, sólo están disponibles para el optimizador PLSQL basado en costes.

Artículos relacionados:
- Hints PLSQL para determinar el modo de optimización.
- Hints PL/SQL para forzar la forma de accesso.

Análisis de la salida del comando TKPROF en PL/SQL y SQL

2007-07-12T18:19:00.001+02:00

Ya hemos hablado en otro artículo acerca de las opciones y parámetros del comando TKPROF y de su uso para analizar los fichero de trazado PLSQL. Ahora es el momento de escribir acerca de cómo interpretar la salida de dicho comando. La salida del comando TKPROF muestra las estadíticas resultantes de la ejecución de una sentencia SQL o PLSQL agrupadas por fases de procesamiento. En el artículo "Pasos seguidos durante la ejecución de una sentencia SQL", ya hice una exposición detallada de lo que acontece en cada una de estas fases, no obstante, a continuación, voy a realizar un pequeño resumen.

Fases de procesamiento analizadas por el comando TKPROF

Fase de parsing (parse): Durante este paso se genera el plan de ejecución de la sentencia SQL o PL/SQL, se comprueban los permisos de ejecución y se verifica la existencia de los objetos referenciados en dicha sentencia (tablas, vistas, columnas, etcétera).

El parsing puede ser duro (hard) o blando (soft). El parsing duro se realiza cuando se genera el plan de ejecución, incluyendo la optimización del mismo, y dicho plan de ejecución se almacena en la librería cache. El parsing blando hace referencia a que cuando la sentencia SQL o PLSQL es enviada al kernel para generar el plan de ejecución, el kernel encuentra dicho plan en la librería cache, por lo que no es necesario generar el plan de ejecución, y sólo se necesitan verificar los permisos de acceso a los objetos Oracle utilizados en la sentencia.

Fase de ejecución (execute): Este paso se corresponde con la ejecución propiamente dicha de la sentencia SQL o PLSQL por parte del servidor de la base de datos Oracle. Para las sentencias INSERT, UPDATE y DELETE, este paso implica la modificación de los datos y, a veces, su ordenación. Para la sentencia SELECT, esta fase supone la identificación de los registros seleccionados.

Fase de fetching (fetch): Durante este paso el servidor Oracle devuelve los registros resultantes de la ejecución de una sentencia SELECT y, si es necesario, los ordena. Esta fase sólo afecta a las sentencias SELECT.

Columnas del informe

Las estadísticas entregadas por el comando TKPROF se organizan en siete categorías o columnas:

Count: Número de veces que una sentencia SQL o PL/SQL fue analizada según fases. A la hora de interpretar los resultados que aparecen en las otras columnas del informe generado por el comando TKPROF, es importante comprobar si esta columna toma valores mayores que uno, ya que si hemos utilizado la opción AGGREGATE=YES, el comando TKPROF agrupará la ejecución de todas las sentencias SQL o PLSQL idénticas en una sola tabla de resultados.

CPU: Tiempo total en segundos de uso de CPU según fases.

Elapsed: Lapso de tiempo total en segundos según fases.

Disk: Número de bloques de datos físicamente leídos desde disco según fases.

Query: Número de bloques de memoria intermedia (buffers) leídos en modo consistente. Las lecturas consistentes se producen generalmente para las sentencias SELECT. Una lectura consistente indica que los registros leídos que han sido modificados y que no han sido confirmados mediante el comando COMMIT, tienen que reconstruirse a partir de los segmentos de rollback.

Current: Número de bloques de memoria intermedia (buffers) leídos en modo normal (current). Las lecturas en modo normal se producen generalmente para las sentencias DML (sentencias de manipulación de datos).

Rows: Número de registros procesados por la sentencia SQL o PL/SQL. Este valor no incluye los registros procesados por las subqueries (sentencias SELECT incluídas en otras sentencias SQL o PLSQL). Para las sentencias SELECT, el número de registros devueltos se mostrará en la fase de fetching. Para las sentencias INSERT, UPDATE y DELETE, el número de registros procesados se mostrará en la fase de ejecución.

Ejemplo de salida resultado de la ejecución del comando TKPROF:

select categoria, trabajo, nombre, salario
from empleados, salarios
where salario between infsal and supsal
order by categoria, trabajo

call    count   cpu elapsed disk query current rows
------- ----- ---- ------- ---- ----- ------- ----
Parse       1 0.06    0.08    2    18       1    0
Execute     1 0.00    0.00    0     0       0    0
Fetch       1 0.01    0.01    1    11      12   14
------- ----- ---- ------- ---- ----- ------- ----
total       3 0.07    0.09    3    29      13   14

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 8 (SCOTT)

Rows     Execution Plan
------- -------------------------------------------
      0 SELECT STATEMENT   GOAL: CHOOSE
     14   SORT (ORDER BY)
     14    NESTED LOOPS
      5     TABLE ACCESS (FULL) OF 'SALARIOS'
     70     TABLE ACCESS (FULL) OF 'EMPLEADOS'

Obviamente, si el fichero de trazado que estamos analizando con el comando TKPROF, contiene información sobre la ejecución de varias sentencias SQL o PL/SQL, la salida mostrará la información arriba indicada para cada una de las sentencias SQL que han sido analizadas.

Identificación de tablas y columnas en una base de datos Oracle mediante sentencias SQL

2007-06-26T18:16:00.001+02:00

Una vez que una tabla Oracle es creada utilizando el comando CREATE TABLE, la estructura de dicha tabla se puede visualizar utilizando las siguientes vistas (views) del sistema de la base de datos Oracle:

DBA_TABLES: Muestra la información de la tabla a nivel de cabecera.

DBA_TAB_COLUMNS: Muestra la información de la tabla a nivel de columna.

DBA_TAB_PRIVS: Muestra los privilegios de acceso a las tablas de los usuarios de la base de datos.

DBA_COL_PRIVS: Muestra los privilegios de acceso a nivel de columna. Es bastante raro tener la necesidad de definir permisos a nivel de columna, de hecho, yo nunca me he encontrado con una bases de datos Oracle que utilice esta funcionalidad.

A nivel de cabecera, es decir en la vista DBA_TABLES, se muestran datos como el esquema al que pertenece la tabla (columna OWNER), el nombre de la tabla (TABLE_NAME), el nombre del tablespace donde se almacenan físicamente los datos contenidos en la tabla (TABLESPACE_NAME), el número de registros que contiene la tabla (NUM_ROWS), el número de bloques que ocupan físicamente los datos (BLOCKS), así como otros datos estadísticos que son utilizados por el optimizador basado en costes para generar los planes de ejecución de las sentencias SQL o PL/SQL.

La vista DBA_TABLES se puede unir con la vista DBA_TAB_COLUMNS mediante los campos OWNER y TABLE_NAME. Esta última vista del sistema Oracle nos dará información acerca los nombres de las columnas (COLUMN_NAME), del tipo de dato SQL almacenado en dicha columna (DATA_TYPE), de las dimensiones o tamaño de la columna (DATA_LENGTH) y nos indicará si la columna permite o no tomar valores nulos o en blanco (NULLABLE). También esta vista del sistema Oracle muestra algunos valores estadísticos que son utilizados por el optimizador.

La vista DBA_TAB_PRIVS se une de igual manera que la DBA_TAB_COLUMNS con la vista DBA_TABLES. Las columnas fundamentales de la vista DBA_TAB_PRIVS dan información sobre cual es el usuario al que se le concede un permiso determinado (GRANTEE), cual es el usuario que concedió el permiso (GRANTOR), cual es el permiso concedido (PRIVILEGE) y si el usuario al que se le concede el permiso puede a su vez conceder dicho permiso a otros usuarios (GRANTABLE).

Los permiso de acceso que se pueden asignar a una tabla son los siguientes: ALL, ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT y UPDATE. Además, cuando se dan permisos de acceso al usuario genérico PUBLIC, automáticamente se están asignando dichos permisos a todos los usuarios de la base de datos Oracle.

A continuación encontraréis un par de ejemplos de sentencias SQL con los que podréis:

1) Encontrar el nombre de una tabla de la que sólo recordáis parte del nombre (la sentencia SQL del ejemplo devolverá todas las tablas y el esquema al que pertenecen que contienen en su nombre la secuencia de caracteres EMPL):

SELECT DISTINCT owner, table_name
FROM dba_tables
WHERE table_name LIKE '%EMPL%'

2) Encontrar las tablas que tienen una columna con un nombre específico (la sentencia SQL del ejemplo devolverá todas las tablas que tienen una columna cuyo nombre es CLIENTE):

SELECT DISTINCT owner, table_name, column_name
FROM dba_tab_columns
WHERE column_name = 'CLIENTE'

3) Listar los permisos que un determinado usuario tiene sobre un esquema de la base de datos determinado (la sentencia SQL del ejemplo mostraría los permisos del usuario JLOPEZ sobre el esquema FACTURAS):

SELECT owner, table_name, grantee,
privilege, grantable
FROM dba_tab_privs
WHERE owner = 'FACTURAS'
AND grantee = 'JLOPEZ'

Nota: Es importante utilizar mayúsculas a la hora de tratar de identificar un esquema, una tabla, una columna o un usuario, ya que sus nombres son almacenados con este formato.

Aclaración: Es posible que un determinado usuario tenga acceso a una tabla y no aparezca ningún registro para dicho usuario en la tabla DBA_TAB_PRIVS. Estas son algunas de las posibles razones por las que esto puede ocurrir:

1) La tabla en cuestión puede estar habilitada para ser accedida públicamente, es decir, tiene permisos de acceso el usuario genérico PUBLIC.

2) Un determinado rol (ROLE) tiene permiso de acceso sobre la tabla y el usuario en cuestión pertenece a dicho rol (ver tablas DBA_ROLES y DBA_ROLE_PRIVS).

3) El usuario tiene acceso a nivel de sistema para acceder a cualquier tabla, es decir, se le ha asignado el privilegio SELECT ANY TABLE (ver tabla DBA_SYS_PRIVS). También existen privilegios equivalentes para las acciones de CREATE, ALTER, BACKUP, DELETE, DROP, INSERT, LOCK y UPDATE.

Uso del comando TKPROF para formatear los ficheros de trazado PL/SQL

2007-06-20T18:18:00.003+02:00

Ya hablamos en un articulo anterior acerca de como activar y desactivar la funcionalidad de trazado SQL o PLSQL. En este artículo voy a escribir acerca de como utilizar el comando del sistema operativo TKPROF, cuyo cometido es formatear los ficheros binarios generados mientras la traza SQL esta activa, de manera que éstos sean legibles. La sintaxis del comando es como sigue:

UNIX> tkprof fichero_trazado fichero_salida [opciones]

Cuando el comando TKPROF es ejecutado sin utilizar ningún argumento, la salida del comando muestra un mensaje de ayuda junto con la descripción de todas las opciones del comando disponibles.

Opciones del comando TKPROF

Sort=opción - Ordena las sentencias SQL de la traza en base a distintos criterios. Los criterios de ordenación más útiles a mí entender son:

- prscpu: Ordenado según el tiempo de CPU.
- prsela: Ordenado según el tiempo de ejecución.

Print=n - El informe mostrará únicamente las n primeras sentencias SQL o PL/SQL. Esta opción es especialmente útil si se utiliza conjuntamente con las opción sort.

Explain=usuario/password - Se conecta a la base de datos utilizando dicho usuario y genera los planes de ejecución (EXPLAIN PLAN) de las sentencias SQL o PL/SQL en el esquema de la base de datos especificado.

Insert=nombre_fichero - Crea un script SQL para cargar los resultados del comando TKPROF dentro de una tabla de la base de datos Oracle.

Sys=no - El informe no muestra las sentencias SQL ejecutadas por el usuario SYS. Esta opción es bastante útil ya que elimina todas sentencias SQL internas. Estas sentencias, al ejecutarlas internamente el sistema de la base de datos Oracle, no pueden ser optimizadas y no es necesario, por lo general, tenerlas en consideración.

Aggregate=no - No acumula los resultados de las sentencias SQL idénticas en un solo registro.

Record=nombre_fichero - Crea un script SQL con todas las sentencias SQL no recursivas encontradas en el fichero de trazado. Este script se puede utilizar posteriormente para repetir la sesión de optimización o para modificar y optimizar las sentencias SQL sobre dicho script y comprobar que el rendimiento mejora al ejecutarlo tras la modificación.

Table=esquema.nombre_tabla - Especifica el esquema y el nombre de la tabla donde se almacenará temporalmente el plan de ejecución antes de que se genere el fichero de salida. Este parámetro es ignorado si no se utiliza la opción explain. Puede ser útil cuando simultáneamente varios usuarios utilizan el comando TKPROF para optimizar el mismo esquema de la base de datos.

Ya he hablado acerca de como ejecutar el comando TKPROF, ahora queda la ardua tarea de escribir acerca de como interpretar la salida de dicho comando, pero esto será objeto de otro artículo en esta bitácora.

Como avance mencionaré algunas circunstancias que nos pueden llevar a malinterpretar la salida del comando TKPROF:

Problemas con el número de lecturas consistentes: Si en el momento de la ejecución de una sentencia SQL o PLSQL hay transacciones no confirmadas (uncommitted transactions) sobre la tabla utilizada, entonces el número de bloques leídos aumentará ya que la base de datos Oracle necesitará construir y leer bloques adicionales para mantener la integridad.

Interpretación del tiempo de ejecución: Si una sentencia DML (sentencias de manipulación de datos) muestra un tiempo de ejecución elevado, puede ocurrir que esto sea debido a que otra transacción esté bloqueando la tabla involucrada. Por esta razón, el tiempo de CPU es un mejor indicador que el tiempo de ejecución.

Diferencias en el tiempo: Si el plan de ejecución de la sentencia SQL analizada indica que la tabla en cuestión es accedida a través de un índice, pero las estadísticas del comando TKPROF muestran un número de bloques leídos muy elevado y, además, los valores para la columna current del informe no son cero, entonces probablemente lo que haya ocurrido es que la tabla fue realmente accedida realizando un escaneado completo (full scan). Este tipo de situaciones se pueden dar cuando la generación del fichero de trazado haya sido anterior a la creación del índice o, si estamos utilizando el optimizador basado en costes, a la regeneración de las estadísticas asociadas con la tabla involucrada.

La existencia de triggers: Los valores mostrados por el comando TKPROF a la hora de analizar una sentencia SQL incluyen todas las sub-sentencias SQL ejecutadas desde la sentencia principal. Es decir, si una sentencia SQL dispara un trigger, los recursos utilizados por dicho trigger serán incluidos en el informe final acerca de la sentencia principal.