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

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:

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

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.

SQL y PL/SQL - La sentencia MERGE

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

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

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

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

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

La claúsula WITH en SQL y PL/SQL

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

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

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

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

Fases de procesamiento analizadas por el comando TKPROF

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

Fases durante el procesamiento de una sentencia SQL

Durante el procesamiento de una sentencia SQL, ya sea mediante un script o un programa PL/SQL, se distinguen cuatro fases: análisis de la sintaxis (parsing), análisis de las variables (binding), ejecución (executing) y recuperación de datos (fetching).

Fase de parsing

Durante esta fase el servidor de la base de datos Oracle realiza las siguientes acciones:

• Busca la sentencia SQL en la memoria compartida (shared pool).
• Chequea la sintaxis de la sentencia siguiendo las especificaciones y la gramática del lenguaje SQL.
• Chequea la semántica, asegurando que los objetos Oracle referenciados en la sentencia SQL son válidos (existen en la base de datos) y satisfacen las restricciones de seguridad (es decir, el usuario que ejecuta la sentencia tienen los permisos adecuados sobre dichos objetos).
• Determina si el proceso que lanza la sentencia SQL tiene los permisos apropiados para ejecutarlo.
• Si la sentencia SQL incluye una vista (view) o una subquery (una subquery no es más que una sentencia SELECT que está dentro de otra sentencia SQL), transforma dicha sentencia en una sentencia SQL equivalente e intenta simplificar la sentencia resultante.
• Determina y almacena el plan de ejecución o, si es posible, utiliza un plan de ejecución existente.

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

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.

Puesta a punto de sentencias SQL (Tuning PLSQL 1)

Si la siguiente sentencia SQL SELECT:

SELECT *
FROM empleados
WHERE nombre = 'Francisco'
AND estado_civil = 'S' -- Soltero

Nos está dando tiempos de ejecución largos, esto querrá decir que:

   1. Obviamente la tabla empleados es de un tamaño considerable.

   2. La tabla no está adecuadamente indexada o que, aún habiéndose creado el índice adecuado, lógicamente un índice sobre la columna "nombre", el optimizador SQL decide utilizar el índice sobre otra columna.

Cómo obtener el plan de ejecución de una sentencia SQL o PL/SQL

Una de las formas más usuales de mejorar el rendimiento de una sentencia SQL o PL/SQL es analizar el plan de ejecución que devuelve el optimizador Oracle. En SQL*Plus se puede obtener dicho plan de ejecución, además de algunas estadísticas referentes al resultado de la ejecución de la sentencia SQL o PLSQL, utilizando el comando AUTOTRACE. Para obtener el plan de ejecución no hay necesidad de ejecutar dicho comando pero, ciertamente, si no lo utilizamos, la poca amigabilidad del comando que debemos ejecutar (EXPLAIN PLAN), el formato de dicho comando y lo complejo que resulta analizar el contenido de la tabla V$SQL_PLAN, hacen que, por mi parte, recomiende encarecidamente el uso del comando SQL*Plus AUTOTRACE.

En mi opinión, AUTOTRACE es una buenísima herramienta de diagnóstico y una excelente ayuda para optimizar sentencias SQL y PL/SQL. El comando AUTOTRACE es puramente declarativo, por lo que es mucho más fácil de utilizar que el comando EXPLAIN PLAN. La sintaxis del comando AUTOTRACE es como sigue:

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

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.

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

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

UNIX> tkprof fichero_trazado fichero_salida [opciones]

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

Cómo usar la utilidad de trazado del SQL de Oracle

La utilidad de trazado del SQL de las bases de datos Oracle nos permite analizar el rendimiento de un determinado programa PL/SQL. Esta funcionalidad nos va a permitir obtener información acerca del rendimiento de todas las sentencias SQL que se ejecuten durante la ejecución del programa PLSQL.

Para utilizar la herramienta de trazado del PL/SQL de Oracle deberemos seguir cinco pasos:

1) Inicializar los parámetros relativos a esta funcionalidad SQL.
2) Activar la traza SQL.
3) Ejecutar la aplicación que queremos analizar y desactivar la traza cuando termine.
4) Formatear el fichero producido por la traza SQL con el comando TKPROF.
5) Interpretar la salida del comando TKPROF y, si es necesario, optimizar nuestro programa PLSQL.

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

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.

Hints en PL/SQL para determinar el método de acceso

Ya hemos hablado de los hints para el modo optimización. En este segundo artículo continuaré hablando de los hints pero, en concreto, de aquellos que permiten indicar al optimizador Oracle el modo en que se debe acceder a los datos de las tablas. Este tipo de hints resultan extremadamente eficaces a la hora de optimizar una sentencia SQL.

En su día ya indiqué cual es la sintaxis de los hints pero creo que no está de más que la muestre de nuevo:

{ DELETE | INSERT | SELECT | UPDATE } /*+ HINT (parámetros) */

o

{ DELETE | INSERT | SELECT | UPDATE } --+ HINT (parámetros)

Los hints básicos que sirven para determinar el metodo de acceso a los datos de una tabla Oracle son los siguientes:

Bucles y problemas de rendimiento (performance)

El lenguaje PL/SQL permite la contrucción de bucles, esta funcionalidad es, sin duda, extremadamente útil. No obstante, utilizar bucles puede causar problemas de rendimiento en nuestras rutinas, aunque ciertamente estos problemas son fáciles de determinar y corregir.

Imaginemos que una rutina PL/SQL contiene una sentencia select y los registros que devuelve son utilizados para actualizar otra tabla dentro de un bucle. Por ejemplo:

FOR bucle1 IN (
  SELECT articulo
  FROM articulos
  WHERE descripcion = '%zapato%'
) LOOP
  UPDATE precios
  SET precio = precio * 1.05
  WHERE articulo = bucle1.articulo;
END LOOP;

Vistas materializadas (materialized views) en SQL y PL/SQL (1)

El SQL de las bases de datos Oracle permite crear vistas materializadas o materialized views. Estas vistas materializadas, a parte de almacenar la definición de la vista propiamente dicha, también almacenan los registros que resultan de la ejecución de la sentencia SELECT que define la vista. Como las vistas normales, la sentencia SELECT es la base de la vista, pero la sentencia SQL se ejecuta cuando se crea la vista y los resultados se almacenan físicamente constituyendo una tabla real que ocupa sitio en el disco duro. Esta tabla puede definirse utilizando los mismos parámetros de almacenamiento que se pueden utilizar para una tabla normal (tablespace, etcétera). Las vistas materializadas también admiten índices, esta funcionalidad resulta muy útil a la hora de mejorar el rendimiento de las sentencias PLSQL o SQL que utilicen vistas materializadas.

Hints en PL/SQL para el modo de optimización

Los hints son pistas que se dan al optimizador SQL de Oracle para que elabore el plan de ejecución de una sentencia DML (sentencias de manipulación de datos como select, insert, update, delete, etc) según nosotros le aconsejemos. En este primer artículo sobre los hints voy a empezar hablando de aquellos que se utilizan para seleccionar el modo de trabajar del optimizador Oracle. Estos hints, hablando desde un punto de vista práctico, no son muy utilizados, aunque no por ello pueden dejar de ser útiles en determinadas circunstancias.

Los hints se incorporan a una sentencia DML en forma de comentario y deben ir justo detrás del comando principal. Por ejemplo, si se tratara de una sentencia SELECT el formato sería el siguiente:

     SELECT /*+ COMANDO-HINT */ ...

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

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.

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

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.