Programación PL/SQL y bases de datos Oracle

Trabajando con fechas en PL/SQL: los tipos DATE, TIMESTAMP e INTERVAL

2012-01-20T19:13:00.000+01:00

Las fechas son un tipo de datos del PL/SQL considerablemente más complejo que un tipo carácter o un tipo numérico. Una fecha o momento de tiempo está compuesto de múltiples campos (año, mes, día, hora, minutos, etcétera) y, además, existen un buen número de normas para determinar si una fecha es válida o no (los años bisiestos, los cambios de hora, etcétera). Como consecuencia de todo esto, en PLSQL resulta habitual tener que:

Declarar constantes y variables de tipo fecha o tiempo.
Utilizar funciones para modificar dichas variables y mostrarlas en el formato deseado por el usuario.
Manipular fechas y tiempos para realizar cálculos variados.

Este artículo será el primero de una serie en los que explicaré todo lo que un programador PL/SQL necesita conocer para trabajar con los diferentes tipos de datos asociados con fechas y momentos de tiempo (DATE, TIMESTAMP e INTERVAL).

Los tipos DATE, TIMESTAMP e INTERVAL

Afortunadamente la base de datos Oracle y el PLSQL proporciona diferentes tipos de datos que permiten manejar fechas y momentos de tiempo, almacenando ambos tipos de información en un formato interno estándar.

Las bases de datos Oracle permiten utilizar tres tipos de datos diferentes para trabajar con fechas y momentos de tiempo:

DATE: este tipo de dato permite almacenar una fecha y un tiempo hasta el nivel de segundos. No incluye información sobre la zona horaria. Es el tipo de dato que más se utiliza para trabajar con fechas dentro de cualquier aplicación Oracle.
TIMESTAMP: se trata de un tipo de dato similar al DATE pero con dos diferencias clave, permiten almacenar y manipular momentos de tiempo hasta la mil millonésima de segundo (con una precisión de 9 decimales), y también es posible asociarle una zona horaria de tal manera que la base de datos Oracle tendrá en cuenta dicha zona horaria cuando manipulemos y realicemos cálculos utilizando este tipo de dato.
INTERVAL: mientras que los tipos DATE y TIMESTAMP indican un momento específico de tiempo, INTERVAL almacena y permite trabajar con duraciones de tiempo, siendo posible definir intervalos de tiempo en términos de años y meses, o de días y segundos.

A continuación os dejo un ejemplo de código PL/SQL donde se declaran diferentes variables que utilizan los tipos de dato DATE, TIMESTAMP e INTERVAL.

DECLARE
   l_hoy_date       DATE := SYSDATE;
   l_hoy_timestamp  TIMESTAMP := SYSTIMESTAMP;
   l_hoy_timetzone  TIMESTAMP WITH TIME ZONE
                    := SYSTIMESTAMP;
   l_interval_ym    INTERVAL YEAR (4) TO MONTH 
                    := '2011-11';
   l_interval_ds    INTERVAL DAY (2) TO SECOND 
                    := '15 00:30:44';
BEGIN
   null;
END;

Como comentario os diré que resulta poco usual que un programador de PLSQL tenga que utilizar los tipos TIMESTAMP e INTERVAL con zona horaria, algo que, por cierto, resulta algo complicado y se necesitan conocer algunas funcionalidades avanzadas.

¿Cómo escoger el tipo de dato fecha adecuado?

Ante la diversidad de tipos de dato fecha y momentos de tiempo que ofrece la base de datos Oracle, al escribir nuestro código PL/SQL puede resultarnos complicado decidirnos por un tipo de dato u otro. Estas son las normas que yo utilizo para decantarme por uno u otro:

Utilizar el tipo TIMESTAMP cuando es necesario controlar momentos de tiempo por debajo de la fracción de segundo.
En general es posible utilizar el tipo TIMESTAMP en lugar del tipo DATE, ya que la base de datos es capaz de distinguir cuando un TIMESTAMP no almacena fracciones de segundo, reservando sólo 7 bytes de almacenamiento para dicho dato, exactamente lo mismo que para un dato DATE. Cuando un TIMESTAMP contiene fracciones de segundo, entonces la base de datos Oracle necesita 11 bytes de almacenamiento.
Utilizar TIMESTAMP WITH TIME ZONE cuando sea necesario realizar un seguimiento de la zona horaria de la sesión en la que el dato fue introducido.
Utilizar TIMESTAMP WITH LOCAL TIME ZONE cuando la base de datos Oracle tenga que convertir automáticamente tiempos entre bases de datos y sesiones que operan bajo diferentes zonas horarias.
Utilizar DATE cuando sea necesario mantener la compatibilidad con una aplicación que fue escrita antes de que el tipo de dato TIMESTAMP fuera introducido.
En nuestro código PL/SQL siempre deberemos utilizar tipos de datos que se correspondan, o que al menos sean compatibles, con el tipo de dato asociado con el campo de la tabla que queramos almacenar en la variable correspondiente. Debemos ser conscientes de que si el campo de una tabla es tipo TIMESTAMP y lo almacenamos en una variable tipo DATE, podremos estar perdiendo información (en este caso la relativa a la zona horaria o a las fracciones de segundo).

En futuros artículos hablaré sobre las funciones que permiten obtener los valores de la fecha y el tiempo actual, las funciones para convertir fechas a caracteres y caracteres a fechas, y las funciones de truncado de fechas.

Funciones númericas en PL/SQL y el SQL de Oracle

2011-11-29T23:30:00.000+01:00

Las bases de datos Oracle ofrecen un extenso conjunto de funciones estándar SQL y PL/SQL para manipular números y realizar conversiones entre números y cadenas de caracteres. En este artículo hablaremos sobre las funciones numéricas más comunes y que se tienen que utilizar con mayor frecuencia a la hora de programar en PL/SQL, siendo su conocimiento fundamental para cualquier programador de bases de datos Oracle.

Las funciones numéricas estándar más comunes del PLSQL y el SQL de Oracle son:

ROUND (m, n): la función ROUND acepta como entrada un número y devuelve como salida otro número redondeado a un número específico de decimales (que se indica en el segundo parámetro de la función). Si no se especifica el número de decimales a mostrar (parámetro n), la función ROUND devolverá el número redondeado al entero más próximo. Si el número de decimales a redondear es negativo (n es negativo), entonces el redondeo se realiza avanzando hacia la izquierda del separador decimal, en lugar de hacia la derecha (ver ejemplo).

BEGIN
  DBMS_OUTPUT.put_line (ROUND (20.35));
  DBMS_OUTPUT.put_line (ROUND (20.35, 1));
  DBMS_OUTPUT.put_line (ROUND (20.33, 1));
  DBMS_OUTPUT.put_line (ROUND (20.35, 2));
  DBMS_OUTPUT.put_line (ROUND (20.35, -2));
  DBMS_OUTPUT.put_line (ROUND (225, -2));
END;

La salida de este bloque PL/SQL sería:
20
20.4
20.3
20.35
0
200

TRUNC (m, n): la función TRUNC es muy parecida a la función ROUND, permitiendo también indicar el número de decimales a truncar a derecha (n positivo) o izquierda (n negativo) del separador decimal. La diferencia estriba en que la función TRUNC simplemente elimina o trunca los dígitos, es decir, no realiza un redondeo.

BEGIN
  DBMS_OUTPUT.put_line (TRUNC (20.35));
  DBMS_OUTPUT.put_line (TRUNC (20.35, 1));
  DBMS_OUTPUT.put_line (TRUNC (20.33, 1));
  DBMS_OUTPUT.put_line (TRUNC (20.35, 2));
  DBMS_OUTPUT.put_line (TRUNC (20.35, -2));
  DBMS_OUTPUT.put_line (TRUNC (225, -2));
END;
/

La salida de este bloque PL/SQL sería:
20
20.3
20.3
20.35
0
200

FLOOR (m): esta función devuelve el mayor entero menor o igual que el número especificado como parámetro.

BEGIN
  DBMS_OUTPUT.put_line (FLOOR (3.5));
END;
/

La salida de este bloque PL/SQL sería:
3

CEIL (m): esta función devuelve el menor entero mayor o igual que el número especificado como parámetro.

BEGIN
  DBMS_OUTPUT.put_line (CEIL (3.5));
END;
/

La salida de este bloque PL/SQL sería:
4

MOD (m, n): calcula el resto del resultado de dividir m entre n. La fórmula que emplea la base de datos Oracle para realizar el cálculo es la siguiente MOD (m, n) = m - n * FLOOR (m/n).

BEGIN
  DBMS_OUTPUT.put_line (MOD (25, 6));
  DBMS_OUTPUT.put_line (MOD (25, 9));
END;
/

La salida de este bloque PL/SQL sería:
1
7

REMAINDER (m, n): calcula el resto por exceso del resultado de dividir m entre n. La fórmula que emplea la base de datos Oracle para realizar el cálculo es la siguiente REMAINDER (m, n) = m - n * ROUND (m/n).

BEGIN
  DBMS_OUTPUT.put_line (REMAINDER (25, 6));
  DBMS_OUTPUT.put_line (REMAINDER (25, 9));
END;
/

La salida de este bloque PL/SQL sería:
1
-2

TO _CHAR (m): la función PL/SQL TO_CHAR, en su formato más sencillo, sirve para convertir un número en una cadena de caracteres. Se pasa como único parámetro un número, devolviendo la función la cadena de caracteres que representa a dicho número mostrando únicamente los dígitos significativos de dicho número (es decir, los ceros que no son significativos son eliminados).

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (200.64));
  DBMS_OUTPUT.put_line (TO_CHAR (000200.6400));
  DBMS_OUTPUT.put_line (TO_CHAR (20000.00));
END;

La salida de este bloque PL/SQL sería:
200.64
200.64
20000

Función TO_CHAR (m, formato)

Con cierta frecuencia, los programadores PL/SQL se encuentran con la necesidad de que al convertir un número en una cadena de caracteres, dicho número cumpla con cierto formato. Por ejemplo, puede ser necesario mostrar el número de manera que incluya siempre un número fijo de decimales (aunque estos sean cero), o puede necesitarse mostrar el separador de miles. Para estos casos la función TO_CHAR debe incluir un segundo parámetro que representa la máscara del formato que debe seguir el número a representar.

Esta máscara se representa mediante una cadena de caracteres y lo más sencillo para comprender su funcionamiento es utilizar una serie de ejemplos.

En el primer ejemplo vemos que el carácter "G" indica en qué lugar de la cadena se quiere colocar el separador de grupo (en nuestro caso separador de miles). El carácter separador de grupo viene determinado por el valor del parámetro NLS_NUMERIC_CHARACTERS de la configuración del lenguaje de la base de datos Oracle, es decir, puede modificarse según las necesidades específicas de cada país (por ejemplo, en España se utiliza el punto "." mientras que en UK o USA se utiliza la coma ","). El carácter "9" le indica a la base de datos Oracle que en esa posición debe colocar un dígito significativo o, si el dígito no es significativo, un espacio en blanco.

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (20000, '9G999G999'));
END;
/
La salida de este bloque PL/SQL sería:
   20,000

El este otro ejemplo podéis ver que si queremos que aparezcan ceros en lugar de espacios en blanco para los dígitos no significativos, entonces deberemos utilizar el carácter "0" en lugar del "9".

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (20000, '0G000G999'));
END;
/
La salida de este bloque PL/SQL sería:
0,020,000

Pero si lo que queremos es que no aparezcan ni ceros, ni espacios en blanco al convertir nuestro número en una cadena de caracteres, tendremos que usar la cadena "FM".

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (20000, 'FM9G999G999'));
END;
/
La salida de este bloque PL/SQL sería:
20,000

Ahora supongamos que nuestro número debe representar una cantidad en euros incluyendo los céntimos de euro y, además, queremos mostrar el símbolo del euro. Entonces deberemos usar la siguiente máscara:

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (20000.57, 'FM999G999D99L'));
END;
/
La salida de este bloque PL/SQL sería:
20000.57€

En el ejemplo vemos que el carácter "L" especifica el lugar donde aparecerá el símbolo de la moneda local (dicho símbolo viene determinado por el parámetro de la base de datos Oracle NLS_CURRENCY). El carácter "D" determina la posición del separador decimal (al igual que el separador de grupo, el carácter que se utiliza como separador decimal, en España la coma "," y en UK o USA el punto ".", viene determinado por el parámetro NLS_NUMERIC_CHARACTERS).

Para finalizar, sólo comentaros que si estáis interesado en ver todos los caracteres y elementos que se pueden usar en PLSQL y el SQL de Oracle en la máscara de los formatos numéricos, podéis chequear la siguiente página de la web oficial de Oracle.

Uso de Rollback Segments por sentencias SELECT

2011-10-20T12:46:00.000+02:00

En alguna ocasión algún lector me ha preguntado, con cierta sorpresa, acerca del por qué una sentencia SELECT le fallaba con el mensaje de error "No es posible ampliar el segmento de rollback" ("Unable to extend rollback segment"). La sorpresa proviene del hecho de que son muchos los desarrolladores PL/SQL los que piensan que los segmentos de rollback sólo se utilizan cuando se emplean sentencias PLSQL en las que se modifican o actualizan datos dentro de la base de datos Oracle. Bajo este tipo de pensamiento es normal que, cuando se produce el error mencionado anteriormente al ejecutar una sentencia SELECT, uno se pregunte: ¿utiliza la base de datos Oracle segmentos de rollback al ejecutar sentencias SELECT?

Bueno, en mi opinión, lo primero que hay que hacer es reformular la pregunta y cambiarla por la siguiente: ¿una sentencia SELECT necesita crear o leer segmentos de rollback? La pregunta formulada de esta manera seguro que nos ayudará a comprender mejor este artículo, ya que el verbo "utilizar" usado en la primera pregunta no es lo suficientemente específico.

En PL/SQL, todas las sentencias SELECT tienen el potencial de utilizar los datos de rollback o undo (o datos de vuelta atrás). La base de datos Oracle a la hora de procesar cualquier tipo de consulta utiliza los segmentos de rollback para producir conjuntos de resultados de lectura consistente (la lectura consistente es una característica de las bases de datos Oracle que asegura que todos los registros dentro de un conjunto de resultados, cuando son presentados a una aplicación PLSQL o a cualquier otro tipo de aplicación, provengan de un mismo instante de tiempo).

No obstante, este uso de los segmentos de rollback no va a causar nunca por sí solo un error como el antes mencionado (ORA-01650 Unable to extend rollback segment... u ORA-01651 Unable to extend undo segment... ). Sin embargo, sí que puede provocar un error del tipo "ORA-01555 Snapshot too old".

Para que una sentencia SELECT pueda generar los errores ORA-01650 u ORA-01651, es necesario que esté generando segmentos de rollback y los motivos pueden ser los siguientes:

La sentencia SELECT contiene la cláusula FOR UPDATE.
La funcionalidad de auditoría está habilitada.
La sentencia SELECT invoca a algún tipo de transacción que escribe en la base de datos Oracle.

Lo más corriente es que la causa sea que la sentencia SELECT contenga una cláusula FOR UPDATE, cuando esto ocurre la base de datos Oracle bloquea todos los registros necesarios antes de que la sentencia SELECT empiece a devolver resultados, y bloquear un registro en la base de datos Oracle implica modificar un bloque de la base de datos para registrar dicho bloqueo. Por otro lado, cada vez que se modifica un bloque de la base de datos, se necesita generar un undo para esa operación.

Demostrar este hecho es muy sencillo. Creemos una sesión SQL y ejecutemos los siguientes comandos sin que haya ningún otro usuario utilizando la base de datos:

SQL> SELECT used_ublk FROM v$transaction

No rows selected

SQL> BEGIN
  2    FOR cursor IN (
  3      SELECT * FROM nombre_tabla
  4        FOR UPDATE)
  5    LOOP null;
  6    END LOOP;
  7  END;
  10 /

PL/SQL procedure successfully completed.

SQL> SELECT used_ublk FROM v$transaction

USED_UBLK
---------
      932

SQL> COMMIT;

Según queríamos confirmar, podemos ver que la ejecución de la sentencia SELECT FOR UPDATE ha generado 932 bloques de undo (tened en cuenta que nombre_tabla debe ser una tabla que exista en vuestra base de datos Oracle).

Cláusula BULK COLLECT para mejorar el rendimiento al realizar procesamiento masivo

2011-09-15T06:30:00.000+02:00

Yo siempre he dicho que cuando para hacer algo se pueden utilizar sentencias SQL sencillas, no resulta conveniente emplear complicados procedimientos PL/SQL que implementen la misma solución. Sin embargo, hay situaciones en que para mejorar el rendimiento de determinados bucles FOR en los que se realizan actualizaciones masivas sobre una determinada tabla de la base de datos Oracle, resulta conveniente utilizar técnicas PLSQL de procesamiento masivo (lo que en inglés se denomina BULK COLLECT).

Para entender mejor en qué consiste esta técnica, primero hay que comprender los motivos por los que un simple bucle FOR puede generar importantes problemas de rendimiento. Veamos el siguiente código PL/SQL:

FOR selrec IN 
  (SELECT * FROM tabla_enorme 
  ORDER BY muchas columnas)
LOOP
  -- Gran cantidad de código que omito y al final:
  UPDATE tabla_enorme SET ...
    WHERE clave_primaria = selrec.clave_primaria;
  COMMIT;
END LOOP;

El presente código es un extracto de un código PLSQL que uno de los lectores de este blog me envió por correo electrónico indicándome que presentaba graves problemas de rendimiento, cosa que desde un primer momento a mi no me extrañó, considerando que la tabla tabla_enorme contenía más de 30 millones de registros. Es el típico ejemplo de código PL/SQL que para mejorar su rendimiento necesita que se aplique la técnica o funcionalidad de BULK COLLECT (que traducido directamente a castellano sería similar a decir "recogida a granel" pero que aquí traduciremos por procesamiento masivo).

Antes de profundizar en el tema, debemos considerar que si leemos los datos de una base de datos Oracle sin necesidad de enviar datos de vuelta a la misma base de datos, no es necesario aplicar la técnica del BULK COLLECT. Es decir, el siguiente código PL/SQL es perfectamente utilizable y no debería generar problemas de rendimiento:

FOR x IN 
  (SELECT * FROM tabla_enorme t WHERE ...)
LOOP
  DBMS_OUTPUT.PUT_LINE (x.col1||...||x.colN);
END LOOP;

En el ejemplo vemos que estamos leyendo los datos utilizando un SELECT, pero que, aun tratándose de una operación registro a registro, no se utilizan para ser retornados a la base de datos utilizando un INSERT, UPDATE o DELETE. La sentencia SQL SELECT ya utiliza, de por sí, la funcionalidad de procesamiento masivo, ya que, desde la versión 10g de la base de datos Oracle, el código "FOR x IN (SELECT ...)", aunque no sea visible para el desarrollador, ha sido optimizado para almacenar internamente en matrices de 100 registros los resultados que devuelve dicho SELECT.

Sin embargo, veamos que ocurre con este otro ejemplo de código PL/SQL:

FOR x IN 
  (SELECT clave_primaria, col1, col2 
  FROM tabla_enorme)
LOOP
  x.col1 := calculos(x.col1, x.col2);
  UPDATE tabla_enorme
    SET col1 = x.col1
    WHERE clave_primaria = x.clave_primaria;
  COMMIT;
END LOOP;

En este código PLSQL, la sentencia SELECT estará utilizando la funcionalidad de procesamiento masivo incorporada con la versión 10g, pero no ocurrirá lo mismo con el procesamiento de la sentencia UPDATE. La pregunta ahora es, ¿cómo podemos mejorar el rendimiento de un código tan sencillo? El primer cambio que realizaremos es algo bastante sencillo:

FOR x IN
  (SELECT rowid, col1, col2 
  FROM tabla_enorme)
LOOP
  x.col1 := calculos(x.col1, x.col2);
  UPDATE tabla_enorme
    SET col1 = x.col1
    WHERE rowid = x.rowid;
END LOOP;

El nuevo código lee cada registro de la tabla_enorme, realiza algún tipo de procesado de los datos y, finalmente, realiza el UPDATE de un campo de la misma tabla por ROWID. ¿Qué hemos conseguido con respecto al primer código?, habremos evitado realizar 30 millones de veces un UNIQUE SCAN sobre el índice de la clave primaria, ya que estaremos accediendo a la tabla por ROWID. Acceder a la tabla a través del índice de la clave primaria puede implicar de tres a cinco operaciones de entrada/salida (I/O) por iteración, por lo que acceder a la tabla por ROWID en una tabla tan grande nos puede ahorrar más de cien millones de operaciones de entrada/salida.

Otro cambio que observaréis es que hemos eliminado el COMMIT después de cada iteración. Aparte de que realizar un COMMIT después de que cada registro es procesado resultará lento y reducirá bastante el rendimiento, si en mitad del procesamiento se produce algún error, habremos dejado la base de datos Oracle en un estado bastante inconsistente, por no decir corrupto (con media tabla_enorme actualizada y la otra sin actualizar).

Pero esto que hemos hecho no tiene en realidad nada que ver con la técnica o funcionalidad de procesamiento masivo. Veamos el siguiente código PL/SQL en el que hemos, por fin, utilizado un FETCH con la cláusula BULK COLLECT:

CREATE OR REPLACE PROCEDURE procesamiento_masivo
AS
  TYPE matriz_rowid IS TABLE OF ROWID;
  TYPE matriz_col1 IS TABLE OF tabla_enorme.col1%TYPE;
  TYPE matriz_col2 IS TABLE OF tabla_enorme.col2%TYPE;

  CURSOR cur IS SELECT rowid, col1, col2
    FROM tabla_enorme;
  m_rowid matriz_rowid;
  m_col1 matriz_col1;
  m_col2 matriz_col2;
  contador NUMBER := 100;

BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT
      INTO m_rowid, m_col1, m_col2 LIMIT contador;
    FOR i IN 1 .. m_rowid.count
    LOOP
      m_col1(i) := calculos(m_col1(i), m_col2(i));
    END LOOP;
    FORALL i IN 1 .. m_rowid.count
      UPDATE tabla_enorme
        SET col1 = m_col1(i)
        WHERE rowid = m_rowid(i);
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;

El nuevo código utiliza si ningún tipo de restricción la funcionalidad de procesamiento masivo BULK COLLECT, de manera que los registros se procesan de cien en cien (valor que podremos cambiar con sólo asignar un valor diferente a la variable contador). Y, una vez procesados, lo que hacemos es un UPDATE masivo (bulk update) utilizando la sentencia PL/SQL FORALL.

Es fácil observar que el código PLSQL que utiliza la funcionalidad BULK COLLECT es bastante más complicado y mucho menos intuitivo que el original, pero los resultados a nivel de rendimiento van a ser realmente sorprendentes. Para tablas con 30 millones de registros, utilizar la funcionalidad de procesamiento masivo puede hacer que nuestro código se ejecute entre diez y veinte veces más rápido que sin utilizar dicha funcionalidad (ojo que, a veces, la mejora de rendimiento puede ser incluso hasta superior).

PLSQL dinámico con las funciones DBM_SESSION.SET_CONTEXT y SYS_CONTEXT (¿Por qué?)

2011-08-10T06:08:00.005+02:00

En programación PL/SQL siempre tenemos lectores que nos hacen preguntas interesantes, en esta ocasión se nos ha preguntado acerca del motivo por el cual al utilizar PLSQL dinámico hay mucha gente que utiliza las funciones estándar de Oracle SYS_CONTEXT y DBM_SESSION.SET_CONTEXT, de manera que en la cláusula WHERE de cualquier consulta SQL, en lugar de utilizar simplemente literales se utiliza la función SYS_CONTEXT.

Es decir, por qué utilizar "WHERE valor = SYS_CONTEXT('mi_contexto','valor')", en vez de, por ejemplo, la simple y más corta sentencia "WHERE valor = 15".

Desde luego mi opinión está claramente a favor de utilizar siempre la función SYS_CONTEXT en lugar de literales, las razones son variadas y están relacionadas con el hecho de que utilizar literales es malo para:

El rendimiento de la base de datos Oracle.
La utilización de la shared pool.
La escalabilidad de la aplicación.
La seguridad (al favorecer algunos métodos de infiltración mediante inyección SQL).

A continuación voy a explicar un poco más en detalle sobre cómo cada uno de los aspectos anteriormente mencionados se pueden ver afectados por el uso o no uso de las funciones estándar de Oracle DBM_SESSION.SET_CONTEXT y SYS_CONTEXT al utilizar PL/SQL dinámico. Para ir avanzando os diré que el problema, en lo que respecta al rendimiento, radica en que utilizar literales implica que las tasas de hard parsing se incrementan significativamente (para saber que es el parsing podéis leer el artículo fases del procesamiento de una sentencia SQL), ya que diferentes valores de estos literales implican diferentes consultas SQL que la base de datos Oracle debe "parsear" desde cero al no encontrarse almacenadas en la shared pool.

No debe sorprenderos saber que para pequeñas sentencias PLSQL, casi un 95% del tiempo de ejecución se corresponde con el tiempo de parsing y no con el tiempo real de ejecución. Por este motivo, reducir el tiempo de parsing es fundamental a la hora de mejorar el rendimiento de nuestra base de datos Oracle. Tampoco debe extrañaros que una de las principales causas de la existencia de problemas de rendimiento en las bases de datos Oracle, sea tener una alta tasa de hard parsing al utilizar SQL dinámico no reutilizable.

Lo mejor para comprender todo este entramado que estoy contando es utilizar un ejemplo. Veamos el siguiente código:

CREATE OR REPLACE FUNCTION
apps.query_dinamica (p_owner dba_tables.owner%TYPE)
RETURN SYS_REFCURSOR
IS
  c_query SYS_REFCURSOR;
  v_query VARCHAR2(1000);
BEGIN
  v_query := 'SELECT table_name
              FROM   dba_tables
              WHERE  1 = 1';
  IF p_owner IS NOT NULL THEN
  -- p_owner es VARCHAR2 por eso hay que utilizar '''
     v_query := v_query ||
                ' AND owner = '''||p_owner||'''';
  END IF;

  OPEN c_query FOR v_query;

  RETURN (c_query);
  
END;

En este ejemplo de PL/SQL dinámico hemos utilizado una forma de codificar que, como veremos a continuación, nos garantiza que la base de datos Oracle tenga que hacer hard parsing (sin posibilidad de acudir a la shared pool) cada vez que utilizamos un valor del parámetro p_owner diferente. Veamos por qué.

SQL> variable x refcursor;
SQL> EXECUTE :x := query_dinamica ('ASP');

PL/SQL procedure successfully completed.

SQL> print x

TABLE_NAME
------------------------------
ASP_ALERT_SUBSCRIPTIONS
ASP_PROGRAM_RUN_DATES

SQL>  EXECUTE :x := query_dinamica ('MWA');

PL/SQL procedure successfully completed.

SQL> print x

TABLE_NAME
------------------------------
MWA_CLASS_CUSTOM_FILES

SQL> SELECT sql_text, executions 
  2  FROM   v$sql
  3  WHERE  parsing_schema_name = 'APPS'
  4  AND    sql_text like '%table_name%dba_tables%1 = 1%';

SQL_TEXT             EXECUTIONS
-------------------------------
SELECT table_name
FROM   dba_tables
WHERE  1 = 1AND
owner = 'ASP'        1

SELECT table_name
FROM   dba_tables
WHERE  1 = 1AND
owner = 'MWA'        1

Como podéis ver, cada llamada a la función query_dinamica genera sentencias totalmente independientes que deben pasar por la fase de parsing de forma separada.

La solución para evitar esta circunstancia pasa por utilizar la librería estándar Oracle CONTEXTS, que si bien apareció para soportar VPD (Virtual Private Database), también puede emplearse para el caso que estamos analizando. Con la aplicación CONTEXTS es posible definir atributos y asignarles valores que residirán en el UGA (User Global Area) durante la vida de la sesión, son valores de carácter privado y, por lo tanto, cada sesión utiliza los suyos propios y no pueden ser accedidos desde otras sesiones.

Comprenderéis un poco mejor el funcionamiento de esta librería cuando vayamos resolviendo el problema que nos hemos planteado. Primero deberemos crear el contexto.

SQL> CREATE CONTEXT query_dinamica_ctx 
  2  USING apps.query_dinamica;

Context created.

Al crear el contexto query_dinamica_ctx hemos utilizado la cláusula USING para indicar a la base de datos Oracle que dicho contexto sólo puede ser utilizado desde nuestra función PLSQL apps.query_dinamica. Esto quiere decir que la creación de atributos y la asignación de valores sólo se puede hacer dentro de la función apps.query_dinamica, lo cual añade un nivel de seguridad adicional, ya que garantiza que los usuarios no van a poder crear atributos ni modificar sus valores desde, por ejemplo, SQL*Plus. Veamos a continuación un ejemplo para que veáis claramente a que me refiero.

SQL> EXECUTE DBMS_SESSION.SET_CONTEXT
  2  ('query_dinamica_ctx','owner_ctx','ASP');

BEGIN DBMS_SESSION.SET_CONTEXT
('query_dinamica_ctx','owner_ctx','ASP'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 101
ORA-06512: at line 1

A continuación veremos cómo quedaría nuestra función PLSQL apps.query_dinamica utilizando las funciones estándar Oracle DBM_SESSION.SET_CONTEXT y SYS_CONTEXT.

CREATE OR REPLACE FUNCTION 
apps.query_dinamica (p_owner dba_tables.owner%TYPE)
RETURN SYS_REFCURSOR
IS
  c_query SYS_REFCURSOR;
  v_query VARCHAR2(1000);
BEGIN
  v_query := 'SELECT table_name
              FROM   dba_tables
              WHERE  1 = 1';
  IF p_owner IS NOT NULL THEN
     v_query := v_query ||
                ' AND owner = 
                  SYS_CONTEXT(''query_dinamica_ctx''
                             ,''owner_ctx'')';
     DBMS_SESSION.SET_CONTEXT
       ('query_dinamica_ctx','owner_ctx',p_owner);
  END IF;

  OPEN c_query FOR v_query;

  RETURN (c_query);
  
END;

Con la función PL/SQL DBMS_SESSION.SET_CONTEXT lo que hacemos es asignar el valor del parámetro p_owner al atributo owner_ctx, y con la función SYS_CONTEXT leemos desde el UGA el valor asignado a dicho atributo. Veamos a continuación como los cambios que hemos realizado afectan positivamente al rendimiento de nuestra función PLSQL.

SQL> execute :x := query_dinamica ('ASP');

PL/SQL procedure successfully completed.

SQL> print x

TABLE_NAME
------------------------------
ASP_ALERT_SUBSCRIPTIONS
ASP_PROGRAM_RUN_DATES

SQL> execute :x := query_dinamica ('MWA');

PL/SQL procedure successfully completed.

SQL> print x

TABLE_NAME
------------------------------
MWA_CLASS_CUSTOM_FILES

SQL> SELECT sql_text, executions 
  2  FROM   v$sql
  3  WHERE  parsing_schema_name = 'APPS'
  4  AND    sql_text like '%table_name%dba_tables%SYS_CON%';

SQL_TEXT                              EXECUTIONS
------------------------------------------------
SELECT table_name
FROM   dba_tables
WHERE  1 = 1
AND owner = SYS_CONTEXT
('query_dinamica_ctx','owner_ctx')    2

Hemos conseguido que la sentencia SQL que hemos ejecutado en un par de ocasiones, a pesar de utilizar parámetros con valores diferentes, sea exactamente la misma. De esta manera el parsing se hará durante la primera ejecución, mientras que para la segunda, el gestor de la base de datos Oracle acudirá a la shared pool evitándose el tiempo de parsing. Hemos dicho adiós al hard parsing y generado código PL/SQL reutilizable.

Pero no sólo la utilización de las funciones CONTEXT es importante a la hora de mejorar el rendimiento de una base de datos Oracle, sino que también resulta imperativo para conseguir escalabilidad multiusuario. Al realizar hard parsing es necesario bloquear o serializar las estructuras de datos en la SGA (System Global Area), lo que constituye una de las principales limitaciones a la escalabilidad de los sistemas actuales.

Y si nos fijamos en la seguridad de los sistemas, al utilizar funciones CONTEXT estaremos evitando problemas indeseados de inyección SQL, porque podremos evitar que los parámetros de entrada a nuestros procedimientos PL/SQL sean parte de la sentencia SQL. Usando funciones CONTEXT podemos conseguir que sólo valores contextuales, valores que podremos controlar, sean valores de entrada a dichos procedimientos (ojo, no es el caso de nuestro ejemplo).

Nota: en los scripts SQL también se pueden utilizar las llamadas bind variables para conseguir el mismo efecto que utilizando funciones CONTEXT (las variables bind son las que en el código SQL se representan con dos punto [":"] al principio de la misma).

Tuning y constraints (o restricciones en la base de datos Oracle)

2011-07-06T14:14:00.002+02:00

En este artículo continuaré con el caso de tuning PL/SQL planteado en el artículo Tuning de consultas SELECT COUNT(*) y determinaré cómo es posible mejorar aún más el rendimiento de la consulta SELECT objeto del mencionado artículo. Eso sí, para poder profundizar en el estudio del rendimiento, tuve que solicitar al lector que me hizo la pregunta inicial que me enviase los datos del esquema de la base de datos Oracle para las tablas involucradas en la consulta PLSQL SELECT. Después de un par de correos pude disponer de toda la información que necesitaba, los campos de las tablas, los índices asociados, las claves primarias (primary keys), las claves extranjeras (foreign keys), y las diferentes restricciones (constraints) aplicadas sobre las mencionadas tablas.

En suma, disponía de la siguiente información incluida en las siguientes sentencias SQL:

SELECT COUNT(*)
INTO   v_count
FROM   tabla1 t1, tabla2 t2
WHERE  t1.id = t2.id
  AND  t2.otro_id = v_otro_id;

CREATE TABLE tabla1 (
  id       NUMBER(10) NOT NULL,
  ...
  datos    VARCHAR2(250)
);

ALTER TABLE tabla1 ADD CONSTRAINT t1_pk
PRIMARY KEY (id);

CREATE TABLE tabla2 (
  otro_id  NUMBER(10) NOT NULL,
  id       NUMBER(10) NOT NULL,
  ...
  datos  VARCHAR2(250)
);

ALTER TABLE tabla2 ADD CONSTRAINT t2_pk
PRIMARY KEY (otro_id);

ALTER TABLE tabla2 ADD CONSTRAINT t2_fk1
FOREIGN KEY (id) REFERENCES t1 (id);

Lo primero que me llamó la atención a ver esta información fue que la columna ID era la clave primaria de la TABLA1 y que, a la vez, era la clave extranjera (foreign key) de la TABLA2 a la TABLA1; además, TABLA2.id estaba definido como un campo no nulo (NOT NULL). Esto me daba una clara idea para poder reescribir la consulta SELECT de manera que mejorase su rendimiento, todo ello gracias a que además sabía lo siguiente:

La salida del SELECT no proporcionaba ningún campo de la TABLA1.
Al relacionar las tablas TABLA1 y TABLA2 por el campo ID, los registros en la TABLA2 son de clave preservada, ya que TABLA1.id es un valor único y al unir dicha tabla con la TABLA2, como mucho la consulta SELECT devolverá los registros contenidos en esta última tabla una única vez.
Pero además, como TABLA2.id es una clave extranjera de TABLA2 a TABLA1, todos los registros de la TABLA2 que tienen un valor no nulo del campo ID, aparecerán al menos una vez en la salida de la consulta SELECT.
Por último, como TABLA2.id es un campo no nulo (NOT NULL), todos los registros de la TABLA2 aparecerán a menos una vez y como mucho una vez en dicha salida.

Por los tanto, la consulta SELECT antes mencionada es equivalente a la siguiente consulta mucho más sencilla:

SELECT COUNT(*)
INTO   v_count
FROM   tabla2 t2
WHERE  t2.otro_id = v_otro_id;

Hemos podido por tanto eliminar una de las tablas de la consulta SELECT, con lo cual accederemos a un solo objeto de la base de datos Oracle, la TABLA2. El plan de ejecución de la sentencia se ha simplificado notablemente ya que, entre otras cosas, no será necesario utilizar en índice de la clave primaria T1_PK.

Lo que seguro que muchos os estaréis preguntando ahora es: "Si nosotros, tras realizar un sencillo análisis, hemos podido eliminar la TABLA1 de la consulta SELECT, ¿no podría hacer lo mismo el optimizador de la base de datos Oracle?" La respuesta es que sí, pero esto sólo ocurre para las versiones de la base de datos Oracle 11g Release 1 y posteriores, que además tengan habilitados los constraints a nivel de la base de datos. Si la base de datos Oracle no utiliza constraints, entonces los constraints que aparecen en las definiciones de la tablas sólo se aplican a nivel de la aplicación, pero no de la base de datos. Es decir, es la aplicación la que fuerza a que se cumplan esos constraints y no la base de datos misma.

En conclusión, para realizar el tuning o puesta a punto de una sentencia PL/SQL es necesario conocer el modelo de datos y la estructura de la base de datos (al menos de las tablas y objetos involucrados). Si en el anterior ejemplo yo no hubiera conocido las claves primarias, las claves extranjeras, ni los campos NOT NULL, no hubiera sido capaz de poner a punto la consulta SELECT de una manera eficaz. Por otro lado, es imprescindible que el optimizador conozca los constraints, si decidimos que la base de datos no use constraints, estaremos limitando enormemente la capacidad del optimizador que será incapaz de optimizar eficazmente cualquier consulta, en nuestro caso habría sido incapaz de eliminar el uso de la TABLA1, pero incluso puede ser incapaz de utilizar algún que otro índice, ya que los constraints NOT NULL pueden tener un gran impacto sobre el optimizador a la hora de saber si puede o no utilizar un índice determinado.

En mi opinión es mucho mejor que sea el optimizador el que elabore el plan de ejecución correcto, y no ser nosotros los que tengamos que pensar si un plan es equivalente a otro. Por lo tanto, los metadatos o constraints no sólo son imprescindibles para asegurar la integridad de los datos, sino también para que el optimizador pueda elegir el mejor plan de ejecución.

Artículos relacionados: Bloqueo de tablas hijo al ejecutar sentencia PL/SQL sobre tablas padre (sobre el uso de claves extranjeras o foreign keys).

La competencia de Oracle Business Suite: Open Apps de Velneo V7

2011-06-03T13:20:00.003+02:00

Hace unos días me propusieron hacer una revisión y opinar sobre Velneo V7, una plataforma completa con base de datos integrada de desarrollo de aplicaciones empresariales que además incorpora plantillas de código abierto y editable FLOSS para desarrollar aplicaciones del tipo ERP (Enterprise Resource Planning) o CRM (Customer Relationship Management). Por lo tanto Velneo es, de alguna manera, competencia de Oracle Business Suite, aunque ni por las dimensiones de una y otra empresa, ni por lo que ofrecen una aplicación y otra, esa competencia sea real.

Las aplicaciones de gestion desarrolladas con Velneo V7 pueden ejecutarse en múltiples plataformas (Windows, Linux, Mac o MeeGo), soportando arquitectura Cliente/Servidor con escritorios monopuesto y multipuesto, SaaS, HTML Web, Web RIA y terminales móviles.

Velneo V7 es una plataforma completa que no necesita componentes de terceros ni DLLs, con un multiservidor que distribuye aplicaciones, datos (base de datos), web, disco y ediciones. Todos los componentes (componentes de edición, de servicio y administración, y de ejecución) se conectan al servidor para poder realizar todas las fases del ciclo de desarrollo y despliegue.

Las aplicaciones se pueden desarrollar, administrar y ejecutar en local, en la nube y a través de dispositivos móviles, soportando todos los sistemas operativos simultáneamente.

Permite controlar el ciclo de desarrollo completo: diseño del proyecto, diseño de la base de datos, crear la lógica del negocio, construir y generar la interfaz de usuario, y crear el software de instalación.

Velneo V7 ha sido especialmente diseñada para desarrollar aplicaciones de gestión empresarial destacando por la ejecución nativa multiplataforma (sin máquinas virtuales), el código distribuido y reutilizable, y la integración total de bases de datos, lógica del negocio e interfaz de usuario.

La base de datos de Velneo V7 destaca por su rapidez y alto rendimiento en la ejecución de búsquedas y transacciones de bases de datos, su gran fiabilidad y la utilización de programación avanzada sin usar sentencias SQL.

Además, Velneo V7 dispone de un amplio catálogo de Open Apps con tutoriales y ejemplos para aprender a trabajar con la aplicación, con componentes que pueden reutilizarse en nuestro código, y con plantillas empresariales para poder desarrollar nuestras propias aplicaciones. Entre estas Open Apps destacaremos vConta (software para contabilidad), vGestion (gestión de compras, ventas y almacén), vCash (gestión de cuentas bancarias), vTodo Plus (software de planificación y productividad empresarial) y Business Center (software de gestion ERP).

Y para terminar, dejo un enlace para todos aquellos que estén interesados en probar el software de Velneo V7.

Tuning o puesta a punto de consultas SELECT COUNT(*) en PL/SQL

2011-04-26T06:27:00.001+02:00

De vez en cuando recibo consultas sobre cómo sería posible mejorar el rendimiento de sentencias PL/SQL concretas. En la mayoría de los casos contestar a estas preguntas puede ser poco menos que imposible, más que nada porque realizar el tuning de una consulta PL/SQL sin conocer el contexto en que se ejecuta dicha consulta resulta muy complicado. Cada vez que esto ocurre siempre me asaltan preguntas como: ¿por qué se ejecuta dicha consulta?, ¿puede eliminarse la consulta y ser incluida en otro proceso?, ¿está la consulta dentro de un bucle LOOP y realmente debe formar parte del bucle?, ¿están creados todos los índices que podrían acelerar su ejecución? Por si esto fuera poco, una vez que tenemos la respuesta a preguntas como las antes mencionadas, sin duda, surgirán nuevas preguntas.

No obstante, el otro día un asiduo lector de este blog me envió una consulta SELECT bastante sencilla que, aún utilizando los índices de forma adecuada y ejecutándose bastante rápido, terminaba consumiendo muchos recursos de CPU en su base de datos Oracle debido a que era ejecutaba con mucha frecuencia dentro un procedimiento PLSQL. Dicho lector me pedía ayuda para realizar el tuning o puesta a punto de la mencionada consulta.

La consulta SQL era la siguiente:

SELECT COUNT(*)
INTO   v_count
FROM   tabla1 t1, tabla2 t2
WHERE  t1.id = t2.id(+)
  AND  t2.otro_id = v_otro_id;

Lo primero que se me vino a la mente es que había muchas posibilidades de que realmente no hiciera falta ejecutar dicha sentencia, y no existe ninguna posibilidad de ejecutar de forma más rápida una sentencia SELECT que no tener que ejecutarla. El caso es que siempre que me encuentro una cláusula COUNT(*) en una consulta SQL, tiendo a intentar eliminarla. La razón es que en la mayoría de los casos el procedimiento PL/SQL que la contiene, utiliza dicho COUNT(*) para hacer lo siguiente:

SELECT COUNT(*)
INTO   v_count
FROM   ...;

IF v_count > 0 THEN
  ejecutar_algo();
ENF IF;

En mi opinión, en estos casos lo mejor para el rendimiento de la base de datos Oracle es reescribir el código antes mencionado y reemplazarlo por un código tan simple como este:

ejecutar_algo();

Si el procedimiento PLSQL ejecutar_algo() lo que hace es analizar un conjunto de resultados dentro de un bucle, terminando cuando dicho conjunto ha sido analizado en su totalidad, entonces llamar a dicho procedimiento PLSQL cuando no hay datos para procesar, significará que el conjunto de resultados inicial estará vacío y la rutina ejecutar_algo() no hará nada. Por otro lado, si existen datos para procesar, entonces el procedimiento PL/SQL se ejecutará más rápido porque habremos eliminado la primera consulta SELECT realmente innecesaria. Y si lo pensamos detenidamente, es más que probable que incluso no habiendo datos para procesar, también el rendimiento de nuestro proceso mejore.

De cualquier forma, si asumimos que por cualquier motivo no hay forma de eliminar la consulta SELECT y que ésta tiene que ser realmente ejecutada, entonces deberemos pensar en qué podemos hacer para ponerla a punto o "tunearla". En el caso que os he planteado, algo que me resultó evidente nada más ver la consulta fue que el outer join podía eliminarse sin problemas, no en vano la primera consulta SQL es equivalente a esta otra:

SELECT COUNT(*)
INTO   v_count
FROM   tabla1 t1, tabla2 t2
WHERE  t1.id = t2.id
  AND  t2.otro_id = v_otro_id;

El outer join desde la tabla t1 a la t2 lo que hace es devolver resultados de la tabla t1 cuando no existe ninguno asociado por el mismo id en la t2, pero en este caso el campo t2.otro_id será NULL, y el valor NULL nunca es igual a nada, por lo que el último límite de la consulta nunca se cumplirá cuando la tabla t2 no devuelve ningún registro asociado.

No obstante, realizar este cambio en el código PLSQL no implicará ninguna mejora en el rendimiento de nuestro proceso, y esto es debido a que el optimizador de la base de datos Oracle es lo suficientemente "inteligente" como para darse cuenta de que puede eliminar sin problemas el outer join, y así lo hará al escribir el plan de ejecución correspondiente. Lo único que realmente habremos hecho es conseguir que nuestra consulta SELECT esté escrita de forma correcta.

Por lo tanto, llegado a este punto tuve que preguntarme si verdaderamente se podía hacer algo más para mejorar el rendimiento de esa sentencia SELECT tan simple, y me encontré con que sabiendo lo que sabía no era posible hacer nada. Sin conocer nada acerca del esquema de la base de datos Oracle, no podía hacer ninguna suposición, ni llegar a ninguna conclusión. ¿Realmente se podría hacer algo más conociendo el esquema y la relación existente entre las tablas t1 y t2? La respuesta es sí, pero esto será materia de otro artículo.

Artículos relacionados:
Puesta a punto de sentencias SQL.
Bucles y problemas de rendimiento.
Dinamic sampling o muestreo dinámico.
La cláusula CONSTRAINT.
Subqueries o joins.

¿Qué es mejor para el rendimiento, utilizar consultas PLSQL con subqueries o con joins?

2011-03-16T06:55:00.000+01:00

Alguna vez me han llegado cuestiones en la que se me preguntaba qué es mejor, en términos de rendimiento de las bases de datos Oracle, si utilizar en las consultas PLSQL subqueries (subconsultas) o utilizar joins (es decir, listar todas las tablas en la clausula FROM y unirlas en el WHERE). Lo primero que hay que tener claro es que escribir una consulta PL/SQL utilizando subqueries o utilizando joins es semánticamente diferente; además, utilizar una u otra forma puede derivar en que ambas consultas devuelvan resultados diferentes y que no sean directamente intercambiables.

Lo que yo recomiendo para elegir entre un tipo de consulta u otro es, en general, hacer lo siguiente:

Utilizar una subquery o subconsulta cuando no se necesita ninguna columna de la tabla que es referenciada en la subquery.
Utilizar un join en caso de necesitar alguna de las columnas.

Para intentar dejar las cosas algo más claras os dejo el siguiente ejemplo:

SELECT * 
FROM   pedidos
WHERE  tipo_pedido IN
       (SELECT tipo_pedido 
        FROM   tipo_pedidos);

SELECT p.* 
FROM   pedidos p, 
       tipo pedidos t
WHERE  p.tipo_pedido = t.tipo_pedido;

Mi opinión es que es mejor utilizar el primer SELECT, pero única y exclusivamente por razones semánticas, ya que la primera consulta es más fácil de "leer" que la segunda. Mientras que el primer SELECT lo que nos dice es "estoy obteniendo todos los registros de la tabla pedidos tales que el campo pedidos.tipo_pedido esté en la tabla tipo_pedidos", la segunda consulta lo que nos dice es "estoy juntando la tabla pedidos con la tabla tipo_pedidos". No obstante, para el optimizador de la base de datos Oracle ambas consultas son idénticas y el rendimiento de ambas será el mismo.

Otro aspecto a recordar es que normalmente una subconsulta o subquery no puede reemplazarse por un join o viceversa, la razón es que a menudo los resultados que devolverán serán diferentes. Por ejemplo:

SQL> SELECT * 
 2   FROM tipo_pedidos
 3   WHERE tipo_pedido IN
 4    (SELECT tipo_pedido 
 5    FROM pedidos)
 6   /

ID TIPO_PEDIDO
-- -----------
 1 Online
 2 Tienda

2 rows selected.

SQL> SELECT t.* 
 2   FROM pedidos p, tipo pedidos t
 3   WHERE p.tipo_pedido = t.tipo_pedido
 4   /

ID TIPO_PEDIDO
-- -----------
 1 Online
 1 Online
..............
 1 Online
 2 Tienda
..............
 2 Tienda

14321 rows selected.

En conclusión, el optimizador de la base de datos Oracle siempre sabrá que es lo que tiene que hacer, por lo que a la hora de construir una consulta PL/SQL y valorar si utilizar una subquery o un join, deberemos tener en cuenta que una y otra manera de construir una consulta no son, por lo general, intercambiables, y de ser posible elegir, deberemos escoger aquella que sea más sencilla de interpretar.

Artículos relacionados: El almacenamiento de subconsultas PLSQL.

Almacenamiento de subconsultas (subqueries PL/SQL) en la caché de las bases de datos Oracle

2011-02-09T06:57:00.012+01:00

El almacenamiento caché de subconsultas o subqueries PL/SQL se trata de una funcionalidad de las bases de datos Oracle, denominada en inglés scalar subquery caching, que se encarga de optimizar internamente la ejecución de aquellas consultas que incorporan subconsultas. El funcionamiento es bastante intuitivo, si durante la ejecución de una consulta PLSQL compleja, dicha consulta incluye alguna subquery, la base de datos Oracle intentará almacenar en la caché la salida de dicha subconsulta con el objetivo de poder reutilizar dichos datos, una y otra vez, durante la ejecución de la consulta PL/SQL principal. Obviamente esto será mucho mejor para el rendimiento de la base de datos que el tener que re-ejecutar la subconsulta múltiples veces.

Los resultados de la subconsulta quedan almacenados en una estructura de datos interna o hash table que, mientras dura la ejecución de la consulta PLSQL, queda residente en la memoria caché de la sesión Oracle correspondiente. Dicha estructura de datos desaparece de la caché en el momento que la consulta PL/SQL termina.

Aunque nosotros como desarrolladores no podremos localizar físicamente esa hash table, si que seremos capaces de "verla" en acción realizando algunas medidas bastante sencillas. Para ello podemos utilizar el siguiente ejemplo.

Primero crearemos una función PL/SQL como sigue:

SQL> CREATE OR REPLACE 
  2  FUNCTION subc (v in VARCHAR2)
  3  RETURN NUMBER AS
  4  BEGIN
  5    DBMS_APPLICATION_INFO.SET_CLIENT_INFO
  6      (userenv('client_info')+1);
  7    RETURN LENGTH(v);
  8  END;
  9  /

La función PLSQL que hemos creado lo que hace básicamente es contar cuantas veces ha sido llamada almacenando el resultado en CLIENT_INFO en la vista estándar V$SESSION.

A continuación invocamos la función utilizando la siguiente secuencia de comandos SQL:

SQL> SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

GET_CPU_TIME
------------
         710

SQL> EXEC 
  2  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(0);
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT OWNER, subc(OWNER) FROM ALL_TABLES;
28764 rows selected.

Statistics
---------------------------
      .....................
      28764  rows processed

SQL> SET AUTOTRACE OFF
SQL> SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

GET_CPU_TIME
------------
         997

SQL> SELECT USERENV('client_info') FROM DUAL;

USERENV('CLIENT_INFO')
----------------------
28764

El script SQL superior llama directamente a la función PL/SQL subc desde una consulta SELECT (ver línea en color naranja), mostrando cual ha sido el tiempo de CPU consumido por dicha consulta (997-710=287) y cuantas veces fue llamada dicha función (en este caso una vez por cada registro en la tabla ALL_TABLES, es decir, 28764 veces).

A continuación, en lugar de realizar la llamada a la función PLSQL directamente desde la consulta SELECT principal, hacemos dicha llamada dentro de una subconsulta.

SQL> SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

GET_CPU_TIME
------------
         997

SQL> EXEC 
  2  DBMS_APPLICATION_INFO.SET_CLIENT_INFO(0);
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE TRACEONLY STATISTICS
SQL> SELECT
  2    OWNER,
  3    (SELECT subc(OWNER) FROM DUAL) subc
  4  FROM ALL_TABLES;
28764 rows selected.

Statistics
---------------------------
      .....................
      28764  rows processed

SQL> SELECT DBMS_UTILITY.GET_CPU_TIME FROM DUAL;

GET_CPU_TIME
------------
        1186

SQL> SELECT USERENV('client_info') FROM DUAL;

USERENV('CLIENT_INFO')
----------------------
1444

De esta manera observamos que la función PL/SQL sólo ha sido llamada 1444 veces (frente a las 28764 anteriores) y que el tiempo de CPU se ha visto reducido en 0,98 segundos (1186-997=189, 287-189=98).

Nota: la función DBMS_UTILITY.GET_CPU_TIME fue introducida en la versión 10g de las bases de datos Oracle y mide el tiempo de uso de la CPU en centésimas de segundo.

Bloqueo de tablas hijo por causa de ejecutar sentencias PL/SQL sobre tablas padre

2011-01-04T14:22:00.006+01:00

En versiones de la base de datos Oracle anteriores a la 9i, cuando la clave primaria de una tabla padre (parent table) no se encuentra indexada en la tabla hijo (child table), es muy probable que tengamos problemas con los bloqueos de la tabla hijo que se producen, bien cuando se actualiza (con la sentencia PLSQL UPDATE) la clave primaria de la tabla padre (lo cual ocurre con relativa frecuencia ya que existen determinados trabajos que actualizan todas las columnas de una tabla incluso cuando el valor de la misma no ha cambiado), o bien cuando se realizaba el borrado (con la sentencia PL/SQL DELETE) de algún registro de la tabla padre.

El caso es que en las circunstancias anteriores y para evitar que se produzca un bloqueo completo de la tabla hijo (full table lock), lo más recomendable es indexar también en la tabla hijo la clave primaria de la tabla padre. No obstante, esta norma de bloqueo cambió con la versión 9i de la base de datos Oracle.

La norma en Oracle 9i, aunque básicamente el bloqueo completo no se puede evitar, lo que sí que hace es disminuir sensiblemente el tiempo que dura dicho bloqueo. Así, para versiones de la base de datos Oracle posteriores a la 9i, si se actualiza o se borra algún registro de una tabla padre cuya clave primaria no está indexada en la tabla hijo, dicha tabla hijo queda totalmente bloqueada sólo mientras dura la ejecución de las sentencias PLSQL UPDATE o DELETE. Es decir, el bloqueo se libera cuando las sentencias terminan y no es necesario esperar a que se ejecute la sentencia PL/SQL COMMIT. Por lo tanto, aunque la situación es mejor que para versiones anteriores a Oracle 9i, el bloqueo completo de la tabla hijo todavía ocurre.

También conviene señalar que este bloqueo completo de la tabla hijo también ocurre cuando se ejecuta la sentencia PLSQL MERGE (sentencia que apareció precisamente con la versión de la base de datos Oracle 9i). No obstante, después de la versión Oracle 11g Release 1, la ejecución de una sentencia PL/SQL MERGE no siempre produce el bloqueo de la tabla hijo, esto ocurre cuando el MERGE es simplemente un INSERT, o cuando el MERGE funciona también como un UPDATE pero dicho UPDATE no cambia la clave primaria de la tabla padre.

A continuación os dejo un script SQL con el que es posible detectar las claves primarias que no están indexadas en las tablas hijo correspondientes (por cierto, en inglés las claves primarias de una tabla padre se conocen, desde el punto de vista de las tablas hijos, como foreign keys o claves extranjeras). El script sólo funciona para claves primarias que incluyen un máximo de ocho columnas.

SELECT nombre_tabla,
       nombre_constraint,
       cnom1 || NVL2(cnom2,','||cnom2,NULL) ||
                NVL2(cnom3,','||cnom3,NULL) ||
                NVL2(cnom4,','||cnom4,NULL) ||
                NVL2(cnom5,','||cnom5,NULL) || 
                NVL2(cnom6,','||cnom6,NULL) ||
                NVL2(cnom7,','||cnom7,NULL) || 
                NVL2(cnom8,','||cnom8,NULL) columnas
FROM ( SELECT uc.table_name nombre_tabla,
              uc.constraint_name nombre_constraint,
              MAX(DECODE(pos,1,coln,NULL)) cnom1,
              MAX(DECODE(pos,2,coln,NULL)) cnom2,
              MAX(DECODE(pos,3,coln,NULL)) cnom3,
              MAX(DECODE(pos,4,coln,NULL)) cnom4,
              MAX(DECODE(pos,5,coln,NULL)) cnom5,
              MAX(DECODE(pos,6,coln,NULL)) cnom6,
              MAX(DECODE(pos,7,coln,NULL)) cnom7,
              MAX(DECODE(pos,8,coln,NULL)) cnom8,
              count(*) ncol
       FROM ( SELECT SUBSTR(table_name,1,30) tn,
                     SUBSTR(constraint_name,1,30) cn,
                     SUBSTR(column_name,1,30) coln,
                     position pos
              FROM user_cons_columns ) ucc,
              user_constraints uc
       WHERE  ucc.cn = uc.constraint_name
         AND  uc.constraint_type = 'R'
       GROUP BY uc.table_name, uc.constraint_name
     ) user_cons
 WHERE ncol > ALL
     ( SELECT count(*)
       FROM   user_ind_columns uic
       WHERE  uic.table_name = user_cons.nombre_tabla
         AND  uic.column_name in (cnom1, cnom2, 
                                  cnom3, cnom4,
                                  cnom5, cnom6,
                                  cnom7, cnom8)
         AND  uic.column_position <= user_cons.ncol
       GROUP BY uic.index_name
     )

En conclusión, la clara recomendación que personalmente doy es que, si sobre una tabla padre vamos a realizar operaciones de actualización de la clave primaria, borrado de registros o merge (inserción + actualización + borrado), lo mejor es proceder a indexar dicha clave primaria en la tabla hijo. Además, otra circunstancia que hace recomendable la indexación de la clave primaria de la tabla padre en la tabla hijo, es que no hacerlo puede derivar en graves problemas de rendimiento.

Diferencias entre restricciones PLSQL (cláusula CONSTRAINT) a nivel de tabla y a nivel de columna

2010-11-22T06:01:00.000+01:00

Resulta de perogrullo decir que una restricción PLSQL (cláusula CONSTRAINT) a nivel de columna sólo aplica a la columna sobre la que se ha definido, mientras que una restricción a nivel de tabla puede incluir todas las columnas de dicha tabla. Esta es la diferencia básica en PL/SQL, pero también conviene señalar que cualquier restricción a nivel de columna puede definirse también a nivel de tabla, sin embargo, no todas las restricciones a nivel de tabla pueden definirse a nivel de columna.

Ojo, no estoy diciendo que todas las restricciones deban definirse a nivel de tabla, simplemente estoy diciendo que es posible hacerlo. De hecho, mi opinión es que, siempre que se pueda expresar una restricción utilizando una CONSTRAINT PLSQL a nivel de columna, debemos hacerlo de esta manera. Una razón evidente es que una restricción a nivel de columna es sintácticamente más clara ya que resulta obvio que aplica a sólo una columna, mientras que si utilizamos una CONSTRAINT a nivel de tabla para definir la misma restricción, deberemos prestar más atención al código PL/SQL para comprender lo que hace. Pero esta no es la única razón, las hay todavía de mayor peso.

Antes de empezar con el ejemplo, creo que resulta conveniente mencionar que, a nivel de tiempos de ejecución, las restricciones a nivel de tabla y a nivel de columna son idénticas. Es decir, no hay diferencias en los tiempos de ejecución al emplear una u otra opción.

A continuación os expongo un caso mucho más indicativo de por qué deben utilizarse las restricciones a nivel de columna cuando esto es posible. Una restricción NOT NULL se implementa técnicamente en las bases de datos Oracle con una restricción de tipo CHECK. Así, por ejemplo, si creamos la siguiente tabla:

SQL> CREATE TABLE ejemplo
2    ( a INT NOT NULL,
3      b INT CHECK(b IS NOT NULL),
4      c INT,
5      CONSTRAINT c_chk CHECK(c IS NOT NULL)
6    );

Table created.

Podremos comprobar de una manera muy sencilla que las tres restricciones aparecen definidas de la misma manera en el diccionario de datos de la base de datos Oracle.

SQL> SELECT constraint_type,
2           search_condition
3    FROM   user_constraints
4    WHERE  table_name = 'EJEMPLO';

C SEARCH_CONDITION
— ————————————————
C "A" IS NOT NULL
C b IS NOT NULL
C c IS NOT NULL

Por lo tanto, podemos ver que las tres restricciones, aunque han sido creadas de forma distinta, todas se han generado como una restricción de tipo CHECK (el valor C de la columna CONSTRAINT_TYPE es la inicial de CHECK).

No obstante, si una columna es NOT NULL, debemos utilizar siempre la restricción NOT NULL en lugar de una restricción CHECK que realice el chequeo de que la columna es NOT NULL. La razón es que el optimizador PLSQL de las bases de datos Oracle reconoce la existencia de las restricciones NOT NULL sobre una columna, mientras que no es capaz de reconocer la existencia de una restricción CHECK sobre la misma columna. Seguro que ahora muchos os estaréis preguntando, ¿y qué más da que el optimizador sea capaz de identificar si una columna es NOT NULL?

Ciertamente no da lo mismo, imaginad que definimos un índice sobre la columna A de nuestra tabla EJEMPLO, en este caso el optimizador PL/SQL será capaz de utilizar dicho índice para ejecutar un COUNT(*). Sin embargo, si reemplazamos dicho índice y lo definimos sobre las columnas B o C, en ninguno de los casos el optimizador podrá utilizar dichos índices para ejecutar el COUNT(*). La razón es bien sencilla, en el primero de los casos el optimizador PLSQL sabía que la columna A era NOT NULL y que el índice sobre dicha columna apuntaba a todos y cada uno de los registros de la tabla EJEMPLO, por lo que era posible utilizar el índice para contar el número de registros de la misma. Sin embargo, al utilizar las otras dos columnas, el optimizador no es capaz de llegar a la misma conclusión porque la restricción CHECK no le proporciona la misma información que una restricción NOT NULL directa, concluyendo que los índices sobre las columnas B o C podrían no apuntar a todos los registros de la tabla, ya que las columnas con valor NULL no se incluyen en el índice.

En conclusión, este hecho demuestra que siempre es conveniente ser lo más específico posible y utilizar las restricciones a nivel de columna en lugar de a nivel de tabla cuando esto sea posible. De esta manera estaremos aportando más información al optimizador PLSQL.

Artículos relacionados: Cláusula CONSTRAINT para mejorar el rendimiento de las consultas PL/SQL.

Base de datos Oracle 11g: nueva funcionalidad errorlogging del SQL*Plus

2010-10-18T06:38:00.003+02:00

Hasta la versión de la base de datos Oracle 10g Release 2 no era posible capturar en SQL*Plus los errores que se generan cuando escribíamos incorrectamente una sentencia SQL. Es decir, los errores conocidos como SP2 no podían ser gestionados por las funcionalidades estándar del SQL*Plus OSERROR o SQLERROR. Esto era algo normal ya que los errores SP2 no son errores del tipo OS, como el típico error "unable to open spool file", ni tampoco son errores de tipo SQL o PLSQL, ya que la sentencia que hemos escrito incorrectamente en realidad no se trata de ningún comando SQL y nunca llegó a alcanzar la capa SQL o PL/SQL de la base de datos Oracle.

Para los que todavía estén un poco confundidos y aún no hayan identificado cuáles son los errores SQL de tipo SP2 os dejo el siguiente ejemplo:

SQL > SLECT * FROM empleados;
SP2-0734: unknown command beginning "SLECT * FR..." - 
rest of line ignored.

Una vez que ya he dejado claro cuales son los errores SP2, y después de remarcar que este tipo de errores no podían ser capturados en versiones de la base de datos Oracle anteriores a la 11g, sólo queda señalar que esto ha cambiado con la versión 11g de SQL*Plus al incluir la nueva funcionalidad errorlogging, una funcionalidad que se habilita mediante el siguiente comando SQL*Plus:

SQL > SET errorlogging ON

De esta manera en las versiones de la bases de datos Oracle 11g y superiores podremos guardar cualquier error, ya sea SQL, OS o SP2, en la tabla de logging denominada SPERRORLOG. Además, es posible marcar los errores con un identificador específico, de forma que sea mucho más sencillo encontrar nuestros registros de error propios. Por lo tanto, si durante la ejecución de un programa que utilice SQL*Plus se ha producido algún tipo de error, podremos, durante la misma sesión Oracle y utilizando sentencias SQL, chequear los errores que se han producido. Veamos un sencillo ejemplo:

SQL*Plus: Release 11.2.0.1.0 - 
Production on Mon Oct 18 12:55:41 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.1.7.4.0 - Production
With the Partitioning option
JServer Release 9.1.7.4.0 - Production

SQL > SET errorlogging ON
SQL > SLECT * FROM empleados;
SP2-0734: unknown command beginning "SLECT * FR..." - 
rest of line ignored.
SQL >
SQL > SELECT statement, message
  2   FROM sperrorlog;

STATEMENT     MESSAGE
————————————  ———————
SLECT * FROM  SP2-0734: unknown command beginning
empleados;    "SLECT * FR..." - rest of line ignored.

Como podeís observar en el ejemplo, hemos utilizado la funcionalidad errorlogging contra una versión de la base de datos 9i, esto ha sido posible gracias a que dicha funcionalidad es propia de la versión 11g de SQL*Plus, por lo tanto puede utilizarse con versiones antiguas de la base de datos Oracle siempre que la versión de SQL*Plus sea la 11g o superior.

Ejecución de cursores PLSQL y sentencias DML utilizando SQL dinámico nativo (Native Dynamic SQL)

2010-09-09T06:39:00.000+02:00

Una de las preguntas más frecuentes que me suele hacer la gente es acerca de la posibilidad de definir un procedimiento PL/SQL en el que se declaren múltiples cursores en base al valor de los parámetros de entrada de dicho procedimiento. Las preguntas suelen incluir condiciones muy variadas, pero lo normal es que los implicados sólo necesiten hacer variable la cláusula WHERE y que el resto del cuerpo del cursor PLSQL se mantenga fijo. Cuando esto ocurre yo siempre contesto remitiendo a los que preguntan al artículo que escribí sobre el paquete estándar PL/SQL DBMS_SQL, un paquete que permite crear sentencias SQL dinámicas.

Si he decidido escribir un artículo nuevo al respecto es por el hecho de que resulta mucho más legible un código que utilice directamente SQL dinámico nativo, es decir, un código en el que no se utiliza el paquete estándar DBMS_SQL. A continuación os dejo un sencillo ejemplo de cómo se podría construir, utilizando SQL dinámico nativo, un procedimiento PLSQL en el que se define un cursor cuya cláusula WHERE varíe en función de un parámetro.

CREATE OR REPLACE PROCEDURE varcursor
(par1 IN NUMBER DEFAULT NULL)
AS
  l_query VARCHAR2(500);
  TYPE tcursor IS REF CURSOR;
  l_cursor tcursor;
  l_cursor_rec dba_users%ROWTYPE;
BEGIN
  l_query := 'SELECT * FROM dba_users WHERE 1=1';
  IF par1 IS NOT NULL THEN
    l_query := l_query ||
               ' AND user_id = ' || par1;
  END IF;
  OPEN l_cursor FOR l_query;
  LOOP
    FETCH l_cursor INTO l_cursor_rec;
    EXIT WHEN l_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE
      (l_cursor_rec.username);
  END LOOP;
  CLOSE l_cursor;
END;

En nuestro ejemplo, el procedimiento varcursor sacará por pantalla el username con el user_id que se le pase como parámetro, y si el parámetro se pasa con valor NULL, entonces sacara por pantalla todos los username de la tabla dba_users.

Por otro lado, muchas otras preguntas hacen referencia a la utilización de SQL dinámico nativo con sentencias SQL DML. De todas las sentencias DML la que se lleva la palma es las sentencia UPDATE y, en este caso concreto, las preguntas hacen referencia tanto a la posibilidad de utilizar una cláusula WHERE variable, como a la posibilidad de modificar o no una determinada columna en base a si se pasa o no un determinado parámetro.

Antes de poneros un ejemplo concreto, os diré que la implementación de una cláusula WHERE variable se realiza de forma similar a la utilizada en el procedimiento varcursor, sin embargo, para el caso de que una columna se actualice según se pase o no un determinado parámetro, es mejor no utilizar SQL dinámico. Veamos ahora un ejemplo concreto.

CREATE OR REPLACE PROCEDURE varupdate
(vsal IN NUMBER DEFAULT NULL,
 veid IN NUMBER DEFAULT NULL)
AS
  l_query VARCHAR2(500);
BEGIN
  l_query := 'UPDATE empleados
              SET fecha_actualizacion = SYSDATE,
              salario = NVL(:vsal, salario)
              WHERE 1=1';
  IF par2 IS NOT NULL THEN
    l_query := l_query ||
               ' AND empleado_id = :veid';
  END IF;
  EXECUTE IMMEDIATE l_query USING vsal, veid;
END;

En el ejemplo podéis ver que el primer parámetro es el que determina si la columna salario se actualiza o no, sin embargo, dicha columna la hemos incluido en el bloque estático de la sentencia SQL UPDATE utilizando la función NVL(), de manera que si el parámetro vsal toma el valor NULL, entonces actualizaremos la columna salario con el mismo valor que tenía anteriormente. Esto es mucho más sencillo que utilizar un IF y cambiar dinámicamente la sentencia UPDATE en base a si el parámetro vsal toma el valor NULL o no. No obstante, alguien podría pensar que utilizar el IF puede ahorrar tiempo de procesamiento ya que se evitaría el tener que actualizar una columna innecesariamente, sin embargo, puesto que los índices no tienen que reconstruirse cuando el valor de una columna no cambia realmente, el aumento en tiempo de procesamiento es mínimo y lo más conveniente es utilizar SQL estático y la función NVL().

En cambio, para el parámetro que se utiliza en la cláusula WHERE de nuestro UPDATE, hemos utilizado la misma técnica que en el procedimiento varcursor, es decir, hemos empleado un IF para así utilizar o no la condición asociada con dicho parámetro.

En conclusión, nuestro procedimiento actualizará la columna salario del empleado con id veid al valor vsal, pero si vsal es NULL, entonces el valor de la columna salario continuará siendo el mismo y sólo se habrá actualizado el valor de la columna fecha_actualizacion. Y si veid toma el valor de NULL, entonces el valor de la columna fecha_actualizacion se actualizará para todos los empleados, ocurriendo lo mismo para la columna salario que tomará el valor vsal, siempre que vsal no sea NULL, en cuyo caso dicha columna no cambiaría.

La cláusula PIPELINED en las funciones PL/SQL y la excepción NO_DATA_NEEDED

2010-07-29T19:08:00.010+02:00

En esta entrada explicaré para que sirve la cláusula PIPELINED en las funciones PL/SQL, y como se debe utilizar la excepción NO_DATA_NEEDED, que nada tiene que ver con la excepción NO_DATA_FOUND, para controlar las funciones PLSQL que incluyen dicha cláusula y que en inglés se denominan pipelined functions. Primero quiero remarcar que la funcionalidad pipelined fue introducida por primera vez en la versión 9i de las bases de datos Oracle. Básicamente, el uso de la cláusula PIPELINED resulta de gran utilidad y es prácticamente imprescindible cuando necesitamos que en lugar de una tabla sea una rutina PL/SQL la que nos sirva como fuente de datos.

La mejor forma de explicar el funcionamiento de esta cláusula es con un ejemplo.

SQL> CREATE OR REPLACE FUNCTION
  2    generador_numeros (n IN NUMBER DEFAULT NULL)
  3    RETURN sys.odciNumberList
  4    PIPELINED
  5    AS
  6      BEGIN
  7        FOR i IN 1 .. NVL(n,100)
  8        LOOP
  9          PIPE ROW(i);
 10        END LOOP;
 11        DBMS_OUTPUT.PUT_LINE
 12          ('Ejecutando return');
 13        RETURN;
 14      END;
 15  /
 
Function created

En el ejemplo la cláusula PIPELINED permite que la función generador_numeros funcione exactamente como una tabla. Veamos como.

SQL> SELECT * FROM TABLE(generador_numeros(4));
 
COLUMN_VALUE
------------
           1
           2
           3
           4
 
Ejecutando return

Una vez explicado para que sirve la cláusula PIPELINED, ya es posible explicar como se debe utilizar la excepción NO_DATA_NEEDED dentro de este tipo de funciones PLSQL, de hecho se trata de una excepción muy importante cuando estamos escribiendo el código de una pipelined function. Si alguna vez habéis utilizado la cláusula PIPELINED y dentro de la función PL/SQL asociada no habéis utilizado la mencionada excepción, es más que probable que en vuestro código tengáis un bug en estado latente. Ahondando un poco más en el tema, os diré que la excepción NO_DATA_NEEDED aparece cuando una función que utiliza la cláusula PIPELINED puede devolver más datos pero la sentencia SQL que invoca dicha función no los ha solicitado.

Como anteriormente, la mejor forma de explicar cuando aparece y como debemos manejar la excepción NO_DATA_NEEDED es con un ejemplo.

SQL> SELECT * FROM TABLE(generador_numeros(4))
  2  WHERE rownum < 3;
 
COLUMN_VALUE
------------
           1
           2

Aparentemente la función PL/SQL generador_numeros ha funcionado correctamente, pero si nos fijamos bien, la salida por pantalla que yo había incluido al final de la función no se ha ejecutado, es decir, en la pantalla no aparece por ningún lado el esperado "Ejecutando Return". Simplemente esa parte del código no se ha ejecutado porque la sentencia SQL con la que hemos invocado la función generador_numeros no lo necesitaba.

Ciertamente todo parece haber funcionado correctamente, sin embargo, aunque haya permanecido oculto a nuestros ojos, se ha generado una excepción NO_DATA_NEEDED. En este sentido, la excepción NO_DATA_NEEDED se trata de una excepción totalmente diferente a todas las demás, ya que si ésta no se trata dentro del código, simplemente es ignorada (por contra, el resto de excepciones, en caso de no ser tratadas, generan un error).

A continuación cambiaré someramente el código de nuestra función PLSQL pipelined para demostrar lo que estoy diciendo.

SQL> CREATE OR REPLACE FUNCTION
  2    generador_numeros (n IN NUMBER DEFAULT NULL)
  3    RETURN sys.odciNumberList
  4    PIPELINED
  5    AS
  6      BEGIN
  7        FOR i IN 1 .. NVL(n,100)
  8        LOOP
  9          PIPE ROW(i);
 10        END LOOP;
 11        DBMS_OUTPUT.PUT_LINE
 12          ('Ejecutando return');
 13        RETURN;
 14      EXCEPTION
 15        WHEN NO_DATA_NEEDED
 16        THEN
 17          DBMS_OUTPUT.PUT_LINE
 18            ('Ejecutando excepcion');
 19          RETURN;
 20      END;
 21  /
 
Function created

Ahora si ejecutamos nuestra sentencia SQL obtendremos lo siguiente:

SQL> SELECT * FROM TABLE(generador_numeros(4))
  2  WHERE rownum < 3;
 
COLUMN_VALUE
------------
           1
           2
 
Ejecutando excepcion

Es decir, vemos que la salida por pantalla muestra el texto "Ejecutando excepcion", quedando así demostrado que el bloque EXCEPTION se ha ejecutado.

Por último, sólo me queda mencionar que utilizando la sentencia WHEN OTHERS en lugar de WHEN NO_DATA_NEEDED, habríamos conseguido el mismo resultado, sin embargo no sería la manera correcta de hacerlo ya que estaríamos enmascarando otros errores que podrían generarse por otros motivos. La excepción NO_DATA_NEEDED está especialmente diseñada para tratar este tipo de situaciones y debe ser utilizada de forma apropiada cuando estemos escribiendo una función PL/SQL que utilice la cláusula PIPELINED.

Creación diferida de segmentos (Deferred Segment Creation), nueva funcionalidad de la release 2 de Oracle 11g

2010-06-07T21:44:00.008+02:00

En las versiones anteriores a la release 2 de la base de datos Oracle 11g, cuando se creaba cualquier objeto en la base de datos utilizando una sentencia SQL o PL/SQL, ya fuera un tabla, un índice o cualquier otro objeto que requiriese ser almacenado, el gestor de la base de datos creaba los segmentos necesarios y asignaba un tamaño inicial a los mismos, un tamaño que podía ser pequeño, de unos 64 Kbytes mínimo, pero dicho espacio ya no podía ser utilizado para otras necesidades. En los tiempos actuales, un tamaño de 64 Kbytes no es nada, pero si algo tan pequeño se tienen que repetir muchas veces, entonces el consumo de recursos de almacenamiento puede llegar a ser bastante grande.

Por ejemplo, podemos pensar en alguna aplicación que necesitase crear cientos o miles de tablas, tablas que nunca van a ser utilizadas pero cuyos segmentos de almacenamiento tienen que ser creados de todas formas. Algunos pueden preguntarse por qué un aplicación puede necesitar crear tablas que nunca van a ser utilizadas, este hecho no es tan extraño ya que existen muchas aplicaciones, como por ejemplo Oracle Financials, que ofrecen funcionalidades que requieren el uso de determinadas tablas y dichas tablas sólo contienen datos cuando se hace uso de dichas funcionalidades. Para evitar el consumo de almacenamiento en este tipo de situaciones, la release 2 de la base de datos Oracle 11g incorpora una nueva funcionalidad conocida como creación diferida de segmentos (Deferred Segment Creation), funcionalidad que a continuación explicaremos y analizaremos.

La funcionalidad de creación diferida de segmentos puede establecerse a nivel de sistema, a nivel de sesión, o incluso a nivel de sentencia SQL DDL. Veamos a continuación los diferentes comandos SQL que permiten realizar este cambio:

ALTER SYSTEM SET deferred_segment_creation=true;

ALTER SESSION SET deferred_segment_creation=true;

CREATE TABLE table_name
....................
SEGMENT CREATION DEFERRED;

Obviamente el parámetro deferred_segment_creation puede inicializarse desde el fichero init.ora y, por defecto, es decir, si no se le asigna un valor en concreto en dicho fichero, tomará el valor de true, por lo que la funcionalidad de creación diferida de segmentos estará habilitada a menos que hayamos decidido cambiar el valor de dicho parámetro a false.

Por otro lado, también conviene mencionar que es posible forzar la creación de los segmentos a nivel de sentencia SQL utilizando la cláusula SEGMENT CREATION IMMEDIATE.

Por lo tanto, cuando la funcionalidad de creación diferida de segmentos está habilitada, una tabla de la base de datos Oracle no consumirá recursos de almacenamiento hasta que se inserta el primer registro en dicha tabla, esto es también extensible a cualquier índice u objeto que esté asociado con dicha tabla.

Con la release 2 de la base de datos Oracle 11g, la tabla del sistema USER_TABLES incorpora un nuevo campo denominado SEGMENT_CREATED, campo que tomará el valor de NO en el momento de la creación de una tabla siempre que hayamos utilizado la cláusula SEGMENT CREATION DEFERRED o que el parámetro deferred_segment_creation tenga el valor de true. Sólo en el instante en que se inserte el primer registro en dicha tabla, el mencionado campo SEGMENT_CREATED pasará a tomar el valor de YES.

Otras tablas del sistema que pueden ser útiles para verificar la utilización de segmentos de almacenamiento son USER_EXTENTS y USER_SEGMENTS, ambas tablas muestran información muy similar por lo que en el siguiente ejemplo sólo utilizaré una de ellas.

SQL> CREATE TABLE p1 (
     a INT CONSTRAINT p1_pk PRIMARY KEY,
     b INT CONSTRAINT p1_b UNIQUE,
     c CLOB )
     LOB (c) STORED AS p1_c
     SEGMENT CREATION DEFERRED;

Table created.

SQL> SELECT segment_created
     FROM user_tables
     WHERE table_name = 'P1';

SEGMENT_CREATED
---------------
NO

1 row selected.

SQL> SELECT 
       segment_name,
       segment_type,
       bytes
     FROM user_segments
     WHERE segment_name like 'P1%';

no rows selected.

Con el comando SQL CREATE TABLE, que incorpora la cláusula SEGMENT CREATION DEFERRED, hemos creado la tabla P1. Después, la información que devuelven las sentencias SELECT nos demuestra que los segmentos de almacenamiento no han sido creados todavía.

Sigamos con el ejemplo.

SQL> INSERT INTO p1
     VALUES (1,2,'Prueba PLSQL');

1 row created.

SQL> SELECT segment_created
     FROM user_tables
     WHERE table_name = 'P1';

SEGMENT_CREATED
---------------
YES

1 row selected.

SQL> SELECT 
       segment_name,
       segment_type,
       bytes
     FROM user_segments
     WHERE segment_name like 'P1%';
 

SEGMENT_NAME  SEGMENT_TYPE  BYTES
------------  ------------  -----
P1            TABLE         65536
P1_PK         INDEX         65536
P1_B          INDEX         65536
P1_C          LOBSEGMENT    65536

4 rows selected.

Una vez que se inserta un registro en la tabla P1, las mismas sentencias SELECT que hemos utilizado anteriormente indican que ya se están consumiendo recursos de almacenamiento, y, en concreto, en la tabla USER_SEGMENTS, aparecen todos los objetos asociados con la tabla P1 que consumen recursos de este tipo.

Uso de ediciones para actualizar la base de datos, nueva funcionalidad de la release 2 de Oracle 11g

2010-05-03T18:17:00.011+02:00

Desde mi punto de vista, la nueva funcionalidad Edition-Based Redefinition, cuya traducción directa sería "redefinición basada en ediciones", se trata de la característica más interesante de la release 2 de la base de datos Oracle 11g. En pocas palabras se trata de la posibilidad de actualizar nuestra aplicación PL/SQL sin tener que poner en modo restringido la base de datos, es decir, con esta nueva versión de la base de datos Oracle es posible realizar online la actualización de nuestra aplicación. Explicado de esta manera tan sencilla la nueva funcionalidad suena como un juego de niños, pero en realidad es algo cuya verdadera dimensión resulta difícil de medir por sus enormes implicaciones.

Las versiones anteriores de la base de datos Oracle ya permitían realizar un gran número de operaciones online:

Modificar la mayoría de los parámetros de la base de datos Oracle (sólo 90 de los 350 parámetros requieren reiniciar la base de datos).
Aplicar patches a la base de datos utilizando Oracle Real Application Clusters.
Reorganizar objetos de la base de datos en el espacio de almacenamiento.
Crear índices (CREATE INDEX).
Realizar actualizaciones de las base de datos Oracle de una release principal a otra release principal.

En conclusión, mientras la base de datos estaba online, podíamos realizar casi cualquier actualización, con excepción de operaciones del tipo recrear un procedimiento almacenado PL/SQL, cambiar un trigger PLSQL, actualizar permisos, o modificar una vista normal o una vista materializada. En pocas palabras, mientras los usuarios los estaban utilizando, no nos era posible actualizar los objetos que constituyen la base de nuestra aplicación.

Supongo que muchos DBAs habrán experimentado una enorme frustración cuando han intentado recrear un procedimiento PL/SQL para corregir algún tipo de problema, y no han podido hacerlo porque el procedimiento estaba siendo ejecutado por algún usuario del sistema. Obviamente, cuando se trata de un solo procedimiento PLSQL, el problema se solventa esperando a que el usuario lo deje de utilizar y el objeto se desbloquee, pero en la mayoría de los casos, los DBAs no tienen que reemplazar un solo procedimiento PL/SQL, sino un buen número de ellos, y muchos de estos procedimientos estarán relacionados con otros objetos de la base de datos Oracle. Por lo tanto, si un DBA debe actualizar varios procedimientos, paquetes, vistas y triggers PLSQL simultáneamente, y existen usuarios utilizando la aplicación, por un lado los usuarios bloquearan las transacciones iniciadas por el DBA y, por otro, el DBA estará bloqueando las operaciones de dichos usuarios. Esto supone que, para realizar este tipo de actualizaciones, el DBA necesitará poner en modo restringido la base de datos Oracle.

Todo esto termina con la release 2 de la base de datos Oracle 11g y la funcionalidad Edition-Based Redefinition, que habilita a DBAs y usuarios para que accedan a distintas versiones de procedimientos, triggers, vistas, sinónimos y otros objetos de la base de datos almacenados bajo un mismo esquema, siendo ambas versiones totalmente independientes, de manera que coexisten en el sistema pero no interfieren entre ellas.

Esto es posible gracias a que la creación de objetos en la release 2 de la base de datos Oracle 11g introduce una tercera dimensión en la funcionalidad que permite resolver el nombre de los objetos, esta tercera dimensión es la edición de la base de datos que está utilizando nuestra sesión. Es decir, para referirnos a un objeto, aparte del dueño del mismo y su propio nombre, también se utiliza la edición de la sesión. Cuando un usuario crea una sesión en la release 2 de la base de datos Oracle 11g, dicha sesión tendrá asociado un atributo que identifica la edición de la sesión, edición que si no se cambia con el comando ALTER SESSION tomará el valor por defecto de la edición de la base de datos (normalmente el nombre de la edición por defecto es ora$base).

Todo esto posibilita que los DBAs puedan utilizar el comando SQL ALTER SESSION para cambiar la edición de la sesión que estén utilizando y, por ejemplo, utilizar una edición denominada "ver2", edición sobre la que podrán compilar todos los procedimientos PLSQL que quieran actualizar. Obviamente los cambios realizados sobre la edición "ver2" sólo serán visibles por las sesiones que utilicen dicha edición, y como dicha edición no es la edición por defecto, ningún usuario verá dichos cambios a menos que lo pida a través del comando ALTER SESSION.

Veamos a continuación como debería proceder un DBA para actualizar la aplicación sin afectar a los usuarios que la están utilizando.

CREATE EDITION ver2 AS CHILD OF ora$base;

ALTER SESSION SET EDITION = ver2;

Con el comando SQL CREATE EDITION hemos creado una nueva edición como hija de la edición de la base de datos por defecto. La edición ver2 es sólo una copia virtual de la base de datos, es decir, la base de datos no se copia físicamente sino que apunta a los objetos de la edición por defecto ora$base. Sólo se empezará a utilizar espacio de almacenamiento físico cuando se modifique algún objeto de la base de datos en el contexto de la edición ver2.

Con el comando SQL ALTER SESSION hemos indicado a la base de datos que los cambios que realicemos a continuación deben hacerse sobre la edición ver2. En este momento el DBA puede realizar la actualización de la aplicación modificando procedimientos, triggers, vistas, objetos, etcétera.

ALTER DATABASE DEFAULT EDITION = ver2;

Una vez completados los cambios, el DBA puede llevarlos a producción utilizando el comando SQL ALTER DATABASE, mediante el cual indicará que la versión por defecto de la base de datos es la ver2, de manera que el nuevo código se hace accesible a todos los usuarios de forma instantánea.

Memoria PGA frente a espacio de almacenamiento temporal en las bases de datos Oracle

2010-03-29T06:25:00.009+02:00

Recientemente he recibido en este blogs sobre programación PL/SQL algunas preguntas referentes a las diferencias entre el tipo de operaciones que la base de datos Oracle realiza en la memoria del área global de programa (PGA - Program Global Area) frente a las que realiza en el espacio del almacenamiento temporal (TEMP space). Primero os comentaré que las versiones antiguas de las bases de datos Oracle solían utilizar los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE para controlar cuanta memoria PGA era posible utilizar antes de empezar a tener que usar el espacio de disco temporal (TEMP).

No obstante en las nuevas versiones de las bases de datos Oracle, la gestión de la memoria PGA se realiza de forma automática y mucho más dinámica.

Supongamos que ejecutamos algún tipo de sentencia SQL y que esta sentencia SQL tiene que realizar algún tipo de operación del tipo:

Enlazar tablas usando un algoritmo de tipo hash (Hash join).
Ordenar datos (operación de tipo SORT), por ejemplo cuando se aplican cláusulas como ORDER BY, DISTINCT o GROUP BY.
Realizar cualquier operación que necesite la utilización de memoria.

En estas situaciones la base de datos Oracle asigna un área de trabajo, ya sea un área HASH, un área SORT o lo que sea, cuyo tamaño será determinado dependiendo de la carga de trabajo del sistema en ese momento concreto. Cuando este área de trabajo se llena y la operación SQL que está teniendo lugar no se ha completado, entonces la base de datos Oracle procede a llevar los datos de la operación en proceso al disco, es decir realiza una operación de escritura al espacio de almacenamiento temporal (TEMP). De tal manera que el espacio TEMP, aún no siendo tal cosa, funcionará como una memoria virtual, como si se tratase de una auténtica memoria PGA. Obviamente, para optimizar el rendimiento de la base de datos Oracle lo mejor es evitar la escrituras en el espacio de disco TEMP.

El objetivo de la gestión automática de la memoria PGA es no utilizar toda la memoria a la vez. La idea es reservar siempre algo de memoria previniendo la incorporación momentánea de usuarios adicionales y evitando situaciones en la que un determinado usuario acapare toda la memoria disponible.

De todas formas los DBA (analistas de bases de datos) pueden elegir entre una gestión manual de la memoria o una gestión automática. Yo personalmente me inclino por utilizar por defecto la gestión automática de la memoria PGA.

Por lo tanto, es posible asignar valores fijos a los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE, el problema es que los valores recomendables para estos parámetros van a variar a lo largo del tiempo, ya que dichos valores no pueden ser idénticos cuando la base de datos Oracle cuenta con, por ejemplo, cinco usuarios activos, frente a unas horas más tarde cuando el número de usuarios activos va a ser de cuatrocientos.

Por eso resulta muy recomendable asignar al parámetro WORKAREA_SIZE_POLICY el valor de AUTO, momento en el cual entra en juego el parámetro PGA_AGGREGATE_TARGET, parámetro que sirve para asignar la máxima cantidad de memoria PGA que la base de datos Oracle debe procurar utilizar. De esta manera, la base de datos Oracle distribuirá dicha memoria entre todas las sesiones activas. Además, desde la versión Oracle 9i Release 2, el paquete de estadísticas disponible a través de la vista de rendimiento dinámico V$, que es accesible desde Oracle Enterprise Manager, nos indica cual debe ser, en nuestro sistema, el valor óptimo del parámetro PGA_AGGREGATE_TARGET para minimizar las operaciones de entrada/salida en el espacio de alojamiento temporal. Esta información nos permitirá cambiar dinámicamente el tamaño de nuestra memoria PGA o tomar la decisión de aumentar el tamaño de la memoria RAM en nuestro servidor para conseguir un rendimiento óptimo de nuestra base de datos Oracle.

Situaciones en las puede ser necesario un uso extensivo de la memoria, por ejemplo, si necesitamos ejecutar diariamente un procedimiento PLSQL que realiza hash joins tremendamente grandes u otro tipo de acciones que requieren utilizar mucha memoria PGA, entonces puede ser recomendable crear un script SQL para ejecutar dicho procedimiento, script que utilizará el comando ALTER SESSION para que dicha sesión pueda hacer uso de todos los recursos de memoria PGA disponibles mediante la inicialización adecuada de los parámetros SORT_AREA_SIZE y HASH_AREA_SIZE.

En conclusión, mi recomendación es hacer uso de la gestión automática de la memoria PGA para las sesiones de usuario y aplicaciones que se ejecutan en el día a día. La gestión manual de la memoria PGA sólo tiene sentido para aquellos procesos con mucha carga de trabajo, que requieren un uso extensivo de la memoria PGA y que se ejecutarán en periodos de tiempo durante los cuales sólo estarán en ejecución este tipo de procesos (generalmente serán trabajos programados que se lanzan en horas con poca o ninguna actividad de usuarios).

Utilidad del paquete estándar PL/SQL DBMS_ROWID

2010-02-25T06:45:00.007+01:00

Hace unas semanas me llegó una consulta sobre PL/SQL en la que se me preguntaba si era posible conocer, utilizando una consulta SQL, el nombre de la partición en la que se encontraba almacenado un determinado registro de una tabla. El paquete estándar PLSQL DBMS_ROWID nos puede ayudar a obtener esta información mediante la extracción del ROWID_OBJECT que identifica de manera única el segmento donde se encuentran los datos. Después bastará que asociemos este identificador con una de las vistas XXX_OBJECTS (donde XXX puede ser DBA, ALL o USER) para obtener el nombre de la partición (ver ejemplo a continuación).

SELECT sh.order_number
     , do.subobject_name
     , do.data_object_id
FROM   oe.so_headers_all sh
     , dba_objects do
WHERE  do.data_object_id = 
         DBMS_ROWID.ROWID_OBJECT(sh.rowid)
  AND  sh.order_number = '123456'

ORDER_NUMBER SUBOBJECT_NAME DATA_OBJECT_ID
------------ -------------- --------------
      123456 PART1          15107

El paquete estándar PLSQL DBMS_ROWID resulta de mucha utilidad para ver como los datos están organizados dentro de una tabla e identificar donde están almacenados los distintos registros de la misma. Veamos a continuación otro ejemplo de uso del paquete DBMS_ROWID.

SELECT sh.order_number
     , dt.tablespace_name
     , dt.status
FROM   oe.so_headers_all sh
     , dba_data_files  ddf
     , dba_tablespaces dt
WHERE ddf.tablespace_name = dt.tablespace_name
  AND ddf.relative_fno = 
        DBMS_ROWID.ROWID_RELATIVE_FNO(sh.rowid)
  AND ddf.file_id = 
        DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO
          (sh.rowid, 'OE', 'SO_HEADERS_ALL')
  AND sh.order_number = '123456'

ORDER_NUMBER TABLESPACE_NAME STATUS   
------------ --------------- ---------
      123456 RESDATOS        ONLINE

En este caso hemos utilizado el paquete estándar PL/SQL DBMS_ROWID para obtener, para un determinado ROWID, el número de bloque y el número de fichero en la base de datos Oracle, con estos valores podremos consultar en la vista DBA_DATA_FILES el nombre del TABLESPACE en el que está almacenado el registro en cuestión y después, utilizando la vista DBA_TABLESPACES, podremos determinar el estado de dicho TABLESPACE.

Cambios en los parámetros de la base de datos Oracle, cuándo tienen lugar

2010-01-19T19:29:00.004+01:00

Existe la creencia un tanto generalizada de que cuando realizamos un cambio en los parámetros de la base de datos Oracle (utilizando el comando SQL ALTER SYSTEM SET ...) mientras la instancia de la base de datos está operativa y si los cambios los realizamos utilizando el fichero de parámetros almacenado (SPFILE), estos cambios no tienen lugar hasta que la base de datos se reinicia. Esta idea es falsa y equivocada, ya que el comando SQL ALTER SYSTEM dispone del parámetro SCOPE para especificar en que momento deben tener efecto los cambios.

El parámetro SCOPE puede tomar los valores MEMORY, SPFILE, or BOTH. Si le asignamos el valor MEMORY el cambio aplicará de forma inmediata siempre que estemos cambiando un parámetro que no requiera el reinicio de la base de datos Oracle para que el cambio sea efectivo. Si le asignamos el valor SPFILE entonces únicamente estaremos modificando el fichero de parámetros almacenado y el cambio tendrá lugar en el siguiente reinicio de la base de datos Oracle. Pero si utilizamos el valor BOTH, entonces el cambio será inmediato y, además, cambiaremos el valor en el fichero de parámetros almacenado (SPFILE).

En este punto concreto, desde programación PLSQL queremos indicar que, dependiendo de si la base de datos Oracle está o no online, podemos encontrarnos con tres tipos diferentes de parámetros:

Parámetros que no pueden cambiarse online

La guía de referencia de bases de datos Oracle (Oracle Database Reference) describe claramente los parámetros de inicialización y sus propiedades, señalando si un parámetro es modificable o no, si un parámetro es no modificable, entonces no podremos cambiarlo mientras la base de datos está online. Por ejemplo es el caso del parámetro AUDIT_TRAIL.

Parámetros que pueden cambiarse online pero sólo para sesiones futuras

En este caso el cambio realizado no afectará a ninguna de las sesiones que estén conectadas a la base de datos en el momento que realicemos en cambio, sino que afectará sólo a las sesiones que se conecten posteriormente a la ejecución del comando ALTER SYSTEM. Es el caso de, por ejemplo, el parámetro SORT_AREA_SIZE:

SQL> ALTER SYSTEM
2    SET sort_area_size = 12345
3    DEFERRED SCOPE=memory;
System altered.

SQL> SHOW PARAMETER sort_area_size

NAME           TYPE    VALUE
-------------- ------- -----
sort_area_size integer 65536

SQL> connect /
Connected.

SQL> SHOW PARAMETER sort_area_size

NAME           TYPE    VALUE
-------------- ------- -----
sort_area_size integer 12345

Parámetros que pueden cambiarse online de forma inmediata

En este caso los cambios se reflejan inmediatamente, propagándose a todas las sesiones que, en ese momento, estén conectadas a la base de datos Oracle. Así ocurre, por ejemplo, con el parámetro USER_DUMP_DEST.

SQL> SHOW PARAMETER user_dump_dest

NAME           TYPE    VALUE
-------------- ------- -----
user_dump_dest string  /tmp

SQL> ALTER SYSTEM
2    SET user_dump_dest = '/u02/tmp';
System altered.

SQL> SHOW PARAMETER user_dump_dest

NAME           TYPE    VALUE
-------------- ------- --------
user_dump_dest string  /u02/tmp

Por otro lado, si lo que queremos es eliminar un parámetro de configuración de la SPFILE, puesto que se trata de un fichero que no se puede editar utilizando un editor de textos, también deberemos utilizar el comando SQL ALTER SYSTEM, pero en este caso utilizando la cláusula RESET. Así, por ejemplo, si lo que queremos es eliminar el parámetro USER_DUMP_DEST y que tome el valor por defecto que hemos sobre escrito anteriormente, deberemos proceder como sigue:

SQL> ALTER SYSTEM
2    RESET user_dump_dest
3    SCOPE=spfile SID='*';
System altered.

En este momento habremos eliminado el parámetro USER_DUMP_DEST del fichero SPFILE cosa que podremos verificar ejecutando el comando SQL:

SQL> CREATE pfile='/tmp/pfile.txt'
2    FROM spfile;
File created.

Ahora, si revisamos el fichero /tmp/pfile.txt veremos que el parámetro USER_DUMP_DEST no aparece en el mismo.

Cláusula CONSTRAINT para mejorar el rendimiento de las consultas PL/SQL

2009-12-14T18:26:00.004+01:00

Resulta bastante interesante mencionar la importancia de que, al crear las tablas de nuestra base de datos Oracle, utilicemos, cuando sea posible, la cláusula CONSTRAINT para mejorar el rendimiento de las consultas PLSQL que utilicen dicha tabla. Muchos desarrolladores de PL/SQL piensan que la utilización de la cláusula CONSTRAINT sólo sirve para garantizar la integridad de los datos, lo cual es cierto, pero esta cláusula también la utiliza el optimizador Oracle para determinar el plan de ejecución óptimo.

De hecho, el optimizador PLSQL utiliza como entradas:

La consulta SQL que se va a optimizar.
Todas las estadísticas disponibles de los objetos de la base de datos.
De estar disponibles, las estadísticas del sistema (velocidad de CPU, velocidad de los dispositivos de entrada/salida, etcétera).
Los parámetros de inicialización.
Las constraints (restricciones).

El optimizador utiliza todos estos datos para determinar el mejor plan de ejecución posible. Personalmente he detectado que mucha gente tiende a no emplear la funcionalidad CONSTRAINT en los data warehouse y sistemas de reporting. Su argumento es decir: "Ya hicimos una limpieza de datos y no necesitamos aplicar restricciones a los datos". Ciertamente es posible que no necesiten utilizar constraints para garantizar la integridad de los datos, pero lo que es seguro es que necesitarán dichas constraints para conseguir que el optimizador genere los planes de ejecución más adecuados. En un data warehouse un plan de ejecución incorrecto puede causar que la ejecución de una consulta lleve horas e incluso días. Por lo tanto, en un data warehouse la utilización de las cláusulas CONSTRAINT es necesaria por razones de rendimiento.

En el siguiente ejemplo analizaremos como conseguir eliminar el acceso a una tabla de una vista (view) gracias a la utilización de la cláusula CONSTRAINT:

CREATE TABLE e1 AS
  SELECT * FROM empleados
  WHERE nombre IN ('Pepe', 'Juan');

ALTER TABLE e1 MODIFY nombre NOT NULL;

ALTER TABLE e1 ADD CONSTRAINT e1_check
  CHECK (nombre IN ('Pepe', 'Juan'));

CREATE TABLE e2 AS
  SELECT * FROM empleados
  WHERE nombre IN ('Paco', 'Luis');

ALTER TABLE e2 MODIFY nombre NOT NULL;

ALTER TABLE e2 ADD CONSTRAINT e1_check
  CHECK (nombre IN ('Paco', 'Luis'));

CREATE OR REPLACE VIEW vemp AS
  SELECT * FROM e1
  UNION ALL
  SELECT * FROM e2;

Según podéis ver, hemos creado dos tablas que contienen datos mutuamente excluyentes y una vista que utiliza la cláusula UNION ALL para mostrar todos los datos juntos. A continuación lo que haremos es realizar una consulta sobre dicha vista utilizando el campo nombre en la cláusula WHERE para ver como el optimizador decide no utilizar una de las tablas.

SELECT * FROM vemp WHERE name = 'Juan';

Execution Plan
————————————————————————————————
Plan hash value: 3412345678

————————————————————————————————
|Id | Operation         | Name | 
————————————————————————————————
| 0 | SELECT STATEMENT  |      |
| 1 | VIEW              | VEMP | 
| 2 | UNION-ALL         |      | 
| 3 | TABLE ACCESS FULL | E1   |
| 4 | FILTER            |      |
| 5 | TABLE ACCESS FULL | E2   |
————————————————————————————————

Predicate Information 
(identified by operation id):
————————————————————————————————
3 - filter(“NOMBRE”='Juan')
4 - filter(NULL IS NOT NULL)
5 - filter(“NOMBRE”='Juan')

Arriba podemos ver el plan de ejecución generado desde una base de datos Oracle versión 11g y en el que hemos quitado algunas columnas que no son de utilidad en este ejemplo. En él podemos observar que el optimizador realmente no ha prescindido de realizar un FULL SCAN de la tabla e2, lo cual, evidentemente, podría haber hecho ya que dicha tabla sólo contiene a los empleados cuyos nombres son Paco o Luis. Sin embargo, si realizamos una análisis más concienzudo, observaremos que, en el paso 4, el optimizador ha aplicado un filtro, y que dicho filtro reza NULL IS NOT NULL. Está claro que nuestra consulta SELECT no incluye esta condición, sino que el optimizador la ha añadido automáticamente. Puesto que NULL IS NOT NULL es una condición falsa, esa parte del árbol del plan de ejecución nunca se ejecutará, por lo que en ningún momento se accederá a la tabla e2.

Este ejemplo es muy simple, pero creo que bastante explicativo de como las cláusulas CONSTRAINT pueden ayudar a mejorar el rendimiento de nuestra base de datos Oracle.

Sentencia CASE en PL/SQL de Oracle

2009-11-10T18:48:00.015+01:00

Las versiones de base de datos Oracle 9i y posteriores incluyen la posibilidad de utilizar la sentencia CASE dentro de una sentencia SQL (SELECT, UPDATE, etcétera). La sentencia CASE permite realizar las mismas operaciones que las sentencias de control PL/SQL IF-THEN-ELSIF-ELSE pero con la particularidad de que pueden utilizarse dentro de una sentencia SQL. La sintaxis de la sentencia CASE es como sigue:

CASE  [ expresion ]
  WHEN condicion_1 THEN resultado_1
  WHEN condicion_2 THEN resultado_2
  ...
  WHEN condicion_n THEN resultado_n
  ELSE resultado
END

La opción expresion es opcional. Se trata del valor que estaríamos comparando con la lista de condiciones (condicion_1, condicion_2, ..., condicion_n).

Las condiciones, condicion_1 a condicion_n, son analizadas en el mismo orden en que aparecen listadas y, en el momento en que una de estas condiciones se cumple como verdadera, la sentencia CASE devuelve el resultado correspondiente y deja de analizar el resto de condiciones.

Los resultados, resultado_1 a resultado_n, deben ser todos del mismo tipo de dato PLSQL. Son los valores que devuelve la sentencia CASE en el momento en que una condición se cumple como verdadera.

Si ninguna de la condiciones se cumple como verdadera, entonces la sentencia CASE devuelve el valor de la cláusula ELSE. Si la cláusula ELSE se omite y ninguna condición se cumple como verdadera, entonces la sentencia CASE devuelve el valor NULL.

En una sentencia CASE puede haber hasta 255 cláusulas WHEN-THEN-ELSE, por lo tanto podremos construir hasta 128 comparaciones.

A continuación podéis ver un par de ejemplos de sentencia SELECT que incluyen una sentencia CASE:

SELECT apellido, 
  CASE nombre
    WHEN 'Pepe' THEN 'se llama Pepe'
    WHEN 'Juan' THEN 'se llama Juan'
    ELSE 'no se llama ni Pepe, ni Juan'
  END
FROM empleados;

SELECT apellido, 
  CASE
    WHEN nombre = 'Pepe' THEN 'se llama Pepe'
    WHEN nombre = 'Juan' THEN 'se llama Juan'
    ELSE 'no se llama ni Pepe, ni Juan'
  END
FROM empleados;

Ambas sentencias SQL devolverán los mismo resultados aunque la segunda sentencia se ha escrito si utilizar la opción expresion.

La sentencia CASE admite múltiples combinaciones, por ejemplo se puede utilizar más de un campo y las condiciones que se pueden contruir pueden ser todo lo complejas que se nos ocurra. Veamos otro ejemplo:

SELECT apellido, nombre,
  CASE
    WHEN salario > pago_especie
     AND apellido < 'P' THEN 'es rico'
    WHEN salario < pago_especie
     AND apellido >= 'Q' THEN 'es pobre'
    ELSE 'no es ni pobre ni rico'
  END
FROM empleados;

En nuestro ejemplo resultaría que los empleados cuyo salarío fuera mayor que lo que reciben en especie y cuyo nombre empezase por una letra de la A a la O, serían considerados ricos, los empleados cuyo salarío fuera menor que lo que reciben en especie y cuyo nombre empezase por una letra de la Q a la Z, serían considerados pobres, y el resto de los empleados no serían considerados ni ricos ni pobres.

Artículos relacionados:
La sentencia MERGE en PL/SQL.
La cláusula PIVOT en PLSQL.
La cláusula WITH en PL/SQL.

Cómo ejecutar sentencias DDL dentro de un trigger PL/SQL

2009-10-06T18:15:00.007+02:00

Supongo que muchos conoceréis el hecho de que no es posible incluir sentencias DDL (es decir, sentencias de definición de objetos como CREATE, REVOKE, GRANT, ALTER, etcétera) dentro de un trigger PL/SQL. Esto es un hecho que tiene una explicación muy sencilla, ¿qué ocurriría si dentro de un trigger ejecutamos una sentencia no transaccional o DDL y necesitamos deshacer la transacción (rollback)? Sencillamente no podríamos deshacer la ejecución de dicha sentencia. En esta situación nuestra transacción dentro del trigger PLSQL no habría tenido lugar, pero la sentencia DDL se habría ejecutado dejando nuestra base de datos Oracle en una situación claramente no deseable.

La ejecución de sentencias no transaccionales en un trigger sólo puede derivar en problemas, así que, en el caso improbable de que necesitemos ejecutar una sentencia DDL dentro de un trigger, mi recomendación es utilizar el paquete PLSQL DBMS_JOB para programar la ejecución de dicha sentencia DDL (CREATE, REPLACE, DROP, etc.). Veamos un ejemplo:

CREATE TABLE ejecutar_DDL (
  njob NUMBER PRIMARY KEY,
  sentencia VARCHAR2(2000) );

CREATE OR REPLACE
PROCEDURE ejecutar_DDL_en_trigger
(  p_njob IN NUMBER )
IS
  l_ejecutar_DDL ejecutar_DDL%ROWTYPE;
BEGIN
  SELECT * INTO l_ejecutar_DDL
  FROM ejecutar_DDL
  WHERE njob = p_njob;
  EXECUTE IMMEDIATE l_ejecutar_DDL.sentencia;
END;

Ahora lo único que queda es, una vez que nuestra transacción en el trigger PL/SQL se ha completado, invocar nuestra sentencia DDL utilizando un bloque PLSQL similar a este:

DECLARE
  l_njob NUMBER;
BEGIN
  DBMS_JOB.SUBMIT (
    l_njob,
    'ejecutar_DDL_en_trigger(JOB);' );
  INSERT INTO ejecutar_DDL
  VALUES (
    l_njob,
    '...sentencia DDL (sin ; al final)...' );
  COMMIT;
END;

De esta forma dispondremos de un procedimiento PL/SQL, ejecutar_DDL_en_trigger, que nos permitirá ejecutar nuestra sentencia DDL recogiendo, de ser necesario, los errores producidos y que, además, se ejecutará poco tiempo después de que la transacción realizada dentro del trigger se haya completado (commit). Y lo mejor de todo es que si nuestra transacción se deshace (rollback), el INSERT en la tabla ejecutar_DDL también se habrá deshecho y la sentencia DDL no se ejecutará.

Conclusión: dentro de un trigger PL/SQL, siempre deberemos crear soluciones semejantes a ésta cuando estemos pensando en ejecutar algo del tipo no transaccional (ver artículo problemas con los triggers PLSQL).

Transacciones parciales en PLSQL para lidiar con tablas muy grandes o voluminosas

2009-09-03T20:14:00.013+02:00

Hace unas semanas alguien mencionó en un comentario que en su base de datos Oracle tenía una tabla con millones de registros y que, utilizando un bucle PLSQL, pensaba ejecutar un UPDATE y un COMMIT por cada, digamos, 500 registros procesados en dicha tabla, evitando así posibles problemas con los segmentos de rollback. El caso es que dicho lector me preguntaba si yo tenía alguna sugerencia al respecto. Este tipo de problema es, ciertamente, algo más complejo de lo que a simple vista parece, y sobre el que conviene escribir con cierta calma.

Si dividimos una transacción muy larga en muchas otras más pequeñas, existen bastantes probabilidades de que a mitad de la transacción global se produzca un error ORA-01555 (snapshop too old), problema generalmente causado por la alta frecuencia en la ejecución de sentencias COMMIT, o que simplemente se produzca un fallo del sistema. Entonces nos encontraremos con una transacción realizada parcialmente. Por lo tanto, antes de implementar este tipo de solución, deberemos asegurarnos de la que la transacción global puede re-ejecutarse. La cuestión es que en muchos casos esto no será posible y, por tanto, tendremos que escribir bastante código PL/SQL para hacer posible esta re-ejecución.

Supongamos que tenemos el siguiente bloque de código PLSQL:

DECLARE
  CURSOR cur IS SELECT * FROM tmillon;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur BULK COLLECT INTO cur_rec LIMIT 500;
    ...
    FORALL i IN 1 .. cur_rec.COUNT
      UPDATE tmillon SET ... ;
      -- cur_rec se usa en el UPDATE
    ...
    COMMIT;
    EXIT WHEN cur%NOTFOUND;
  END LOOP;
  CLOSE cur;
END;

En el caso concreto de este bloque PL/SQL os puedo asegurar de que la probabilidad de que se produzca un error ORA-01555 es bastante elevada, la razón principal es que estamos leyendo y modificando la misma tabla y que el SELECT del cursor debe entregar, en todo instante, los valores del momento en que se inició la consulta. El caso es que la base de datos necesitará espacio para el SELECT y para poder hacer un ROLLBACK de los UPDATE ejecutados, siendo muy probable que se produzca el mencionado error.

Entonces, puesto que se puede producir este error, deberemos pensar en alguna forma de poder re-ejecutar el bloque PL/SQL, necesitaremos bien una columna en la tabla que nos indique que dicho registro ha sido actualizado, u otra tabla en la que se inserten las claves primarias de los registros que han sido modificados. Si empleamos esta última solución, nuestro cursor quedaría de la siguiente manera:

CURSOR cur IS SELECT * FROM tmillon t
WHERE NOT EXISTS (
  SELECT 'Existe'
  FROM claves_ok c
  WHERE c.prclave = t.prclave);

Estás dos soluciones no son únicas, pero lo que yo quiero hacer patente es que, en cualquier caso, siempre tendremos que escribir algo de código adicional.

Para lidiar con este tipo de situaciones yo recomiendo:

Actualizar la tabla utilizando una sola sentencia PLSQL. Esta es, sin duda, la manera más eficiente en términos de utilización de los recursos de la base de datos. Además, no nos debemos preocupar demasiado por un posible problema con los segmentos de rollback, ya que estos se necesitan para que la transacción se complete correctamente.

Utilizar el paquete PL/SQL DBMS_REDEFINITION. Con este paquete crearemos una copia de la tabla base de forma online, es decir, que no será necesario bloquear las modificaciones de la tabla, y, después, utilizaremos dicha tabla en el cursor principal. Estaremos utilizando una estructura de datos más compacta e incluso, si nos interesa, en la versión 10g de la base de datos Oracle, podemos añadir un comando ORDER BY a la redefinición de la tabla para reordenar los registros de la misma en el disco. De este paquete es muy probable que hable en una entrada posterior.

Utilizar el comando CREATE TABLE AS SELECT. Esto sería similar a utilizar el paquete PL/SQL DBMS_REDEFINITION pero, en este caso, no debemos permitir que mientras se realice esta operación se esté modificando la tabla fuente original. Además, el paquete DBMS_REDEFINITION se encarga de la creación automática de todo los objetos necesarios para crear una copia de la tabla (índices, permisos, etcétera), mientras que si utilizamos CREATE TABLE AS SELECT, de ser necesario, tendremos que crear todos estos objetos manualmente.

Cómo utilizar un cursor PL/SQL como parámetro de salida en un procedimiento

2009-07-22T17:47:00.012+02:00

En este artículo o voy a proponer un ejercicio práctico bastante sencillo que puede resultar de bastante útilidad práctica. Supongamos que queremos crear un procedimiento PLSQL que utilice un par de parámetros de entrada, que podrán tomar el valor NULL, y con un parámetro de salida que será el cursor PL/SQL correspondiente a la siguiente sentencia SELECT:

SELECT * FROM empleados
WHERE nombre = parametro_1
  AND apellidos = parametro_2;

Lo primero que tendremos que hacer es crear un paquete PL/SQL que contenga la definición del cursor:

CREATE OR REPLACE PACKAGE empleados_pkg
IS
  /* Definición del REF CURSOR type */
  TYPE empleados_type IS REF CURSOR
  RETURN empleados%ROWTYPE;
END empleados_pkg;

Después sólo tendremos que crear el procedimiento PLSQL que devolverá como salida el cursor definido en el paso anterior, y que utilizará los dos parámetros de entrada indicados en el enunciado del problema que estamos intentando resolver.

CREATE OR REPLACE PROCEDURE datos_empleados
  (
  par1 IN VARCHAR2,
  par2 IN VARCHAR2,
  cursor1 OUT empleados_pkg.empleados_type
  )
AS
BEGIN
  OPEN cursor1 FOR
  SELECT * FROM empleados
  WHERE nombre = NVL(par1,nombre)
    AND apellidos = NVL(par2,apellidos);
END datos_empleados;

No obstante, la utilización de la función NVL (si par1 es NULL toma el valor nombre, si par2 es NULL toma el valor apellidos), podría no ser muy conveniente y derivar en problemas de rendimiento, por lo que es recomendable definir el procedimiento anterior como sigue:

CREATE OR REPLACE PROCEDURE datos_empleados
  (
  par1 IN VARCHAR2,
  par2 IN VARCHAR2,
  cursor1 OUT empleados_pkg.empleados_type
  )
AS
BEGIN

  /* Todos los parámetros tienen valor */
  IF par1 IS NOT NULL AND par2 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE nombre = par1
      AND apellidos = par2;

  /* Sólo tiene valor par1 */
  ELSIF par1 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE nombre = par1;

  /* Sólo tienen valor par2 */
  ELSIF par2 IS NOT NULL
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados
    WHERE apellidos = par2;

  /* Ningún parámetro tiene valor */
  ELSE
  THEN
    OPEN cursor1 FOR
    SELECT * FROM empleados;

  END IF;

END datos_empleados;

Artículos relacionados: Cursores en PLSQL.